Note:
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.
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.
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?
Yes, you can refer to the official website for a detailed explanation.
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.
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:
SESSION1:
SQL>begin;
SESSION2:
SQL>begin;
SQL>update test set name=‘DDD’ where id=4;
SQL>commit;
SESSION1:
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?
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. 
[2022/07/29 17:41:59.575 +08:00] [INFO] [conn.go:1115] [“command dispatched failed”] [conn=493] [connInfo=“id:493, addr:10.1.48.44:45680 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]
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?
This topic will be automatically closed 60 days after the last reply. No new replies are allowed.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: