当我运行多次时，同一笔交易会返回不同的结果 [英] Same transaction returns different results when i ran multiply times
问题描述
当我使用TiDB时，当我使两个事务同时运行时，我感到很奇怪.我原本希望得到与MySQL相同的值2，但是我得到的只是0、2、0、2、0、2 ...
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...
对于两个数据库，tx_isolation都设置为已读".因此，合理的是，select语句返回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.
这是测试代码:
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()
表t的创建如下:
CREATE TABLE `t` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`a`)
)
，最初插入(1,0).
and (1,0) is inserted initially.
推荐答案
首先:
对于 TiDB ，仅支持 SNAPSHOT (最新版本) 交易隔离级别.但只能在开始交易之前看到提交的数据.
For TiDB only support SNAPSHOT(latest version) Transactions Isolation Level. but it only can see committed data before Transaction started.
和 TiDB 也不会更新交易中的相同值， 例如 MySQL 和 SQL Server 等.
and TiDB also will not update the same value in transaction, like MySQL and SQL Server etc.
对于 MySQL ，当使用 READ COMMITTED 隔离级别时，它会 将读取已提交数据，因此它将读取其他交易 提交的数据.
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 第一轮工作流程:
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第2轮工作流程:
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 |
+-------------------------+
因此，对于 TiDB ，输出如下:
So for TiDB will output like:
0, 2, 0, 2, 0, 2...
MySQL 工作流程:
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 |
+--------------------------+
因此 MySQL 可以连续输出:
2, 2, 2, 2...
对于 TiDB 在 Transaction 中跳过更新相同的值，这是很奇怪的，但是当不同的值时也可以成功更新，就像我们可以在循环中将b更新为其他值一样，我们总是可以获取最新更改 b.
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
参考文献:
https://github.com/pingcap/docs/blob/master/sql/transaction-isolation.md
这篇关于当我运行多次时，同一笔交易会返回不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
