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.

Danube

What is your responsibility for COMMIT of transactions? – Eiderdown

Transaction 1 locks the row where id=10 and not committed yet. Transaction 2 tries to delete rows where id >1 and id<9 only But it is waiting for lock of the row id=10. Transaction 3 tries to delete rows where id>10 and id<19. It is executed successfully. Here we are using innodb engine which uses row level locking. So why transaction 2 is waiting for lock here? – Danube

Transaction 1 has lock for row where id=10. Transaction 2 is trying delete from id>1 and id<9 only. so it dont need row lock on id=10. So here why transaction 2 is waiting for row lock that it is not going to delete. – Danube

Is it likely that both would be in the same 'block' of data space in the table? A reason to COMMIT as soon as possible in production or testing. From the SESSION 1 terminal, you could COMMIT and it would free the block to allow you to still control your testing environment in this low level testing environment, IMHO – Eiderdown

innodb is using row level locking. Here Transacation 1 locks row id=10. So it locks the row id=10. Transaction 2 is trying to delete rows id>1 and id<9. So it should not be waiting for lock for the row id=10. But it is waiting. Transaction 3 is trying to delete rows id>10 and id<19. It executed sucessfully. – Danube

Please try explain delete from test where id=10;COMMIT; for your transaction 1 to confirm even though innodb uses row level locking - your testing will be successful and not WAITing. You may find row level locking also requires BLOCK locking behind the scenes. – Eiderdown

What happens if you change "9" to "8"? – Ambivalence

Changed Transaction 2 query -> delete from test where id>1 and id<8; It is still waiting for lock. – Danube

Changed Transaction 2 query -> delete from test where id>1 and id<8; It is still waiting for lock. @RickJames – Danube

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.

Mecham

But gap locks doesn't occur when the ISOLATION is level READ-COMMITTED right ?. Gap lock in Read committed isolation level – Perrault

Oh even-though isolation is READ-COMMITTED, GAP lock is done when its a Delete Query. Is this case mentioned anywhere by MySQL ? And thanks for the info. – Perrault

I have read a lot of documents but could not find it. However I have tested it in MySQL and MariaDB and the results are the same. – Mecham

@vinieth, please go through this. – Mecham

thanks for sharing the doc. On checking it, that seems specific to mariadb, where a dev while removing this variable innodb_locks_unsafe_for_binlog handling from code instead removing that srv_locks_unsafe_for_binlog specific check that dev removed the whole condition !(srv_locks_unsafe_for_binlog || trx->isolation_level <= TRX_ISO_READ_COMMITTED) – Perrault

MariaDB is a fork of MySQL. So maybe they've adopted pretty much everything in their initial phases. – Mecham