MySQL Bugs: #106629: DELETE fails to delete record after blocking is released

Bug #106629	DELETE fails to delete record after blocking is released
Submitted:	3 Mar 2022 8:11	Modified:	9 Mar 2022 12:21
Reporter:	John Jove	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0.28	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any
Tags:	read committed, read uncommitted, transaction

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.

How to repeat:
/* 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;

Hi Mr. Jove,

Thank you for your bug report.

However, it is not a bug.

That is exactly how those MVCC isolation levels work. Isolation levels are there, primarly, in order to provide the visibility level between concurrent transactions. Hence , for example, repeatable_read will always see only that snapshot that was active when the transaction started. On the other hand, read_committed will present the running transactions with all changes that were committed during the lifetime of the transaction.

The isolation levels have nothing to do with locking, except for the SERIALIZABLE. Simply, InnoDB is based on the pessimistic ACID model and, henceforth,  locks have to be taken for all DML operations that occur during the lifetime of each transaction. In that manner, no two rows can be changed simultaneously by two or more transactions. 

This is all described in our Reference Manual.

Not a bug.

Thank you for your reply.

However, I think it's a bug.

If the first transaction updates the record once and commits, the second transaction deletes this record successfully.

How to repeat:

/* 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 */ COMMIT; -- t2 unblocked
/* t2 */ SELECT * FROM t FOR UPDATE; -- empty set
/* t2 */ ROLLBACK;

It is weird that if the first transaction updates the record again after the second transaction is blocked, the second transaction deletes this record unsuccessfully.

Hi,

This is truly the expected behaviour. Once COMMIT or ROLLBACK are issued at the end of the transaction, then all locks are closed.

Then, all existing rows are available for processing by other transactions.

Not a bug.

Hi,

Thank you for your reply. I agree with your explanation that the second case is normal. 

However, the first case is different. According to the explanation, in the first case, the first transaction commits, then the second transaction should delete the same row successfully, like the second case. But, the second transaction fails to delete the row after the first transaction commits. 

Actually, this problem can be triggered in this case: the first transaction updates the record to a smaller value after the second transaction is blocked, then the second transaction deletes it unsuccessfully. If the first transaction updates the record to a bigger value, then the second transaction deletes this record successfully. 

Here is the third case that the first transaction updates the record to a bigger value:

/* 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 = 6;
/* t1 */ COMMIT; -- t2 unblocked
/* t2 */ SELECT * FROM t FOR UPDATE; -- empty set
/* t2 */ ROLLBACK;