Bug #120199 Two transactions simultaneously acquire the same exclusive lock

• Submitted: 1 Apr 8:00
• Reporter: Rose Yang
• Status: Open
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 9.4.0
• OS: Any
• CPU Architecture: Any
• Tags: block, lock, transaction

[1 Apr 8:00] Rose Yang

Description:
Under Repeatable Read and Serializable isolation levels, two blocked INSERT statements are released simultaneously and acquire the same exclusive lock.

How to repeat:
/* init */ CREATE TABLE t1 (c1 INT PRIMARY KEY);
/* init */ CREATE TABLE t2 (c1 INT, c2 INT, FOREIGN KEY (c1) REFERENCES t1(c1));

/* s11 */ BEGIN;
/* s12 */ UPDATE t2 SET c2 = 1, c1 = 1;
/* s21 */ BEGIN; 
/* s22 */ INSERT INTO t2 VALUES (2,2);  -- blocked
/* s31 */ BEGIN;
/* s32 */ INSERT INTO t2 VALUES (3,3);  -- blocked
/* s41 */ /*sets:allsets*/select * from performance_schema.data_locks;
+--------+-----------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+--------------------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID                                | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME      | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE          | LOCK_STATUS | LOCK_DATA              |
+--------+-----------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+--------------------+-------------+------------------------+
| INNODB | 130749818318040:86:34463:130749743289152      |               1055037 |        54 |       33 | test2         | t2          | NULL           | NULL              | NULL            |       130749743289152 | TABLE     | IX                 | GRANTED     | NULL                   |
| INNODB | 130749818318848:190:34463:130749743295248     |               1055038 |        55 |       25 | test2         | t2          | NULL           | NULL              | NULL            |       130749743295248 | TABLE     | IX                 | GRANTED     | NULL                   |
| INNODB | 130749818319656:10:34463:130749743301232      |               1055039 |        56 |       25 | test2         | t2          | NULL           | NULL              | NULL            |       130749743301232 | TABLE     | IX                 | GRANTED     | NULL                   |
| INNODB | 130749818318040:86:33397:4:1:130749743286160  |               1055037 |        54 |       33 | test2         | t2          | NULL           | NULL              | GEN_CLUST_INDEX |       130749743286160 | RECORD    | X                  | GRANTED     | supremum pseudo-record |
| INNODB | 130749818318848:190:33397:4:1:130749743292336 |               1055038 |        55 |       25 | test2         | t2          | NULL           | NULL              | GEN_CLUST_INDEX |       130749743292336 | RECORD    | X,INSERT_INTENTION | WAITING     | supremum pseudo-record |
| INNODB | 130749818319656:10:33397:4:1:130749743298320  |               1055039 |        56 |       25 | test2         | t2          | NULL           | NULL              | GEN_CLUST_INDEX |       130749743298320 | RECORD    | X,INSERT_INTENTION | WAITING     | supremum pseudo-record |
+--------+-----------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+--------------------+-------------+------------------------+
6 rows in set (0.001 sec)
/* s13 */ ROLLBACK;
/* s22 */ INSERT INTO t2 VALUES (2,2);  -- s22 unblocked
[Error 1452: Cannot add or update a child row: a foreign key constraint fails (`lockres0`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t1` (`c1`))]
/* s32 */ INSERT INTO t2 VALUES (3,3);  -- s32 unblocked
[Error 1452: Cannot add or update a child row: a foreign key constraint fails (`lockres0`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t1` (`c1`))]
/* s42 */ /*sets:allsets*/select * from performance_schema.data_locks;
+--------+-----------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+--------------------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID                                | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME      | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE          | LOCK_STATUS | LOCK_DATA              |
+--------+-----------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+--------------------+-------------+------------------------+
| INNODB | 130749818318848:190:34463:130749743295248     |               1055038 |        55 |       25 | test2         | t2          | NULL           | NULL              | NULL            |       130749743295248 | TABLE     | IX                 | GRANTED     | NULL                   |
| INNODB | 130749818319656:10:34463:130749743301232      |               1055039 |        56 |       25 | test2         | t2          | NULL           | NULL              | NULL            |       130749743301232 | TABLE     | IX                 | GRANTED     | NULL                   |
| INNODB | 130749818319656:10:34462:130749743301320      |               1055039 |        56 |       25 | test2         | t1          | NULL           | NULL              | NULL            |       130749743301320 | TABLE     | IS                 | GRANTED     | NULL                   |
| INNODB | 130749818318848:190:34462:130749743295336     |               1055038 |        55 |       25 | test2         | t1          | NULL           | NULL              | NULL            |       130749743295336 | TABLE     | IS                 | GRANTED     | NULL                   |
| INNODB | 130749818318848:190:33397:4:1:130749743293368 |               1055038 |        55 |       25 | test2         | t2          | NULL           | NULL              | GEN_CLUST_INDEX |       130749743293368 | RECORD    | X                  | GRANTED     | supremum pseudo-record |
| INNODB | 130749818319656:10:33397:4:1:130749743299352  |               1055039 |        56 |       25 | test2         | t2          | NULL           | NULL              | GEN_CLUST_INDEX |       130749743299352 | RECORD    | X                  | GRANTED     | supremum pseudo-record |
| INNODB | 130749818319656:10:33397:4:1:130749743298320  |               1055039 |        56 |       25 | test2         | t2          | NULL           | NULL              | GEN_CLUST_INDEX |       130749743298320 | RECORD    | X,INSERT_INTENTION | GRANTED     | supremum pseudo-record |
| INNODB | 130749818318848:190:33397:4:1:130749743292336 |               1055038 |        55 |       25 | test2         | t2          | NULL           | NULL              | GEN_CLUST_INDEX |       130749743292336 | RECORD    | X,INSERT_INTENTION | GRANTED     | supremum pseudo-record |
| INNODB | 130749818318848:190:33396:4:1:130749743292680 |               1055038 |        55 |       25 | test2         | t1          | NULL           | NULL              | PRIMARY         |       130749743292680 | RECORD    | S                  | GRANTED     | supremum pseudo-record |
| INNODB | 130749818319656:10:33396:4:1:130749743298664  |               1055039 |        56 |       25 | test2         | t1          | NULL           | NULL              | PRIMARY         |       130749743298664 | RECORD    | S                  | GRANTED     | supremum pseudo-record |
+--------+-----------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+--------------------+-------------+------------------------+
10 rows in set (0.001 sec)
/* s23 */ COMMIT;
/* s33 */ COMMIT;

After s13 is rolled back, s22 and s32 are simultaneously released and acquire exclusive locks on the supremum pseudo-record.