Bug #120204 UPDATE statements fail to update rows or acquire locks after being released

• Submitted: 2 Apr 7:52
• 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, UPDATE

[2 Apr 7:52] Rose Yang

Description:
Under the Read Uncommitted and Read Committed isolation levels, two blocked UPDATE statements are released simultaneously. They should perform an update operation and acquire locks, but in reality, they neither update any row nor acquire any locks.

How to repeat:
/* init */ CREATE TABLE t (c1 INT, c2 INT, PRIMARY KEY (c2, c1));
/* init */ INSERT INTO t (c1,c2) VALUES (39,16);

/* s11 */ BEGIN;
/* s12 */ UPDATE t SET c2 = 6;
/* s13 */ INSERT INTO t VALUES (81,9);
/* s21 */ BEGIN; 
/* s22 */ UPDATE IGNORE t SET c1 = 116, c2 = 3;  -- blocked
/* s31 */ BEGIN;
/* s32 */ UPDATE t SET c1 = 167;  -- 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:110:34466:130749743289152     |               1055109 |        54 |       69 | test2         | t           | NULL           | NULL              | NULL       |       130749743289152 | TABLE     | IX            | GRANTED     | NULL      |
| INNODB | 130749818318848:196:34466:130749743295248     |               1055112 |        55 |       41 | test2         | t           | NULL           | NULL              | NULL       |       130749743295248 | TABLE     | IX            | GRANTED     | NULL      |
| INNODB | 130749818319656:16:34466:130749743301232      |               1055113 |        56 |       41 | test2         | t           | NULL           | NULL              | NULL       |       130749743301232 | TABLE     | IX            | GRANTED     | NULL      |
| INNODB | 130749818318040:110:33400:4:2:130749743286160 |               1055109 |        54 |       69 | test2         | t           | NULL           | NULL              | PRIMARY    |       130749743286160 | RECORD    | X,REC_NOT_GAP | GRANTED     | 16, 39    |
| INNODB | 130749818318040:110:33400:4:3:130749743286160 |               1055109 |        54 |       69 | test2         | t           | NULL           | NULL              | PRIMARY    |       130749743286160 | RECORD    | X,REC_NOT_GAP | GRANTED     | 6, 39     |
| INNODB | 130749818318040:110:33400:4:4:130749743286160 |               1055109 |        54 |       69 | test2         | t           | NULL           | NULL              | PRIMARY    |       130749743286160 | RECORD    | X,REC_NOT_GAP | GRANTED     | 9, 81     |
| INNODB | 130749818318848:196:33400:4:2:130749743292336 |               1055112 |        55 |       41 | test2         | t           | NULL           | NULL              | PRIMARY    |       130749743292336 | RECORD    | X,REC_NOT_GAP | WAITING     | 16, 39    |
| INNODB | 130749818319656:16:33400:4:2:130749743298320  |               1055113 |        56 |       41 | test2         | t           | NULL           | NULL              | PRIMARY    |       130749743298320 | RECORD    | X,REC_NOT_GAP | WAITING     | 16, 39    |
+--------+-----------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
8 rows in set (0.002 sec)
/* s13 */ COMMIT;
/* s22 */ UPDATE IGNORE t SET c1 = 116, c2 = 3; -- s22 unblocked
/* s32 */ UPDATE t SET c1 = 167;  -- s32 unblocked
/* 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:196:34466:130749743295248 |               1055112 |        55 |       41 | test2         | t           | NULL           | NULL              | NULL       |       130749743295248 | TABLE     | IX        | GRANTED     | NULL      |
| INNODB | 130749818319656:16:34466:130749743301232  |               1055113 |        56 |       41 | test2         | t           | NULL           | NULL              | NULL       |       130749743301232 | TABLE     | IX        | GRANTED     | NULL      |
+--------+-------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
2 rows in set (0.001 sec)
/* s33 */ COMMIT;
/* s23 */ COMMIT;
/* s43 */ SELECT c1, c2 FROM t;  -- [ (39, 6), (81, 9)]

After s13 commits, s22 and s32 are released simultaneously. s22 and s32 do not update any rows, nor do they acquire any locks.