Write Conflict Issue

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: Write conflict写写冲突问题

| username: Zealot

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

2
| username: dba远航 | Original post link

Use the command: show variables like '%tidb_txn_mode%'; to check which one is being used. Pessimistic lock should be pessimistic.

3
| username: Zealot | Original post link

Executing select @@session.tidb_txn_mode shows “pessimistic”, no issues.

4
| username: 有猫万事足 | Original post link

The auto-commit transaction model in TiDB can be different from the explicitly set transaction model. By default, auto-commit uses the optimistic transaction model, which is controlled by the parameter in the documentation link above.

5
| username: 有猫万事足 | Original post link

However, write conflicts still exist. Changing to pessimistic transactions means that write conflict error logs are replaced by pessimistic lock waits. The conflicts are still there; only the handling mechanism has changed.
When necessary, for the sake of database performance, it’s still important to manage locks outside the database to ensure no conflicts when entering the database.

For example, using Redis/Zookeeper to manage distributed locks.

6
| username: Zealot | Original post link

Got it, I enabled autocommit mainly to enable tidb_batch_insert. I just checked, the batch-DML approach has been deprecated. Currently, it is recommended to use non-transactional DML statements. It looks like I need to consider modifying the SQL.

7
| username: Zealot | Original post link

Well, non-transactional DML statements must also be autocommit, and there are too many restrictions on non-transactional DML. It seems that updates can only be done after starting a transaction.

8
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.