Is this innodb gap lock bug?

Question

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.

Answer 1

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.