# How to Make TiDB Handle Conflicting Transactions Like MySQL

## <span style="font-size:10;">Contributed by: Daniël van Eeden</span>

## Summary

To compare MySQL and TiDB transaction handling, a customer tried experimenting with this script:

```{.sql .copyable .custom-prompt .prompt-MySQL}
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):

```{.sql .copyable .custom-prompt .prompt-MySQL}
start transaction;

update t set b = 1 where a = 0;

commit;
```

and

```{.sql .copyable .custom-prompt .prompt-MySQL}
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)[https://docs.pingcap.com/tidb/stable/pessimistic-transaction](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](https://docs.pingcap.com/tidb/stable/pessimistic-transaction).

## Applies To

All TiDB releases

**#TiDB #Locking #Pessimistic-locking #MySQL-Compatibility**