MySQL Bugs: #115078: RC isolation happen dead lock like gap lock

Bug #115078	RC isolation happen dead lock like gap lock
Submitted:	22 May 2024 9:54	Modified:	22 May 2024 13:43
Reporter:	HULONG CUI	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.37	OS:	Any (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	For Update

Description:
when RC transaction_isolation，using where condition primary key  ，
 for update sql have dead lock。like gap lock

How to repeat:
DROP TABLE IF EXISTS T1;
CREATE TABLE `t1` (
  `id` int NOT NULL,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
#not insert data 9 ：
INSERT INTO t1(id)  VALUES (1),(2),(3),(4),(5),(6),(7),(8),(10);

Session1:
SET session transaction_isolation='READ-COMMITTED';
BEGIN;
SELECT  *  FROM  t1 WHERE  id = 10  FOR UPDATE;

Session2:
SET session transaction_isolation='READ-COMMITTED';
BEGIN;

"SELECT  * FROM t1 WHERE  ID >= 7 AND   ID <= 8;
+----+---------------------+
| id | update_time         |
+----+---------------------+
|  7 | 2024-05-21 18:43:33 |
|  8 | 2024-05-21 18:43:33 |
+----+---------------------+
2 rows in set (0.00 sec)"
"SELECT  * FROM t1 WHERE  ID >= 7 AND   ID <= 8 FOR UPDATE;
等待中。。。
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction"

when  insert data 9
INSERT INTO T1(id) values(9);
not happen dead lock

Hi Mr. Cui,

Thank you for your bug report.

However, we can not repeat it with our 8.0.37 binary:

Session 1
----------

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `t1` (   `id` int NOT NULL,   `update_time` datetime DEFAULT CURRENT_TIMESTAMP,   PRIMARY KEY (`id`) ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO t1(id)  VALUES (1),(2),(3),(4),(5),(6),(7),(8),(10);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql>
mysql> SET session transaction_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT  *  FROM  t1 WHERE  id = 10  FOR UPDATE;
+----+---------------------+
| id | update_time         |
+----+---------------------+
| 10 | 2024-05-22 13:10:10 |
+----+---------------------+
1 row in set (0.01 sec)

Immediately after that in session 2:

mysql> SET session transaction_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

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

mysql>
mysql> SELECT  * FROM t1 WHERE  ID >= 7 AND   ID <= 8;
+----+---------------------+
| id | update_time         |
+----+---------------------+
|  7 | 2024-05-22 13:10:10 |
|  8 | 2024-05-22 13:10:10 |
+----+---------------------+
2 rows in set (0.00 sec)

mysql>
mysql> SELECT  * FROM t1 WHERE  ID >= 7 AND   ID <= 8 FOR UPDATE;
+----+---------------------+
| id | update_time         |
+----+---------------------+
|  7 | 2024-05-22 13:10:10 |
|  8 | 2024-05-22 13:10:10 |
+----+---------------------+
2 rows in set (0.00 sec)

Can't repeat.

In any case, you got a timeout, which is expected behaviour. You did NOT get a deadlock.

this is show engine innodb status\G; 

RANSACTIONS
------------
Trx id counter 389169
Purge done for trx's n:o < 389166 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421141811792536, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421141811790112, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421141811789304, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 389168, ACTIVE 18 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 10, OS thread handle 139666253866752, query id 20 localhost root executing
SELECT  * FROM t1 WHERE  ID >= 7 AND   ID <= 8 FOR UPDATE
------- TRX HAS BEEN WAITING 18 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 661 page no 4 n bits 80 index PRIMARY of table `demo`.`t1` trx id 389168 lock_mode X locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000005f028; asc      (;;
 2: len 7; hex 81000000f0015e; asc       ^;;
 3: len 5; hex 99b36d3777; asc   m7w;;

------------------
---TRANSACTION 389167, ACTIVE 26 sec
2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 9, OS thread handle 139666262791936, query id 17 localhost root

root@localhost: 19:37:  [demo]> select *  from performance_schema.data_locks \G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139666835081072:1935:139666901151008
ENGINE_TRANSACTION_ID: 389170
            THREAD_ID: 48
             EVENT_ID: 9
        OBJECT_SCHEMA: demo
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139666901151008
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139666835081072:661:4:8:139667195062304
ENGINE_TRANSACTION_ID: 389170
            THREAD_ID: 48
             EVENT_ID: 9
        OBJECT_SCHEMA: demo
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139667195062304
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: WAITING
            LOCK_DATA: 10
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139666835080264:1935:139666901150240
ENGINE_TRANSACTION_ID: 389169
            THREAD_ID: 47
             EVENT_ID: 9
        OBJECT_SCHEMA: demo
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139666901150240
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139666835080264:661:4:8:139667195059232
ENGINE_TRANSACTION_ID: 389169
            THREAD_ID: 47
             EVENT_ID: 9
        OBJECT_SCHEMA: demo
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139667195059232
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 10
4 rows in set (0.00 sec)

innodb status

Attachment: lock info20240522.txt (text/plain), 7.82 KiB.

lock info imag

Attachment: lock 20240522.png (image/png, text), 130.57 KiB.

Hi Mr. CUI,

What you have posted in your comments is expected behaviour.

InnoDB is transactional storage engine, so it locks necessary rows , gaps and similar .......

Also, every lock that is waited to be granted has timeout. This is all according to standard ......

Also, your output clearly shows that there is not a single deadlock.

This is all also very thoroughly explained in our Reference Manual, which you can find on https://dev.mysql.com.

Not a bug.

SQL1= SELECT  *  FROM  t1 WHERE  id = 10  FOR UPDATE;
SQL2 = SELECT  * FROM t1 WHERE  ID >= 7 AND   ID <= 8 FOR UPDATE;

my question is SQL1 and SQL2  ,at  where condition using primary key ,sql1 and sql2 result is not same , but sql2 why wait lock sql1? 
At the READ-COMMITTEDisolation level, this non compliant behavior。

Hi,

What you describe is 100 % compliant behaviour.

All isolation levels issue the same locks. The only exception is SERIALIZABLE.

Hence, READ COMMITTED takes the same locks as the other isolation levels. The only difference is in the visibility of the changes made by concurrent transactions ......  Locking rows and gaps are totally the same in all isolation levels, except for SERIALIZABLE.

This is all described in our Reference Manual.

Not a bug .