some data anomalies in read-committed isolation for optimistic transactions

[Slontia]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

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';

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

All transactions commit successfully.

3. What did you see instead (Required)

One of the transactions should be aborted.

4. What is your TiDB version? (Required)

v5.4.0

[zyguan]

Similar to #41680, use select ... for update to avoid these issues (wr-wr cycle & rw-rw cycle).