Reasons for Write-Write Conflicts in TiDB

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

Original topic: tidb 出现写写冲突的原因

| username: Raymond

Version: v6.1.0
I have a question. Today, while using sysbench to benchmark TiDB, I encountered a Write conflict. The transaction model used is tidb_txn_mode set to pessimistic. I don’t understand why this Write conflict is occurring.

| username: h5n1 | Original post link

In pessimistic mode, when autocommit is enabled, it will first commit in optimistic mode. If there is a conflict, it will switch to pessimistic mode. Compared to optimistic mode, pessimistic mode locks the key during the DML phase, which can block other sessions before committing. During the commit, it still executes in the optimistic mode’s prewrite+commit manner, and there may be write-write conflicts at this stage.

| username: Raymond | Original post link

Teacher, in pessimistic mode, when autocommit is enabled, it will first commit in optimistic mode, and if there is a conflict, it will switch to pessimistic mode. For example, suppose the autocommit system variable is set to ON, and I directly execute an update statement (without begin and commit statements). When this update statement is finally committed, it is committed according to the optimistic transaction mode, right? (There is no need to check if the data is locked when modifying it; instead, it checks for write conflicts or lock conflicts at the commit stage.) Is this understanding correct?

| username: songxuecheng | Original post link

This is the same issue. You can refer to it.

| username: h5n1 | Original post link

Yes, you can refer to the official website for a detailed explanation.

| username: ddhe9527 | Original post link

In scenarios with severe conflicts or when a write conflict occurs, optimistic transactions will terminate directly, while pessimistic transactions will attempt to retry the statement with the latest data until there is no write conflict. Since TiDB’s locking operation is a write operation and the process involves reading before writing, it requires 2 RPCs. If a write conflict occurs during this process, it will retry. Each retry will log an entry, which does not require special attention. The number of retries is defined by pessimistic-txn.max-retry-count.

| username: HACK | Original post link

Write-write conflicts occur during the prewrite phase. When it is detected that another transaction is writing the current key (data.commit_ts > txn.start_ts), a write-write conflict will occur.

How should this data.commit_ts > txn.start_ts be understood? My test is as follows:


SQL>update test set name=‘DDD’ where id=4;

SQL>insert into test values(4,‘ddddddd’);
ERROR 1062 (23000): Duplicate entry ‘4’ for key ‘PRIMARY’
Checking the tidb.log, it shows an error message indicating a pessimistic write conflict.
My SESSION2 has already committed, so why is it still showing a write-write conflict?

| username: ddhe9527 | Original post link

What you’re asking about is related to write-write conflicts in optimistic transactions, but it seems like your test is using pessimistic transactions.

I followed your test process, and with pessimistic transactions, there was no “pessimistic write conflict” error. :joy:

[2022/07/29 17:41:59.575 +08:00] [INFO] [conn.go:1115] [“command dispatched failed”] [conn=493] [connInfo=“id:493, addr: status:11, collation:utf8_general_ci, user:root”] [command=Query] [status=“inTxn:1, autocommit:1”] [sql=“insert into sbtest99 values(1,200000,‘18018173308’,‘18018173308’)”] [txn_mode=PESSIMISTIC] [err=“[kv:1062]Duplicate entry ‘1’ for key ‘PRIMARY’”]

If it were an optimistic transaction, SESSION1 wouldn’t report an error during the insert; it would report a write conflict during COMMIT, not a Duplicate entry error.

ERROR 9007 (HY000): Write conflict, txnStartTS=434919573708603394, conflictStartTS=434919572109524993, conflictCommitTS=434919580091809794, key={tableID=759, indexID=1, indexValues={200000, 1, }} primary={tableID=759, indexID=1, indexValues={200000, 1, }} [try again later]

| username: Raymond | Original post link

Excuse me, teacher. Our TiDB version here is 5.3.1, set to pessimistic transaction mode, with the system variable autocommit set to ON and tidb_disable_txn_auto_retry set to ON. If the statement is automatically committed after execution, it will first be committed in optimistic transaction mode. If a write-write conflict is encountered during the commit process, will the transaction switch to pessimistic transaction mode, and then retry the write-write conflict in pessimistic transaction mode? During the retry process, will it reacquire the transaction’s start.tso, re-execute the statement, and restart the two-phase commit? Is this the process?

| username: system | Original post link

This topic will be automatically closed 60 days after the last reply. No new replies are allowed.