Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5.14, 10.6.6, 10.7.3
-
OS: Ubuntu-20.04 LTS
Description
Isolation Level: Read Committed & Read Uncommitted
One transaction modifies a row, the other transaction concurrently deletes that row and is blocked. After blocking is released, the second transaction DELETE fails.
/* init */ DROP TABLE IF EXISTS t; |
/* init */ CREATE TABLE t(c1 INT PRIMARY KEY, c2 INT); |
/* init */ INSERT INTO t(c1) VALUES (8); |
|
/* t1 */ BEGIN; |
/* t2 */ BEGIN; |
/* t1 */ UPDATE t SET c1 = 5, c2 = 5; |
/* t2 */ DELETE FROM t; -- blocked |
/* t1 */ UPDATE t SET c1 = 3; |
/* t1 */ COMMIT; -- t2 unblocked |
/* t2 */ SELECT * FROM t FOR UPDATE; -- [(3, 5)] |
/* t2 */ ROLLBACK; |
Attachments
Issue Links
- is caused by
-
MDEV-27025 insert-intention lock conflicts with waiting ORDINARY lock
-
- Closed
-
- relates to
-
MDEV-29433 innodb.lock_delete_updated is unstable
-
- Closed
-
-
MDEV-24813 Locking full table scan fails to use table-level locking
-
- In Review
-
- mentioned in
-
Page Failed to load
-
Page Failed to load
-
Page Loading...
-
Page Loading...
-
Page Loading...
Here is a simpler, executable version of the test, again (like in
MDEV-27922) using COMMIT instead of ROLLBACK because COMMIT is a simpler operation for InnoDB:--source include/have_innodb.inc
let $wait_condition=
--source include/wait_condition.inc
reap;
disconnect con1;
I tested some versions of MariaDB where no wrong result was returned. In those cases, DELETE reported an error:
mariadb-10.6.5
mysqltest: At line 24: query 'reap' failed: 1213: Deadlock found when trying to get lock; try restarting transaction
I verified the wrong result (no error reported for DELETE) also for 10.5.14 and 10.6.6. If I revert the
MDEV-27025fix, the deadlock error will be reported.I debugged this with a version of the server that includes the
MDEV-27025(and returns the reported result). At the time the lock_wait() returns to the DELETE statement, the m_prebuilt->pcur was already positioned on the meanwhile delete-marked record (5,5).At the time lock_wait() was invoked, the table contained the record (5,5) and the delete-marked record (8,NULL). So, the problem appears to be that after lock_wait() is resumed and the current record is committed as delete-marked, we fail to notice that the record had been replaced with one with a smaller PRIMARY KEY value (3,5). That is why the record (3,5) will survive the execution of the DELETE statement.
In this special case where DELETE is operating on the entire table, escalating to table-level locking (MDEV-24813) would prevent this wrong result.
vlad.lesin, please check if we could do anything else to prevent this at a lower level.