CREATE TABLE t ( id INT PRIMARY KEY, val INT UNIQUE KEY ); INSERT INTO t (val,id) VALUES (10,4); INSERT INTO t (val,id) VALUES (20,7); BEGIN; SELECT * FROM t; id val 4 10 7 20 DELETE FROM t WHERE val=10; BEGIN; INSERT INTO t (val,id) VALUES (10,3); SELECT LOCK_MODE, COUNT(*) FROM performance_schema.data_locks WHERE OBJECT_SCHEMA='test' AND OBJECT_NAME='t' AND INDEX_NAME='val' AND LOCK_TYPE='RECORD' GROUP BY 1; LOCK_MODE COUNT(*) S 1 S,GAP 2 ROLLBACK; ROLLBACK; DROP TABLE t;