Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4, 11.8, 12.3, 12.2.2
-
MariaDB Server 12.2.2
Storage engine: InnoDB
innodb_snapshot_isolation = ON
Isolation levels tested: REPEATABLE READ and SERIALIZABLE
Description
Description:
I found a possible inconsistency between REPEATABLE READ and SERIALIZABLE when innodb_snapshot_isolation=ON.
In the following case, Transaction 10 deletes the only row in the table but does not commit. Then Transaction 11 and Transaction 12 both execute:
SELECT c0 FROM t0 WHERE TRUE FOR UPDATE; |
Both statements are blocked by Transaction 10.
Under REPEATABLE READ, after Transaction 10 commits, both [11-1] and [12-1] are unblocked and return an empty set.
However, under SERIALIZABLE, after Transaction 10 commits, [11-1] is unblocked, but [12-1] remains blocked until Transaction 11 commits.
This looks suspicious because both SELECT statements are already explicit SELECT ... FOR UPDATE. SERIALIZABLE should not introduce a difference here due to implicit conversion of plain SELECT statements.
How to repeat:
DROP TABLE IF EXISTS t0; |
CREATE TABLE t0( |
c0 MEDIUMINT PRIMARY KEY NOT NULL, |
c1 VARCHAR(100) NOT NULL |
) ENGINE=InnoDB;
|
CREATE INDEX ic0 ON t0(c1); |
INSERT INTO t0 VALUES (1, ''); |
Execute the statements in this order under SERIALIIZABLE:
[10-0] BEGIN; |
[11-0] BEGIN; |
[12-0] BEGIN; |
[10-1] DELETE FROM t0 WHERE TRUE; |
[11-1] SELECT c0 FROM t0 WHERE TRUE FOR UPDATE; |
-- blocked by Transaction 10 |
[12-1] SELECT c0 FROM t0 WHERE TRUE FOR UPDATE; |
-- blocked by Transaction 10 |
[10-2] COMMIT; |
[11-2] COMMIT; |
[12-2] COMMIT; |
Observed result under SERIALIZABLE:
After [10-2] COMMIT, [11-1] is unblocked, but [12-1] remains blocked until [11-2] COMMIT.
Observed result under REPEATABLE READ:
Using the same schedule with:
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
After [10-2] COMMIT, both [11-1] and [12-1] are unblocked and return an empty set.
Expected result:
SERIALIZABLE should behave consistently with REPEATABLE READ in this case, because the statements are already explicit locking reads:
SELECT c0 FROM t0 WHERE TRUE FOR UPDATE; |
Since Transaction 10 has committed the DELETE, the row no longer exists. Both waiting SELECT ... FOR UPDATE statements should be unblocked and return an empty set, as they do under REPEATABLE READ.
Why this looks suspicious:
The main extra behavior of SERIALIZABLE is related to plain SELECT statements being treated as locking reads in explicit transactions. But this test case does not use plain SELECT. Both statements are already SELECT ... FOR UPDATE.
Therefore, it is unexpected that SERIALIZABLE keeps [12-1] blocked after [10-2] COMMIT, while REPEATABLE READ allows both waiting statements to return an empty set.