can not retry txn的原因

🪐 TiDB 技术问题
1

版本v5.3 调整了乐观锁的重试开关
日志提示重试失败,还是报出了写写冲入
如何排查这个重试失败原因
[session.go:721] [“can not retry txn”] [conn=121] [label=general] [error="[kv:9007]Write conflict, txnStartTS=429928699468972383, conflictStartTS=429928699468972373, conflictCommitTS=429928699468972416, key={tableID=3946, indexID=1, indexValues={2503fce6-b923-42ab-9e2a-1d399fae712d, 58fd4d48-cbe5-430c-8cca-8b5b39469bd5, 92e14d5f-528c-49bb-925a-5a0bb79a0e15, 98477245402689717, YH0002, 1, 4433, 6917529027713163827, }} primary={tableID=3946, indexID=6, indexValues={1902511, }} [try again later]"] [IsBatchInsert=false] [IsPessimistic=true] [InRestrictedSQL=false] [tidb_retry_limit=256] [tidb_disable_txn_auto_retry=false]

似乎和这个issues有关系?https://github.com/pingcap/tidb/issues/25964
但是这个issues已经被显示解决了

2 
[session.go:721] [“can not retry txn”] [conn=121] [label=general] [error="[kv:9007]Write conflict, 
txnStartTS=429928699468972383, 
conflictStartTS=429928699468972373, conflictCommitTS=429928699468972416, key={tableID=3946, indexID=1, indexValues={2503fce6-b923-42ab-9e2a-1d399fae712d, 58fd4d48-cbe5-430c-8cca-8b5b39469bd5, 92e14d5f-528c-49bb-925a-5a0bb79a0e15, 98477245402689717, YH0002, 1, 4433, 6917529027713163827, }} 
primary={tableID=3946, indexID=6, indexValues={1902511, }} 
[try again later]"] [IsBatchInsert=false] [IsPessimistic=true] [InRestrictedSQL=false] [tidb_retry_limit=256] [tidb_disable_txn_auto_retry=false]

IsPessimistic=true
这里显示还是悲观锁模式

3

乐观锁重试的时候就会变成悲观模式吧 你的意思是?

4

不是，虽然你调整了乐观锁重试次数，但是业务上还是使用的悲观锁模式。

5

如果在悲观锁模式就更不会出现这个写写冲突了吧 在1阶段就上锁了 不会再prewrite提交事务的时候写冲突了

6

如果是悲观锁模式，在prewrite阶段就会进行检查。

image
image771×584 25.7 KB

7

我也遇到相同的问题，悲观锁模式下为何会出现写冲突呢？ 还有这个图是乐观锁模式的步骤吧？

8

下面链接的第5条提到，悲观事务模式下，autocommit事务首先尝试使用开销更小的乐观事务模式提交。如果发生了写冲突，重试时才会使用悲观事务提交 https://docs.pingcap.com/zh/tidb/stable/pessimistic-transaction#和-mysql-innodb-的差异

9

这是我的报错日志，可以看到当时事务的参数就是用的悲观锁事务
[2022/07/17 18:34:23.377 +08:00] [WARN] [session.go:721] [“can not retry txn”] [conn=5567757] [label=general] [error=“[kv:9007]Write conflict, txnStartTS=434648924385705994, conflictStartTS=434648924385705995, conflictCommitTS=434648924385706003, key={tableID=89, indexID=5, indexValues={1717698306, 1850243498146817280,1850243106666971136, 637967337, }} primary={tableID=89, indexID=1, indexValues={110121367726914335, }} [try again later]”] [IsBatchInsert=false] [IsPessimistic=true] [InRestrictedSQL=false] [tidb_retry_limit=10] [tidb_disable_txn_auto_retry=true]

[2022/07/17 18:34:23.377 +08:00] [WARN] [session.go:737] [“commit failed”] [conn=5567757] [“finished txn”=“Txn{state=invalid}”] [error=“[kv:9007]Write conflict,txnStartTS=434648924385705994, conflictStartTS=434648924385705995, conflictCommitTS=434648924385706003, key={tableID=89, indexID=5, indexValues={1717698306, 1850243498146817280, 1850243106666971136, 637967337, }} primary={tableID=89, indexID=1, indexValues={110121367726914335, }} [try again later]”]

[2022/07/17 18:34:23.377 +08:00] [WARN] [session.go:1583] [“run statement failed”] [conn=5567757] [schemaVersion=96] [error=“previous statement: /*DW: primary=true :/update trade_order_seller SET seller_feature = ‘{"incomeStatus":0}’, delivery_no = ‘DN101005’ where seller_id = 44 and sub_order_no = ‘11’: [kv:9007]Write conflict, txnStartTS=434648924385705994, conflictStartTS=434648924385705995, conflictCommitTS=434648924385706003, key={tableID=89, indexID=5, indexValues={1717698306, 1850243498146817280, 1850243106666971136, 637967337, }} primary={tableID=89, indexID=1, indexValues={110121367726914335, }} [try again later]”] [session=“{
"currDBName": "db",
"id": ,
"status":0,
"strictMode": true,
"user": {
"Username": "db",
"Hostname": "10.240.",
"CurrentUser": false,
"AuthUsername": "db",
"AuthHostname": "%"
}
}”]

[2022/07/17 18:34:23.377 +08:00] [INFO] [conn.go:1069] [“command dispatched failed”] [conn=5567757] [connInfo=“id:5567757, addr:10.240.61.77:24602 status:0, collation:utf8_general_ci, user:dw_trade_order_seller_db”] [command=Query] [status=“inTxn:0, autocommit:0”] [sql=commit] [txn_mode=PESSIMISTIC] [err=“[kv:9007]Write conflict, txnStartTS=434648924385705994, conflictStartTS=434648924385705995, conflictCommitTS=434648924385706003, key={tableID=89, indexID=5, indexValues={1717698306, 1850243498146817280, 1850243106666971136, 637967337, }} primary={tableID=89, indexID=1, indexValues={110121367726914335, }} [try again later]
previousstatement: /DW: primary=true :DW/update trade_order_seller SET seller_feature = ‘{"incomeS}’, delivery_no = ‘DN10100’ where seller_id = 17 and sub_order_no = ‘11’”]

10

hello~ 不要在旧贴追问，记得发多一个一个帖子进行交流

1 个赞
13

该问题是由TiDBbug导致，可升级为5.3.2版本解决。
https://github.com/tikv/tikv/issues/11612