Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 发现 TiDB 存在数据异常
|
username: TiDBer_rxdTV4dZ
[TiDB Usage Environment] Testing
[TiDB Version] v5.4.0
[Reproduction Path] Operations performed that led to the issue
case 1:
set TXN_ISOLATION = read-committed for each session
----------rat_dda_write_read_skew test prepare----------
Q0-T1 execute sql: 'DROP TABLE IF EXISTS t1;'
Q0-T1 execute sql: 'CREATE TABLE t1 (k INT PRIMARY KEY, v INT);'
Q0-T1 execute sql: 'INSERT INTO t1 VALUES (0, 0);'
Q0-T1 execute sql: 'INSERT INTO t1 VALUES (1, 0);'
Q0-T1 execute opt: 'COMMIT';
----------rat_dda_write_read_skew test run----------
Q1-T1 execute sql: 'BEGIN OPTIMISTIC;'
Q2-T1 execute sql: 'UPDATE t1 SET v=1 WHERE k=0;'
Q3-T2 execute sql: 'BEGIN OPTIMISTIC;'
Q4-T2 execute sql: 'UPDATE t1 SET v=1 WHERE k=1;'
Q5-T2 execute sql: 'SELECT * FROM t1 WHERE k=0;'
current_result:
(0,0)
Q6-T1 execute sql: 'SELECT * FROM t1 WHERE k=1; '
current_result:
(1,0)
Q7-T2 execute opt: 'COMMIT';
Q8-T1 execute opt: 'COMMIT';
Q9-T3 execute sql: 'SELECT * FROM t1 ORDER BY k;'
current_result:
(0,1) (1,1)
Q10-T3 execute opt: 'COMMIT';
case 2:
set TXN_ISOLATION = read-committed for each session
----------rat_dda_write_read_skew_committed test prepare----------
Q0-T1 execute sql: 'DROP TABLE IF EXISTS t1;'
Q0-T1 execute sql: 'CREATE TABLE t1 (k INT PRIMARY KEY, v INT);'
Q0-T1 execute sql: 'INSERT INTO t1 VALUES (0, 0);'
Q0-T1 execute sql: 'INSERT INTO t1 VALUES (1, 0);'
Q0-T1 execute opt: 'COMMIT';
----------rat_dda_write_read_skew_committed test run----------
Q1-T1 execute sql: 'BEGIN OPTIMISTIC;'
Q2-T1 execute sql: 'UPDATE t1 SET v=1 WHERE k=0;'
Q3-T2 execute sql: 'BEGIN OPTIMISTIC;'
Q4-T2 execute sql: 'UPDATE t1 SET v=1 WHERE k=1;'
Q5-T2 execute sql: 'SELECT * FROM t1 WHERE k=0;'
current_result:
(0,0)
Q6-T2 execute opt: 'COMMIT';
Q7-T1 execute sql: 'SELECT * FROM t1 WHERE k=1;'
current_result:
(1,0)
Q8-T1 execute opt: 'COMMIT';
Q9-T3 execute sql: 'SELECT * FROM t1 ORDER BY k;'
current_result:
(0,1) (1,1)
Q10-T3 execute opt: 'COMMIT';
case 3:
set TXN_ISOLATION = read-committed for each session
----------rat_mda_step_rat test prepare----------
Q0-T1 execute sql: 'DROP TABLE IF EXISTS t1;'
Q0-T1 execute sql: 'CREATE TABLE t1 (k INT PRIMARY KEY, v INT);'
Q0-T1 execute sql: 'INSERT INTO t1 VALUES (0, 0);'
Q0-T1 execute sql: 'INSERT INTO t1 VALUES (1, 0);'
Q0-T1 execute sql: 'INSERT INTO t1 VALUES (2, 0);'
Q0-T1 execute opt: 'COMMIT';
----------rat_mda_step_rat test run----------
Q1-T1 execute sql: 'BEGIN OPTIMISTIC;'
Q2-T1 execute sql: 'UPDATE t1 SET v=1 WHERE k=0;'
Q3-T2 execute sql: 'BEGIN OPTIMISTIC;'
Q4-T2 execute sql: 'UPDATE t1 SET v=1 WHERE k=1;'
Q5-T2 execute sql: 'SELECT * FROM t1 WHERE k=0;'
current_result:
(0,0)
Q6-T3 execute sql: 'BEGIN OPTIMISTIC;'
Q7-T3 execute sql: 'UPDATE t1 SET v=1 WHERE k=2;'
Q8-T3 execute sql: 'SELECT * FROM t1 WHERE k=1;'
current_result:
(1,0)
Q9-T1 execute sql: 'SELECT * FROM t1 WHERE k=2;'
current_result:
(2,0)
Q10-T1 execute opt: 'COMMIT';
Q11-T2 execute opt: 'COMMIT';
Q12-T3 execute opt: 'COMMIT';
Q13-T4 execute sql: 'SELECT * FROM t1 ORDER BY k;'
current_result:
(0,1) (1,1) (2,1)
Q14-T4 execute opt: 'COMMIT';
case 4:
set TXN_ISOLATION = read-committed for each session
----------iat_dda_write_skew test prepare----------
Q0-T1 execute sql: 'DROP TABLE IF EXISTS t1;'
Q0-T1 execute sql: 'CREATE TABLE t1 (k INT PRIMARY KEY, v INT);'
Q0-T1 execute sql: 'INSERT INTO t1 VALUES (0, 0);'
Q0-T1 execute sql: 'INSERT INTO t1 VALUES (1, 0);'
Q0-T1 execute opt: 'COMMIT';
----------iat_dda_write_skew test run----------
Q1-T1 execute sql: 'BEGIN OPTIMISTIC;'
Q2-T1 execute sql: 'SELECT * FROM t1 WHERE k=0; '
current_result:
(0,0)
Q3-T2 execute sql: 'BEGIN OPTIMISTIC;'
Q4-T2 execute sql: 'SELECT * FROM t1 WHERE k=1;'
current_result:
(1,0)
Q5-T2 execute sql: 'UPDATE t1 SET v=1 WHERE k=0;'
Q6-T1 execute sql: 'UPDATE t1 SET v=1 WHERE k=1;'
Q7-T1 execute opt: 'COMMIT';
Q8-T2 execute opt: 'COMMIT';
Q9-T3 execute sql: 'SELECT * FROM t1 ORDER BY k;'
current_result:
(0,1) (1,1)
Q8-T3 execute opt: 'COMMIT';
case 5:
set TXN_ISOLATION = read-committed for each session
----------iat_dda_write_skew_committed test prepare----------
Q0-T1 execute sql: 'DROP TABLE IF EXISTS t1;'
Q0-T1 execute sql: 'CREATE TABLE t1 (k INT PRIMARY KEY, v INT);'
Q0-T1 execute sql: 'INSERT INTO t1 VALUES (0, 0);'
Q0-T1 execute sql: 'INSERT INTO t1 VALUES (1, 0);'
Q0-T1 execute opt: 'COMMIT';
----------iat_dda_write_skew_committed test run----------
Q1-T1 execute sql: 'BEGIN OPTIMISTIC;'
Q2-T1 execute sql: 'SELECT * FROM t1 WHERE k=0; '
current_result:
(0,0)
Q3-T2 execute sql: 'BEGIN OPTIMISTIC;'
Q4-T2 execute sql: 'SELECT * FROM t1 WHERE k=1;'
current_result:
(1,0)
Q5-T2 execute sql: 'UPDATE t1 SET v=1 WHERE k=0;'
Q6-T2 execute opt: 'COMMIT';
Q7-T1 execute sql: 'UPDATE t1 SET v=1 WHERE k=1;'
Q8-T1 execute opt: 'COMMIT';
Q9-T3 execute sql: 'SELECT * FROM t1 ORDER BY k;'
current_result:
(0,1) (1,1)
Q10-T3 execute opt: 'COMMIT';
case 6:
set TXN_ISOLATION = read-committed for each session
----------iat_mda_step_iat test prepare----------
Q0-T1 execute sql: 'DROP TABLE IF EXISTS t1;'
Q0-T1 execute sql: 'CREATE TABLE t1 (k INT PRIMARY KEY, v INT);'
Q0-T1 execute sql: 'INSERT INTO t1 VALUES (0, 0);'
Q0-T1 execute sql: 'INSERT INTO t1 VALUES (1, 0);'
Q0-T1 execute sql: 'INSERT INTO t1 VALUES (2, 0);'
Q0-T1 execute opt: 'COMMIT';
----------iat_mda_step_iat test run----------
Q1-T1 execute sql: 'BEGIN OPTIMISTIC;'
Q2-T1 execute sql: 'SELECT * FROM t1 WHERE k=2;'
current_result:
(2,0)
Q3-T2 execute sql: 'BEGIN OPTIMISTIC;'
Q4-T2 execute sql: 'SELECT * FROM t1 WHERE k=0;'
current_result:
(0,0)
Q5-T3 execute sql: 'BEGIN OPTIMISTIC;'
Q6-T3 execute sql: 'SELECT * FROM t1 WHERE k=1;'
current_result:
(1,0)
Q7-T1 execute sql: 'UPDATE t1 SET v=1 WHERE k=0;'
Q8-T2 execute sql: 'UPDATE t1 SET v=1 WHERE k=1;'
Q9-T3 execute sql: 'UPDATE t1 SET v=1 WHERE k=2;'
Q10-T1 execute opt: 'COMMIT';
Q11-T2 execute opt: 'COMMIT';
Q12-T3 execute opt: 'COMMIT';
Q13-T4 execute sql: 'SELECT * FROM t1 ORDER BY k;'
current_result:
(0,1) (1,1) (2,1)
Q14-T4 execute opt: 'COMMIT';
[Encountered Issue: Problem Phenomenon and Impact]
In these cases, there are read-write dependencies between transactions. In such situations, we expect at least one transaction to roll back, but in reality, none did, resulting in data anomalies.
For this issue, we have also submitted an issue on GitHub: some data anomalies in read-committed isolation for optimistic transactions · Issue #41681 · pingcap/tidb · GitHub
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]
|
username: