CODEWITHSUNDEEP
mysqllockinginnodb
Ryan Li
Ryan Li

Reputation: 434

Is this innodb gap lock bug?

enter image description here

trx1 select * from table where refId=4 for update

trx2 insert into table (refId) values (2); block

trx2 will be blocked, i knew trx1 will keep it gap lock, [1,4),[4,7);

my question is why keep gap lock? "insert val 2" is not conflict "select where refId=4 for update", why innodb will keep gap lock, why not use record-lock?

this question has been bothering me for a long time, please tech god save me.

Upvotes: 6

Views: 986

Answers (3)

Taufik_TF
Taufik_TF

Reputation: 134

MariaDB [test]> select * from t1 where refId=4 for update; +----+------+ | id | refId| +----+------+ | 2 | aaaaa | | 4 | bbbbb | +----+------+ 2 rows in set (0.00 sec)

if refId is string not int, how to gap lock under RR mode in Mysql?

Upvotes: 0

GProst
GProst

Reputation: 10227

i knew trx1 will keep it gap lock, [1,4),[4,7)

Actually, to be precise in the case of a non-unique index there will be (1,4] 'next-key' lock (record + preceding gap) and (4,7) 'gap' lock before record 7. You can check it using InnoDB monitor.

"insert val 2" is not conflict "select where refId=4 for update", why innodb will keep gap lock, why not use record-lock?

Gap locks purpose is to prevent records inserts into those gaps. I'm pretty sure it doesn't really check or care what refId of a record is when you try to insert it into a locked gap. So the point is a gap is locked and nothing can be inserted there.

Now, why do we need to lock these specific gaps? Let's imagine we're going to insert a new record with refId = 4, it can apparently be inserted after the existing refId = 4 record (i.e. (4,7) gap) or before it (i.e. (1,4) gap). So if we lock just the record and don't lock these gaps it'll be possible to insert such rows.

Upvotes: 0

Pavel Katiushyn
Pavel Katiushyn

Reputation: 825

Interesting question.

The gap lock is needed to avoid phantom rows. MySQL works in REPEATABLE-READ isolation level by default. If you run several times select ... for update in your transaction, it should always return same result. Suppose you don't have gap lock and trx2 inserted another row with refId=4 (index is not unique). Then following select in trx1 will return two rows:

MariaDB [test]> select * from t1 where refId=4 for update;
+----+------+
| id | refId|
+----+------+
|  2 |    4 |
|  4 |    4 |
+----+------+
2 rows in set (0.00 sec)

It is not the same result as first select.

Upvotes: 1

Related Questions