MySQL Bugs: #117797: Lost Update WITH CONSISTENT SNAPSHOT

Bug #117797	Lost Update WITH CONSISTENT SNAPSHOT
Submitted:	26 Mar 5:55	Modified:	4 Jun 2:18
Reporter:	Huicong Xu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	9.2.0	OS:	Any (Ubuntu 22.04)
Assigned to:		CPU Architecture:	Any

Description:
Hi.
When I tested the database, I found a problem.
As in the example below, two transactions update the same row under repeatable isolation level. In my opinion, this is a lost update exception where transaction 2 lost the update made by transaction 1.

--- I see
--- session 0 (init database)
DROP TABLE tTZguDqi;
CREATE TABLE tTZguDqi (ID INT, VAL INT, c0 DOUBLE    UNIQUE KEY, c1 TEXT , c2 BOOLEAN );
INSERT INTO tTZguDqi (ID, VAL, c0, c1, c2) VALUES (1222, 1508, 8548.0247, "9G", 0);
INSERT INTO tTZguDqi (ID, VAL, c0, c1, c2) VALUES (1241, 1531, 2807.8848, "z", 1);
INSERT INTO tTZguDqi (ID, VAL, c0, c1, c2) VALUES (1242, 1532, 6756.6910, '2091851934', 1);
--- session 1
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
--- session 2
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
--- session 1
INSERT INTO tTZguDqi (ID, VAL, c0, c1, c2) VALUES ( 1243, 1533, 694.5452, '-1959894384', NULL);
Query OK, 1 row affected (0.00 sec)
--- session 2
SELECT ID, VAL, tTZguDqi.c0, tTZguDqi.c1, tTZguDqi.c2 FROM tTZguDqi WHERE ( tTZguDqi.c0 = 8548.0247 ) AND (tTZguDqi.c0 >= 100) ORDER BY tTZguDqi.ID;
+------+------+-----------+------+------+
| ID   | VAL  | c0        | c1   | c2   |
+------+------+-----------+------+------+
| 1222 | 1508 | 8548.0247 | 9G   |    0 |
+------+------+-----------+------+------+
1 row in set (0.00 sec)
--- session 1
UPDATE tTZguDqi SET VAL = 1534, tTZguDqi.c0 = 6432.9682 WHERE ( tTZguDqi.c0 = 8548.0247 ) AND (tTZguDqi.c0 >= 100);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
COMMIT;
--- session 2
UPDATE tTZguDqi SET VAL = 1535, tTZguDqi.c2 = 0 WHERE ( tTZguDqi.c0 < 7359.4115 AND tTZguDqi.c0 >= 2572.4906 ) AND (tTZguDqi.c0 >= 100);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
COMMIT;

--- Expected to see (MariaDB show)
...
mysql> UPDATE tTZguDqi SET VAL = 1535, tTZguDqi.c2 = 0 WHERE ( tTZguDqi.c0 < 7359.4115 AND tTZguDqi.c0 >= 2572.4906 ) AND (tTZguDqi.c0 >= 100);
ERROR 1020 (HY000): Record has changed since last read in table 'tTZguDqi'
...

How to repeat:
--- session 0 (init database)
DROP TABLE tTZguDqi;
CREATE TABLE tTZguDqi (ID INT, VAL INT, c0 DOUBLE    UNIQUE KEY, c1 TEXT , c2 BOOLEAN );
INSERT INTO tTZguDqi (ID, VAL, c0, c1, c2) VALUES (1222, 1508, 8548.0247, "9G", 0);
INSERT INTO tTZguDqi (ID, VAL, c0, c1, c2) VALUES (1241, 1531, 2807.8848, "z", 1);
INSERT INTO tTZguDqi (ID, VAL, c0, c1, c2) VALUES (1242, 1532, 6756.6910, '2091851934', 1);
--- session 1
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
--- session 2
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
--- session 1
INSERT INTO tTZguDqi (ID, VAL, c0, c1, c2) VALUES ( 1243, 1533, 694.5452, '-1959894384', NULL);
--- session 2
SELECT ID, VAL, tTZguDqi.c0, tTZguDqi.c1, tTZguDqi.c2 FROM tTZguDqi WHERE ( tTZguDqi.c0 = 8548.0247 ) AND (tTZguDqi.c0 >= 100) ORDER BY tTZguDqi.ID;
--- session 1
UPDATE tTZguDqi SET VAL = 1534, tTZguDqi.c0 = 6432.9682 WHERE ( tTZguDqi.c0 = 8548.0247 ) AND (tTZguDqi.c0 >= 100);
COMMIT;
--- session 2
UPDATE tTZguDqi SET VAL = 1535, tTZguDqi.c2 = 0 WHERE ( tTZguDqi.c0 < 7359.4115 AND tTZguDqi.c0 >= 2572.4906 ) AND (tTZguDqi.c0 >= 100);
COMMIT;

Hi,

I believe this is not a bug but I will verify so that optimizer team can doublecheck.

Posted by developer:
 
Hi, I believe this is not a bug - MySQL seems to be using a different definition of REPEATABLE READ than Maria DB.
In MySQL's implementation of InnoDB, a REPEATABLE READ transaction sees one of two worlds depending on the nature of particular query:
- for non-locking SELECTS we use a read-view 
- for UPDATEs,DELETEs,INSERTs and locking SELECTs we use the most recent state of the DB
More importantly, we make no promise or effort to check that the two worlds are consistent with each other.
Apparently Maria DB goes an extra mile to verify that the rows which are accessed during UPDATEs have not been updated since the read-view creation.
We don't. IMO it would be a major effort to modify InnoDB to do that properly, as it wouldn't be backward-compatible.
As such, I don't think we can address such feature request via bug report/bug fix.

Note that the behaviour you see is documented:
https://dev.mysql.com/doc/refman/9.3/en/innodb-transaction-isolation-levels.html
> It is not recommended to mix locking statements (UPDATE, INSERT, DELETE, or SELECT ... FOR ...) with non-locking SELECT statements in a single REPEATABLE READ transaction, because typically in such cases you want SERIALIZABLE. This is because a non-locking SELECT statement presents the state of the database from a read view which consists of transactions committed before the read view was created, and before the current transaction's own writes, while the locking statements use the most recent state of the database to use locking. In general, these two different table states are inconsistent with each other and difficult to parse.

We judge this bug based on the definition of lost update, which is really inconsistent with the MySQL implementation. In the example above, we do see the lost update occurring. Similar to the bug mentioned in the paper with Semantic Conformance Testing of Relational DBMSs.