Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 乐观锁下写写冲突导致慢sql
[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
[Reproduction Path] What operations were performed to cause the issue
[Encountered Issue: Problem Phenomenon and Impact]
Write-write conflicts under optimistic transactions lead to slow updates or inserts (taking several seconds, with minimal modifications, using index modifications). Can this only be resolved by reducing concurrency and changing parallelism to serial execution? Is there any other way if pessimistic transactions are not used?
Currently, the concurrency is not high, just a few concurrent operations.
tidb_disable_txn_auto_retry retry can cause data loss, automatic retry is not feasible.
In the slow query log, there is txnLock:
Prewrite_time: 4.738867105 Wait_prewrite_binlog_time: 0.000000467 Commit_backoff_time: 4.736 Backoff_types: [txnLock txnLock txnLock txnLock txnLock] Resolve_lock_time: 0.000006231 Write_keys: 9 Write_size: 793 Prewrite_region: 3
In the tidb log, there is Write conflict
[2023/08/21 21:48:03.278 +08:00] [WARN] [session.go:425] [sql] [conn=747870656] [label=general] [error=“[kv:9007]Write conflict, txnStartTS=443711666075730001, conflictStartTS=443711667071877217, conflictCommitTS=443711667071877226, key={tableID=1765, handle=436418090659116} primary={tableID=1765, indexID=7, indexValues={180357100, 1, 1, 1692625676, 436418090659116, }} [try again later]”] [txn=“Txn{state=invalid}”]
The document states that the application logic needs to be modified. How should the logic be modified?
[Resource Configuration]
Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]
Hi~ Your version is too low, I suggest upgrading to a higher version~
Many issues in the lower versions have already been fixed in the higher versions~
You don’t have to struggle anymore!
Refer to this to troubleshoot the issue: 乐观事务模型下写写冲突问题排查 | PingCAP 文档中心
Refer to this best practice: TiDB Best Practice Series (III) Optimistic Lock Transaction | PingCAP
Starting from TiDB v3.0.8, newly created TiDB clusters will use pessimistic transaction mode by default. The current read (for update read) in pessimistic transactions is non-repeatable read. Or as my cousin said, upgrading the TiDB version can also utilize new features to avoid many issues.
Write-write conflicts also exist in higher versions, right? It can now be confirmed that there are write-write conflicts. The screenshot I posted above is from the 7.1 documentation of this link. The documentation says “see if the application logic can be modified.” How should the logic be modified? Change concurrency to serial execution?
Since it was upgraded from an older version, the parameter is still optimistic transactions. If I now change it globally to pessimistic transactions, will it have a significant impact on the existing business? (The business connects to TiDB through middleware, and the middleware does not support session-level parameter modification)
Yes, when you upgrade from an older version, it won’t be enabled automatically. You need to enable it manually. Before doing so, you can thoroughly read the issue. There are similar issues in the community, and you can also check other people’s answers.
My understanding of solving it from the application side is to place the lock outside the database, such as using Redis/Zookeeper for locking. This ensures there are no conflicts when accessing the database.
Just my personal opinion.
You can take a look at all of them.
Optimistic locking should be used in situations where conflicts are basically non-existent.
Have you changed the transaction isolation level?
We had a similar situation before, upgrading from a lower version and always using optimistic locking, which caused severe lock conflicts. Switching to pessimistic locking solved the problem. Just make sure to restart the application after making the change.
|
username: TiDBer_yUoxD0vR
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: