DELETE query is waiting for lock for uncommitted row in mysql - Isolation level READ-COMMITTED

Question

Mysql version 5.7.18, Isolation level : READ-COMMITTED

Table structure
mysql> show create table City;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| City  | CREATE TABLE `City` (
  `CITYID` bigint(19) NOT NULL,
  `NAME` varchar(250) NOT NULL,
  PRIMARY KEY (`CITYID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from City;
+--------+-------+
| CITYID | NAME  |
+--------+-------+
|      1 | Tokyo |
|      2 | Tokyo |
|      3 | Tokyo |
|      4 | Tokyo |
+--------+-------+
4 rows in set (0.01 sec)

Transaction 1:

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

mysql> insert into City values (8,"Newyork");
Query OK, 1 row affected (0.01 sec)

Transaction 1 not committed yet

Transaction 2:

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

mysql> explain delete from City where NAME="Tokyo" and CITYID>=3 and CITYID<=10;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | DELETE      | City  | NULL       | range | PRIMARY       | PRIMARY | 8       | const |    3 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set (0.01 sec)

mysql> delete from City where NAME="Tokyo" and CITYID>=3 and CITYID<=10;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Transaction 2 is waiting for lock for row CITYID=8 which is not committed yet by Transaction 1.

Transaction and lock 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 |
+----------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 11219772 | LOCK WAIT | 2023-07-18 17:14:03 | 11219772:0:31449:6    | 2023-07-18 17:14:03 |          5 |                 135 | delete from City where NAME="Tokyo" and CITYID>=3 and CITYID<=10 | fetching rows       |                 1 |                 1 |                3 |                  1136 |               3 |                 2 |                       0 | READ COMMITTED      |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
| 11219771 | RUNNING   | 2023-07-18 17:13:56 | NULL                  | NULL                |          3 |                 136 | 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 |
+--------------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| 11219772:0:31449:6 | 11219772    | X         | RECORD    | `test`.`city` | PRIMARY    |          0 |     31449 |        6 | 8         |
| 11219771:0:31449:6 | 11219771    | X         | RECORD    | `test`.`city` | PRIMARY    |          0 |     31449 |        6 | 8         |
+--------------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
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   |
+-------------------+--------------------+-----------------+--------------------+
| 11219772          | 11219772:0:31449:6 | 11219771        | 11219771:0:31449:6 |
+-------------------+--------------------+-----------------+--------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.processlist;
+-----+------+-----------+------+---------+------+-----------+------------------------------------------------------------------+
| ID  | USER | HOST      | DB   | COMMAND | TIME | STATE     | INFO                                                             |
+-----+------+-----------+------+---------+------+-----------+------------------------------------------------------------------+
| 136 | root | localhost | test | Sleep   |   20 |           | NULL                                                             |
| 135 | root | localhost | test | Query   |   13 | updating  | delete from City where NAME="Tokyo" and CITYID>=3 and CITYID<=10 |
| 139 | root | localhost | NULL | Query   |    0 | executing | select * from INFORMATION_SCHEMA.processlist                     |
+-----+------+-----------+------+---------+------+-----------+------------------------------------------------------------------+
3 rows in set (0.00 sec)

------------
TRANSACTIONS
------------
Trx id counter 11219777
Purge done for trx's n:o < 11219777 undo n:o < 0 state: running but idle
History list length 27
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422059188686400, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 11219772, ACTIVE 13 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 135, OS thread handle 13023637504, query id 1078757 localhost root updating
delete from City where NAME="Tokyo" and CITYID>=3 and CITYID<=10
------- TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 31449 n bits 72 index PRIMARY of table `test`.`city` trx id 11219772 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 8000000000000008; asc         ;;
 1: len 6; hex 000000ab333b; asc     3;;;
 2: len 7; hex b400001a7f0110; asc        ;;
 3: len 7; hex 4e6577796f726b; asc Newyork;;

------------------
---TRANSACTION 11219771, ACTIVE 20 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 136, OS thread handle 13021409280, query id 1078756 localhost root

Why Transaction 2 is waiting for lock of uncommitted row by Transaction 1 if set isolation level READ-COMMITED.

Answer 1

As you say...

Transaction 2 is waiting for lock for row CITYID=8 which is not committed yet by Transaction 1.

That seems to say it all.

Note that CITYID is the only column in the index; NAME is not considered when deciding on "locking".

Put another way... InnoDB locking mechanisms are optimized for typical cases, hence some uncommon cases lead to unnecessary locks.