Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: Write conflict写写冲突问题
[TiDB Version]
V7.1.2
[Reproduction Path]
A large number of data updates on a single table, two simple update statements executed simultaneously, reporting write conflict, with set @@session.autocommit = 1;
[Problem Encountered: Phenomenon and Impact]
Write conflict, txnStartTS=445789926295863313, conflictStartTS=445789926020612100, conflictCommitTS=445789926308970497, key={tableID=1063399, tableName=ods_extend.qi_asr_result, handle=2933907}, originalKey=7480000000001039e75f7280000000002cc493, primary={tableID=1063399, tableName=ods_extend.qi_asr_result, indexID=2, indexValues={0, 2933907, }}, originalPrimaryKey=7480000000001039e75f6980000000000000020380000000000000000380000000002cc493, reason=Optimistic [try again later]
Looking at this exception, it seems to be caused by the use of optimistic locking, but according to the documentation, TiDB uses pessimistic locking by default. “When using the pessimistic transaction mode, autocommit transactions first attempt to commit using the less costly optimistic transaction mode. If a write conflict occurs, the retry will use the pessimistic transaction mode to commit. Therefore, when tidb_retry_limit = 0
, autocommit transactions encountering a write conflict will still report a Write Conflict
error.” I also checked my tidb_retry_limit, and this value is 10. How can I resolve this issue?
I have set these parameters:
SET @@SESSION.autocommit = 1;
SET @@SESSION.tidb_batch_insert = 1;
SET @@SESSION.tidb_batch_delete = 1;
SET @@SESSION.tidb_dml_batch_size = 1000;
SET @@SESSION.tidb_replica_read = ‘leader-and-follower’;
SET @@SESSION.tidb_mem_quota_query = 16073741824;
SET @@SESSION.group_concat_max_len = 102400;
SET @@SESSION.tidb_enable_paging = 1;
SET @@cte_max_recursion_depth = 11000;