MySQL Bugs: #108488: Gap locking issue in READ COMMITTED

Bug #108488	Gap locking issue in READ COMMITTED
Submitted:	15 Sep 2022 7:50	Modified:	19 Sep 2022 13:59
Reporter:	Vinieth S S	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.18, 5.7.35, 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Facing Gap locking issues for simple delete query in READ COMMITTED isolation level.

!gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

In the doc it is mentioned that gap locking for READ COMMITTED is used only for FK constraint and duplicate-key checking. 

For us it occurring for simple delete query.

How to repeat:
Step to reproduce:

Table structure:
mysql> show create table test;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` bigint(19) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 10 | a    |
+----+------+
1 row in set (0.00 sec)

Transaction 1:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where id=10;
Query OK, 1 row affected (0.00 sec)

Transaction 2:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where id>1 and id<9;
*****Here delete query is waiting for lock*****

Transaction 3:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where id>11 and id<19;
Query OK, 0 rows affected (0.00 sec)

Here if you see gap lock is done for supremum(in transaction 2) and not for infimum(in transaction 3). But according to READ COMMITTED doc there shouldn't be any gap lock for this query.

And also found one interesting thing that here even though the delete query is const criteria, the explain plan type shows range, whereas for the same where condition in select query type is const in explain plan. Not sure whether it is related with this gap lock issue.

Explain for delete query:
mysql> explain delete from test where id = 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | DELETE      | test  | NULL       | range | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

Explain for select query:
mysql> explain select * from test where id = 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

Hi Mr. S S,

Thank you for your bug report.

You are very correct in your analysis, but you did make a small mistake.

Namely, in order for the locking to work as you described, your first transaction should have been committed. But, you did not do any commit, so the gap between supremum and your row still had to be locked !!!!

Not a bug.

Yes the case which you are mentioned is applicable only for Repeatable-Read, where  as for READ COMMITTED isolation level - Gap lock is not necessary which is mentioned in doc.

Mysql doc link - https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-gap-locks:~:text=Gap%20....

And also this issue is reproducible only for delete and update. Not for insert in READ COMMITTED. Where as in Repeatable-Read it is applicable for all insert, update, delete.

Hi Mr. S S,

You are quite correct in what you write. However, in order for READ-COMMITTED to kick in, concurrent transaction has to be committed. That is elementary for MVCC storage engines, like InnoDB.

Okay thanks. shall we update the doc that gap locking is done for delete and update even-though it is in READ-COMMITTED isolation level.

Hi,

Documentation will be appended, but not in the way that you imply. We must underline that gap locking remains for the writing transactions that are NOT committed.

Thanks for the update