mysql gap lock compatibility

Question

It is also worth noting here that conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.

I have a table users, the column id is primary key

mysql> select * from users;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   21 |
|  2 | jerry |   10 |
|  3 | eric  |   18 |
|  5 | eric  |   17 |
+----+-------+------+
4 rows in set (0.00 sec)

Sess1 mysql> start transaction;
      mysql> select * from users where name='eric' for update;
+----+------+------+
| id | name | age  |
+----+------+------+
|  3 | eric |   18 |
|  5 | eric |   17 |
+----+------+------+
2 rows in set (0.01 sec)
      
Sess2 mysql> start transaction;
      mysql> select * from users where name='eric' lock in share mode;  -- it's blocking!

if gap S-lock and gap X-lock can be hold by different transactions, why Sess2 is blocked?

user_2527458 · Answer

The second query:

mysql1 > select * from users where name='eric' lock in share mode;

acquired IS lock on table and S lock on rows with name eric so can't update this row by different connection.

So, one transaction has update transaction with exclusive (IX) lock and another is in share mode with shared (S) lock and based Table-level lock type compatibility which is summarized in the following matrix they have conflict and the later transaction must be locked while first transaction is in running.

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

Read following paragraph from Locking Reads for more information.

SELECT ... FOR UPDATE

For index records the search encounters, locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... LOCK IN SHARE MODE, or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying undo logs on an in-memory copy of the record.)

mysql gap lock compatibility

1 Answers