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:

create database x;

use x;

create table t(a int, b int);

insert into t values(0, 0);

then in two terminals, simultaneously (and line by line):

start transaction;

update t set b = 1 where a = 0;

commit;

and

start transaction;

update t set b = 2 where a = 0;

commit;

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.

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