Locking issue in Delete query Mysql 5.7.18

Question

Facing Locking issues in delete query in mysql 5.7.18. Mysql version - 5.7.18 Isolation level - READ-COMMITTED

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)

Issue:

Transaction 1:

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

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 |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set (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> explain delete from test where id>1 and id<9;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 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 |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> delete from test where id>1 and id<9;

Query is waiting for lock

Further details:

mysql> select * from INFORMATION_SCHEMA.INNODB_TRX;
+---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+--------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id  | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started    | trx_weight | trx_mysql_thread_id | trx_query                            | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+--------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 6643991 | LOCK WAIT | 2022-05-09 19:00:57 | 6643991:2140:3:2      | 2022-05-09 19:01:51 |          2 |                   3 | delete from test where id>1 and id<9 | starting index read |                 1 |                 1 |                2 |                  1136 |               2 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
| 6643990 | RUNNING   | 2022-05-09 19:00:20 | NULL                  | NULL                |          3 |                   4 | NULL                                 | NULL                |                 0 |                 1 |                2 |                  1136 |               1 |                 1 |                       0 | READ COMMITTED      |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
+---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+--------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
2 rows in set (0.01 sec)

mysql> select * from INFORMATION_SCHEMA.INNODB_LOCKS;
+------------------+-------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
| lock_id          | lock_trx_id | lock_mode | lock_type | lock_table         | lock_index | lock_space | lock_page | lock_rec | lock_data |
+------------------+-------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
| 6643991:2140:3:2 | 6643991     | X         | RECORD    | `testSchem`.`test` | PRIMARY    |       2140 |         3 |        2 | 10        |
| 6643990:2140:3:2 | 6643990     | X         | RECORD    | `testSchem`.`test` | PRIMARY    |       2140 |         3 |        2 | 10        |
+------------------+-------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 6643991           | 6643991:2140:3:2  | 6643990         | 6643990:2140:3:2 |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.processlist;
+----+------+-----------+-----------+---------+------+-----------+----------------------------------------------+
| ID | USER | HOST      | DB        | COMMAND | TIME | STATE     | INFO                                         |
+----+------+-----------+-----------+---------+------+-----------+----------------------------------------------+
|  3 | root | localhost | testSchem | Query   |    2 | updating  | delete from test where id>1 and id<9         |
|  4 | root | localhost | testSchem | Sleep   |   93 |           | NULL                                         |
|  6 | root | localhost | NULL      | Query   |    0 | executing | select * from INFORMATION_SCHEMA.processlist |
+----+------+-----------+-----------+---------+------+-----------+----------------------------------------------+
3 rows in set (0.01 sec)

mysql> show engine innodb status;
+--------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+--------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      | 
=====================================
2022-05-09 19:01:53 0x30af81000 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 36 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 10 srv_active, 0 srv_shutdown, 100253 srv_idle
srv_master_thread log flush and writes: 100261
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 32
OS WAIT ARRAY INFO: signal count 29
RW-shared spins 0, rounds 28, OS waits 12
RW-excl spins 0, rounds 51, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 28.00 RW-shared, 51.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 6643992
Purge done for trx's n:o < 6643990 undo n:o < 0 state: running but idle
History list length 6
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421793707003456, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 6643991, ACTIVE 56 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 3, OS thread handle 13068374016, query id 51 localhost root updating
delete from test where id>1 and id<9
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2140 page no 3 n bits 72 index PRIMARY of table `testSchem`.`test` trx id 6643991 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 6; hex 000000656116; asc    ea ;;
 2: len 7; hex 30000003ef0e09; asc 0      ;;
 3: len 1; hex 61; asc a;;

------------------
---TRANSACTION 6643990, ACTIVE 93 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 13068652544, query id 47 localhost root
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
494 OS file reads, 184 OS file writes, 81 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 9, seg size 11, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 4 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 4 buffer(s)
0.00 hash searches/s, 0.08 non-hash searches/s
---
LOG
---
Log sequence number 6401339089
Log flushed up to   6401339089
Pages flushed up to 6401339089
Last checkpoint at  6401339080
0 pending log flushes, 0 pending chkp writes
54 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 303269
Buffer pool size   8192
Free buffers       7650
Database pages     534
Old database pages 213
Modified db pages  3
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 468, created 66, written 113
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 534, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=363, Main thread ID=13062987776, state: sleeping
Number of rows inserted 2392, updated 0, deleted 3, read 2402
0.08 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.08 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
 |
+--------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Transaction 3:

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

mysql> explain delete from test where id>10 and id<19;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 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 |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)

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

Query executed successfully.

Can anyone explain why transaction 2 is waiting for the lock here?

Notable points: Transaction 1 locks a row. Queries which delete values lesser than the locked row are only waiting for lock.

Answer 1

This is crazy case of Gap Locking.

It's pretty strange to just insert id 10 at the beginning. Gap Locking is basically locking the head and tail when you delete. It has to conflict with id 10 that you are using. Since there is no 10th numbered column, MySQL simply locks the gap that would have taken the place. (That is why your 3rd transaction works.)

Try changing the id 10 to 1; and use:

First Transaction T1:

BEGIN;

DELETE FROM test WHERE id=1;


Second Transaction T2:

BEGIN;
DELETE FROM test WHERE id > 1 AND id<10;

I bet it works. Let me know what you think.

*EDIT

As I went through the comment in this answer, I am replying within the answer.

In my use cases and trials, I have found this to contradict with DELETE Queries and insert queries.

Consider following two transactions:

First Transaction T1:

BEGIN;

SELECT * FROM `test` t WHERE t.`id`>1 AND t.`id`<10 FOR UPDATE;

Second Transaction T2:

BEGIN;

INSERT INTO test VALUES(5,'asgar');

First try it with TRANSACTION ISOLATION LEVEL: READ_COMMITTED;

The second transaction does not stay in GAP LOCK

However when I change the ISOLATION LEVEL to REPEATABLE READ:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

and re-do same set of transactions, the second transaction stays in GAP LOCK.

These are in case of insert statements. However for delete statement the ISOLATION LEVEL doesn't seem to affect.