Deadlock in Batch Data Update

Translated
1

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

Original topic: 批量更新数据死锁

| username: Hacker_0LElpqkb

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version] 6.5.1
[Reproduction Path] Operations performed that led to the issue
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachment: Screenshot/Logs/Monitoring]

Failed to execute update statement, how to troubleshoot deadlock

unexpected resolve err: commit_ts_expired:<start_ts:443481858696045745 attempted_commit_ts:443481858696070679 key:“t\200\000\000\000\000\000\002j_r\200\000\000\000\003\314Q\264” min_commit_ts:443481864372486146 > , lock: key: 74800000000000026a5f728000000003cc51b4, primary: 74800000000000021d5f698000000000000001013838373434303432ff3039373535303535ff3938000000000000f9, txnStartTS: 443481858696045745, lockForUpdateTS:443481858696054340, minCommitTs:443481864372486146, ttl: 44600, type: Put, UseAsyncCommit: t

2
| username: FutureDB | Original post link
  1. First, you should investigate why locks are appearing. Can you avoid locks through business logic design? If so, that would be best.
  2. Secondly, implement lock control in the business logic code before updating, such as waiting for the lock or setting retries.
  3. Finally, avoid setting too large a data volume for batch updates. This not only easily causes lock issues but also can lead to OOM (Out of Memory). Try to update data with the same primary key in the same batch as much as possible.
3
| username: Hacker_0LElpqkb | Original post link

select * from information_schema.data_lock_waits
At that time, I did not see any locks related to this update through the query, and the number of updated data was not very large, about 6k rows. :upside_down_face:

4
| username: redgame | Original post link

Logical issues cannot be avoided.

5
| username: TiDBer_aaO4sU46 | Original post link

Checking SQL logic has overwhelmed the database…

6
| username: TiDBer_RjzUpGDL | Original post link

Adjust the order of acquiring locks in the transaction.

7
| username: TiDBer_rvITcue9 | Original post link

select * from information_schema.data_lock_waits