unexpected behavior of a transaction for the same data item

[dlyixue]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

/* init */ CREATE TABLE t0 (c0 INT);
/* init */ INSERT INTO t0 (c0) VALUES(1),(2);

/* t1 */ BEGIN;
/* t2 */ BEGIN;
/* t1 */ SELECT * FROM t0;
/* t2 */ SELECT * FROM t0;
/* t1 */ UPDATE t0 SET c0=123;
/* t2 */ DELETE FROM t0 WHERE ((t0.c0)>=(10));
/* t1 */ SELECT * FROM t0;
/* t1 */ COMMIT;
/* t2 */ SELECT * FROM t0;
/* t2 */ COMMIT;

INSERT INTO t0 (c0) VALUES(1),(2); /* After transaction 1,2 commit and before transaction 3,4 start */

/* t3 */ BEGIN;
/* t4 */ BEGIN;
/* t3 */ SELECT * FROM t0;
/* t4 */ SELECT * FROM t0;
/* t3 */ UPDATE t0 SET c0=123;
/* t4 */ DELETE FROM t0 WHERE ((t0.c0)>=(10));
/* t3 */ SELECT * FROM t0;
/* t3 */ COMMIT;
/* t4 */ SELECT * FROM t0;
/* t4 */ COMMIT; 

2. What did you expect to see? (Required)

mysql> /* t4 */ SELECT * FROM t0;
+------+
| c0   |
+------+
|    1 |
|    2 |
+------+

3. What did you see instead (Required)

mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)
mysql> /* t1 */ BEGIN;
mysql> /* t2 */ BEGIN;
mysql> /* t1 */ SELECT * FROM t0;
+------+
| c0   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
mysql> /* t2 */ SELECT * FROM t0;
+------+
| c0   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
mysql> /* t1 */ UPDATE t0 SET c0=123;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0
mysql> /* t2 */ DELETE FROM t0 WHERE ((t0.c0)>=(10)); // Not Block
Query OK, 0 rows affected (0.00 sec)
mysql> /* t1 */ SELECT  *  FROM t0;
+------+
| c0   |
+------+
|  123 |
|  123 |
+------+
2 rows in set (0.00 sec)
mysql> /* t1 */ COMMIT;
mysql> /* t2 */ SELECT * FROM t0;
+------+
| c0   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
mysql> /* t2 */ COMMIT;

mysql> INSERT INTO t0 (c0) VALUES(1),(2); /* After transaction 1,2 commit and before transaction 3,4 start */
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> /* t3 */ BEGIN;
mysql> /* t4 */ BEGIN;
mysql> /* t3 */ SELECT * FROM t0;
+------+
| c0   |
+------+
|  123 |
|  123 |
|    1 |
|    2 |
+------+
4 rows in set (0.01 sec)
mysql> /* t4 */ SELECT * FROM t0;
+------+
| c0   |
+------+
|  123 |
|  123 |
|    1 |
|    2 |
+------+
4 rows in set (0.00 sec)
mysql> /* t3 */ UPDATE t0 SET c0=123;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 4  Changed: 2  Warnings: 0
mysql> /* t4 */ DELETE FROM t0 WHERE ((t0.c0)>=(10)); // Block 
Query OK, 4 rows affected (8.01 sec)
mysql> /* t3 */ SELECT  *  FROM t0;
+------+
| c0   |
+------+
|  123 |
|  123 |
|  123 |
|  123 |
+------+
4 rows in set (0.00 sec)
mysql> /* t3 */ COMMIT;
mysql> /* t4 */ SELECT * FROM t0;
Empty set (0.00 sec) // **Logic Bug**
mysql> /* t4 */ COMMIT; 

4. What is your TiDB version? (Required)

Release Version: v8.1.0 Edition: Community Git Commit Hash: 945d07c5d5c7a1ae212f6013adfb187f2de24b23 Git Branch: HEAD UTC Build Time: 2024-05-21 03:51:57 GoVersion: go1.21.10 Race Enabled: false Check Table Before Drop: false Store: tikv

5. Logic

According to the definition of isolation level RR, transaction T4 should not see changes from transaction T3, just as transaction T2 does not see changes from T1. Therefore, DB should not delete the entire table.

[jebter]

In my test, the delete statement of T4 will be blocked

MySQL root@127.0.0.1:test> DELETE FROM t0 WHERE ((t0.c0)>=(10));
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
(1205, 'Lock wait timeout exceeded; try restarting transaction')
MySQL root@127.0.0.1:test> select tidb_version() \G;
***************************[ 1. row ]***************************
tidb_version() | Release Version: v8.1.0
Edition: Community
Git Commit Hash: 945d07c5d5c7a1ae212f6013adfb187f2de24b23
Git Branch: HEAD
UTC Build Time: 2024-05-21 03:53:20
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv

1 row in set
Time: 0.002s
MySQL root@127.0.0.1:test> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

[jebter]

@cfzjywxk fyi

[crazycs520]

...
mysql> /* t4 */ SELECT * FROM t0;
Empty set (0.00 sec) // **Logic Bug**
...

MySQL 8.0.29 has the same behavior as TiDB.

[MyonKeminta]

There seem to be some misunderstandings. Note that:

• TiDB's pessimistic transaction in RR isolation level is designed to be compatible with MySQL's RR, but doesn't yet support gap locks;
• DML statements in MySQL's RR transaction actually have part of RC behavior. It can see changes that are committed after the current transaction's BEGIN if there are concurrent write on the data that the current DML is trying to read.

You may already know that the rows updated to 123 by t1/t3 can't be seen by t2/t4 due to Phantom which is allowed in RR. In your example, t3 semantically writes all rows in the table, including the two 123s which is visible to t4's DELETE statement. As a result t4's DELETE statement is blocked and wait until the write to the two 123s finishes. The same behavior doesn't happen on t1 and t2, as nothing update by t1 is visible to t2's DELETE statement

[dlyixue]

...
mysql> /* t4 */ SELECT * FROM t0;
Empty set (0.00 sec) // **Logic Bug**
...

MySQL 8.0.29 has the same behavior as TiDB.

Thanks for your reply. But，when I test T1 and T2 in MySQL, I see the difference with TiDB. In MySQL, I see that T2 is blocked and deletes all rows, but not inTiDB.

[dlyixue]

There seem to be some misunderstandings. Note that:

• TiDB's pessimistic transaction in RR isolation level is designed to be compatible with MySQL's RR, but doesn't yet support gap locks;
• DML statements in MySQL's RR transaction actually have part of RC behavior. It can see changes that are committed after the current transaction's BEGIN if there are concurrent write on the data that the current DML is trying to read.

You may already know that the rows updated to 123 by t1/t3 can't be seen by t2/t4 due to Phantom which is allowed in RR. In your example, t3 semantically writes all rows in the table, including the two 123s which is visible to t4's DELETE statement. As a result t4's DELETE statement is blocked and wait until the write to the two 123s finishes. The same behavior doesn't happen on t1 and t2, as nothing update by t1 is visible to t2's DELETE statement

Thank you for your explanation. May I ask whether the execution results of our test case should appear under the theoretical RR isolation level? If RR have part of RC behavior, how do users choose isolation levels.