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;
