MySQL Bugs: #119949: DELETE statement fails to delete the row after blocking is resumed

Bug #119949	DELETE statement fails to delete the row after blocking is resumed
Submitted:	26 Feb 14:21	Modified:	3 Mar 9:28
Reporter:	Dai Dinary	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	9.5.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Isolation Level: Read Committed & Read Uncommitted
Transaction tx1 modifies a row, transaction tx2 concurrently deletes that row and is blocked. After tx2 is resumed, it fails to delete the row.

How to repeat:
/* init */ CREATE TABLE t(c1 INT PRIMARY KEY);
/* init */ INSERT INTO t(c1) VALUES (5);
 
/* tx1 */ BEGIN;
/* tx2 */ BEGIN;
/* tx1 */ UPDATE t SET c1 = 3;
/* tx2 */ DELETE FROM t; -- tx2 is blocked
/* tx1 */ REPLACE INTO t(c1) VALUES (1) ;
/* tx1 */ COMMIT; -- tx2 is resumed
/* tx2 */ COMMIT;

SELECT * FROM t; -- [(1)]

Maybe related: Bug#119769 and Bug#119801, both or which are non-processed yet (opened 24 Jan and 28 Jan).

Why exactly do you think it is a bug?
IIUC this doesn't happen if you use SERIALIZABLE, right?
So, you are using non-SERIALIZABLE isolation level, in which we don't have the expectation that the transactions should behave as-if they were in some serialized order.
I mean, IIUC, the behaviour you see is consistent with a theory that tx2 was iterating over rows from left to right, and then tx2 inserted row to the left of its cursor, hence it missed it. This is one of many possible things which can happen in READ COMMITTED as it doesn't lock the gaps between rows.
What part of the documentation does the behaviour you observe violate, if any?

The DELETE statement of transaction t2 is resumed after transaction t1 commits. Intuitively, at this point, the smaller value inserted by transaction t1 should be visible to t2. The documentation does not clarify whether statements using current read (such as DELETE), after being resumed, can see values committed by other transactions. If, due to the implementation you mentioned, it is expected that t2 cannot see the smaller value committed by t1, I hope relevant explanations can be added to the documentation.

https://dev.mysql.com/doc/refman/9.6/en/innodb-transaction-isolation-levels.html

> READ COMMITTED  
> [...]
> For [...], DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. [...]
> Because gap locking is disabled, phantom row problems may occur, as other sessions can insert new rows into the gaps. For information about phantom rows, see Section 17.7.4, “Phantom Rows”. 

1. Is the documentation consistent with your observations? I think it is.
2. Is the documentation consistent with your understanding? I believe it isn't. 

I'd prefer to close this issue as "not a bug". Let me know if I am missing something

According to the first test case, when the inserted value (1) is smaller than the updated value (3), the resumed DELETE does not delete the newly inserted row. However, according to the following second test case, when the inserted value (4) is larger than the updated value (3), the resumed DELETE does delete the newly inserted row. Thank you for explaining the reason; I just hadn't found an explanation in the documentation for why it fails to delete a smaller value but can delete a larger value. Here is the second test case:

/* init */ CREATE TABLE t(c1 INT PRIMARY KEY);
/* init */ INSERT INTO t(c1) VALUES (5);
 
/* tx1 */ BEGIN;
/* tx2 */ BEGIN;
/* tx1 */ UPDATE t SET c1 = 3;
/* tx2 */ DELETE FROM t; -- tx2 is blocked
/* tx1 */ REPLACE INTO t(c1) VALUES (4) ;
/* tx1 */ COMMIT; -- tx2 is resumed
/* tx2 */ COMMIT;

SELECT * FROM t; -- [empty set]