This document introduces the principles of TiDB's optimistic transaction model. This document assumes that you have a basic understanding of TiDB architecture, Percolator, and the ACID properties of transactions.
In TiDB's optimistic transaction model, the two-phase commit begins right after the client executes the
COMMIT statement. Therefore, the write-write conflict can be observed before the transactions are actually committed.
Starting from v3.0.8, TiDB uses the pessimistic transaction model by default. However, this does not affect your clusters if you upgrading from v3.0.7 or earlier to v3.0.8 (and later). In other words, only newly created clusters default to using the pessimistic transaction model.
TiDB adopts Google's Percolator transaction model, a variant of two-phase commit (2PC) to ensure the correct completion of a distributed transaction. The procedure is as follows:
The client begins a transaction.
TiDB receives the start version number (monotonically increasing in time and globally unique) from PD and mark it as
The client issues a read request.
start_tsversion from TiKV.
The client issues a write request.
TiDB checks whether the written data satisfies consistency constraints (to ensure the data types are correct and the unique index is met etc.) Valid data is stored in the memory.
The client issues a commit request.
TiDB begins 2PC to ensure the atomicity of distributed transactions and persist data in store.
TiDB returns a message to inform the client that the transaction is successfully committed.
TiDB asynchronously cleans the locks left in this transaction.
From the process of transactions in TiDB above, it is clear that TiDB transactions have the following advantages:
However, TiDB transactions also have the following disadvantages:
To avoid potential problems in application, refer to transaction sizes to see more details.
TiDB uses optimistic concurrency control by default whereas MySQL applies pessimistic concurrency control. This means that MySQL checks for conflicts during the execution of SQL statements, so there are few errors reported in heavy contention scenarios. For the convenience of MySQL users, TiDB provides a retry function that runs inside a transaction.
If there is a conflict, TiDB retries the write operations automatically. You can set
tidb_retry_limit to enable or disable this default function:
# Whether to disable automatic retry. ("on" by default) tidb_disable_txn_auto_retry = off # Set the maximum number of the retires. ("10" by default) # When “tidb_retry_limit = 0”, automatic retry is completely disabled. tidb_retry_limit = 10
You can enable the automatic retry in either session level or global level:
set @@tidb_disable_txn_auto_retry = off;
set @@tidb_retry_limit = 10;
set @@global.tidb_disable_txn_auto_retry = off;
set @@global.tidb_retry_limit = 10;
tidb_retry_limitvariable decides the maximum number of retries. When this variable is set to
0, none of the transactions automatically retries, including the implicit single statement transactions that are automatically committed. This is the way to completely disable the automatic retry mechanism in TiDB. After the automatic retry is disabled, all conflicting transactions report failures (includes the
try again laterstring) to the application layer in the fastest way.
By default, TiDB will not retry transactions because this might lead to lost updates and damaged
REPEATABLE READ isolation.
The reason can be observed from the procedures of retry:
In Step 2, TiDB only retries SQL statements that contain write operations. However, during retrying, TiDB receives a new version number to mark the beginning of the transaction. This means that TiDB retries SQL statements with the data in the new
start_ts version. In this case, if the transaction updates data using other query results, the results might be inconsistent because the
REPEATABLE READ isolation is violated.
If your application can tolerate lost updates, and does not require
REPEATABLE READ isolation consistency, you can enable this feature by setting
tidb_disable_txn_auto_retry = off.
For the optimistic transaction, it is important to detect whether there are write-write conflicts in the underlying data. Although TiKV reads data for detection in the prewrite phase, a conflict pre-detection is also performed in the TiDB clusters to improve the efficiency.
Because TiDB is a distributed database, the conflict detection in the memory is performed in two layers:
The conflict detection in the TiDB layer is disabled by default. The specific configuration items are as follows:
[txn-local-latches] # Whether to enable the latches for transactions. Recommended # to use latches when there are many local transaction conflicts. # ("false" by default) enabled = false # Controls the number of slots corresponding to Hash. ("204800" by default) # It automatically adjusts upward to an exponential multiple of 2. # Each slot occupies 32 Bytes of memory. If set too small, # it might result in slower running speed and poor performance # when data writing covers a relatively large range. capacity = 2048000
The value of the
capacity configuration item mainly affects the accuracy of conflict detection. During conflict detection, only the hash value of each key is stored in the memory. Because the probability of collision when hashing is closely related to the probability of misdetection, you can configure
capacity to controls the number of slots and enhance the accuracy of conflict detection.
capacity, the smaller the occupied memory and the greater the probability of misdetection.
capacity, the larger the occupied memory and the smaller the probability of misdetection.
When you confirm that there is no write-write conflict in the upcoming transactions (such as importing data), it is recommended to disable the function of conflict detection.
TiKV also uses a similar mechanism to detect conflicts, but the conflict detection in the TiKV layer cannot be disabled. You can only configure
scheduler-concurrency to control the number of slots that defined by the modulo operation:
# Controls the number of slots. ("2048000" by default） scheduler-concurrency = 2048000
In addition, TiKV supports monitoring the time spent on waiting latches in scheduler.
Scheduler latch wait duration is high and there is no slow writes, it can be safely concluded that there are many write conflicts at this time.