How to Make TiDB Handle Conflicting Transactions Like MySQL
Contributed by: Daniël van Eeden
Summary
To compare MySQL and TiDB transaction handling, a customer tried experimenting with this script:
then in two terminals, simultaneously (and line by line):
and
TiDB:
· starts the two transactions.
· executes the two updates.
· the second commit fails with write conflict:
ð ERROR 9007 (HY000): Write conflict, txnStartTS=425476103985692672, conflictStartTS=425476103189299200, conflictCommitTS=425476113872715776, key={tableID=251, handle=1} primary=[]byte(nil) [try again later]
MySQL (5.7):
· starts the two transactions.
· The second update hangs until the first transaction is committed.
· The second transaction is only committed and updated after the first one completes.
Is there a way to configure TiDB to behave like MySQL in this case?
Diagnosis
The key insight is that TiDB handles conflicts using optimistic locking. This leads to the proposed solution in the next section:
Solution
This appears to be an issue with pessimistic vs. optimistic transactions. You can change the following setting:https://docs.pingcap.com/tidb/stable/pessimistic-transaction
SET GLOBAL tidb_txn_mode = 'pessimistic';
or set it per transaction.
Related Articles
This document also has a section on differences between InnoDB and TiDB that might be of interest to you:
TiDB Pessimistic Transaction Model.
Applies To
All TiDB releases
#TiDB #Locking #Pessimistic-locking #MySQL-Compatibility
Comments
0 comments
Article is closed for comments.