Marko Mäkelä added a comment - 2020-02-19 20:11 Gap locks should be deterministic, once we fix MDEV-20605 .

Marko Mäkelä added a comment - 2019-02-23 18:58 In a discussion with monty , arubin and Peter Zaitsev today, the need for having secondary index record locks was questioned. The basic idea is that instead of checking if a lock exists on a secondary index record, we check the lock on the corresponding clustered index record. We would still seem to need gap locks on secondary index records. A locking read on a secondary index must prevent an INSERT of the same value, for any primary key value: connection con1; BEGIN ; # returning any amount of rows , possibly including 0 SELECT COUNT (*) FROM t WHERE indexed_column BETWEEN 42 AND 64 LOCK IN SHARE MODE; connection con2; --error ER_LOCK_WAIT_TIMEOUT INSERT INTO t SET pk=12345, indexed_column=50; --error ER_LOCK_WAIT_TIMEOUT UPDATE t SET indexed_column=50; --error ER_LOCK_WAIT_TIMEOUT DELETE FROM t WHERE indexed_column BETWEEN 30 AND 70; connection con1; # must return the same result! SELECT COUNT (*) FROM t WHERE indexed_column BETWEEN 42 AND 64 LOCK IN SHARE MODE; If the SELECT did not acquire gap locks that cover the whole range [42,64], then the INSERT , DELETE or UPDATE would not be blocked. Note: Even if users do not use SERIALIZABLE isolation level or LOCK IN SHARE MODE or FOR UPDATE , InnoDB will internally acquire shared or exclusive locks in FOREIGN KEY checks or CASCADE or SET operations. It would seem that we may not really need single-record locks on secondary indexes, but gap locks we do need. There is a possible reason against relying explicitly on gap locks on secondary indexes. Gap locks are inherently nondeterministic (potentially causing problems with some forms of replication), since they can be attached to a delete-marked purgeable record. That is, purge can widen gaps at any time by removing those "invisible" keys. Because of this, it might be better to prefer single-record locks over gap locks in those cases where we want to lock a single secondary index key.

Marko Mäkelä added a comment - 2018-12-31 09:07 - edited As noted in MDEV-17512 , it could be desirable to have a variation of a locking SELECT that acquires write-gap-locks, so that a later INSERT by the same transaction within the already searched range of keys will be guaranteed to succeed. Currently, FOR UPDATE only guarantees that UPDATE and DELETE will succeed.

Marko Mäkelä added a comment - 2018-06-05 12:44 - edited There are two modes of gap locks, sharable within each mode. That is, multiple locking reads (possibly as part of DELETE or UPDATE ) can acquire a read-gap-lock on the same gap. Also, multiple INSERT can acquire a write-gap-lock (called LOCK_INSERT_INTENTION in the code) on the same gap. But at most one type of gap lock may exist on the same gap at any given time. The following test demonstrates this: --source include/have_innodb.inc CREATE TABLE t1(id INT PRIMARY KEY ) ENGINE=InnoDB; INSERT INTO t1(id) VALUES (1),(2),(3),(4); BEGIN ; SELECT * FROM t1 WHERE id=0 FOR UPDATE ; connect (con1, localhost, root,,); SELECT * FROM t1 WHERE id=0 FOR UPDATE ; disconnect con1; connection default ; DROP TABLE t1; Both SELECT  will acquire a gap lock that precedes the successor of the non-matching record. In this case, the non-matching record would be the page infimum, and the successor would be the record id=1 . Any INSERT with a value that is less than this would be prevented. The example would also work without the INSERT ; in that case, the gap lock would be attached to the page supremum, preventing any INSERT into the empty table while either of the two transactions hold the gap lock.