Same transaction returns different results when i ran multiply times

Question

When i was using TiDB, I found it strange when i make two transactions run at the same time. I was expecting to get the the same value 2 like what MySQL did, but all i got is like 0, 2, 0, 2, 0, 2...

For both databases, the tx_isolation is set to 'read-committed'. So it is reasonable that the select statement returns 2 as it has already committed.

Here's the test code:

for i in range(10):
    conn1 = mysql.connector.connect(host='',
                                port=4000,
                                user='',
                                password='',
                                database='',
                                charset='utf8')
    conn2 = mysql.connector.connect(host='',
                                port=4000,
                                user='',
                                password='',
                                database='',
                                charset='utf8')

    cur1 = conn1.cursor()
    cur2 = conn2.cursor()

    conn1.start_transaction()
    conn2.start_transaction()

    cur2.execute("update t set b=%d where a=1" % 2)
    conn2.commit()

    cur1.execute("select b from t where a=1")
    a = cur1.fetchone()
    print(a)

    cur1.execute("update t set b=%d where a=1" % 0)
    conn1.commit()

    cur1.close()
    cur2.close()
    conn1.close()
    conn2.close()

The table t is created like this:

CREATE TABLE `t` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
)

and (1,0) is inserted initially.

chengpohi · Answer 1 · 2018-09-08T05:11:04.510

First of All:

For TiDB only support SNAPSHOT(latest version) Transactions Isolation Level. but it only can see committed data before Transaction started.

and TiDB also will not update the same value in transaction, like MySQL and SQL Server etc.

For MySQL, when use the READ COMMITTED isolation level, it will read committed data, so it will read the other transactions committed data.

So as your code snippet:

TiDB round 1 workflow:

               T1                                   T2

     +--------------------+
     | transaction start  |
     |      (b = 0)       |
     +---------+----------+
               |
               |
               |                        +------------------------------+
               | <----------------------+  update `b` to 2, and commit |
               |                        +------------------------------+
               |
               |
   +-----------+-----------+
   | select b should be 0, |
   | since tidb will only  |
   | get the data before   |
   | transaction committed |
   +-----------+-----------+
               |
               v

+------------------------------+
|      update value to 0       |
| (since 0 is equal to the     |
| transaction started value,   |
| tidb will ignore this update)|
+------------------------------+
                                  +
                                  |
                                  |
                                  |
                                  v

                      +-------------------------+
                      |so finally `b` will be 2 |
                      +-------------------------+

TiDB round 2 workflow:

               T1                                   T2

     +--------------------+
     | transaction start  |
     |      (b = 2)       |
     +---------+----------+
               |
               |
               |                        +------------------------------+
               | <----------------------+  update `b` to 2, and commit |
               |                        +------------------------------+
               |
               |
   +-----------+-----------+
   | select b should be 2, |
   | since tidb will only  |
   | get the data before   |
   | transaction committed |
   +-----------+-----------+
               |
               v

+------------------------------+
|      update value to 0       |
| (since 0 is not equal to 2   |
+------------------------------+
                                  +
                                  |
                                  |
                                  |
                                  v

                      +-------------------------+
                      |so finally `b` will be 0 |
                      +-------------------------+

So for TiDB will output like:

0, 2, 0, 2, 0, 2...

MySQL workflow:

                      T1                                   T2


          +----------------------+
          |  transaction start   |
          |       ( b = 0 )      |
          +-----------+----------+
                      |
                      |
                      |
                      |                         +---------------------------+
                      |  <----------------------+update `b` to 2, and commit|
                      |                         +---------------------------+
                      |
                      |
                      v

+--------------------------------------------+
| select b should be 2,                      |
| since use READ COMMITTED isolation level,  |
| it will read committed data.               |
+---------------------+----------------------+
                      |
                      |
                      v

           +--------------------+
           | update value to 0  |
           +--------------------+
                                        +
                                        |
                                        |
                                        |
                                        v

                             +--------------------------+
                             | so finally `b` will be 0 |
                             +--------------------------+

so MySQL can continuously output:

2, 2, 2, 2...

Last of word

I think this is very strange for TiDB to skip the update same value in Transaction, but when with the different value it also can be updated success, like we can update b to different value in the loop, we always can get the latest changed b.

So maybe it should be better keep same behavior between same value and different value.

I have created a issue for this:

https://github.com/pingcap/tidb/issues/7644

References:

https://github.com/pingcap/docs/blob/master/sql/transaction-isolation.md

Same transaction returns different results when i ran multiply times

1 Answers1

First of All:

Last of word

Linked