Author: Shirly

The TiDB Best Practices series is a series of tutorials for TiDB users, designed to provide an in-depth introduction to the architecture and principles of TiDB and help users maximize the benefits of TiDB in a production environment. We will share a series of best practice paths for typical scenarios to help you get started, locate and solve problems quickly.

In the first two articles, we respectively introduced common hot issues and evading methods of TiDB high concurrent write and PD scheduling strategy best practices. In this paper, we will introduce the TiDB optimistic transaction principle in a simple way, and give the best practices in various scenarios, hoping that you can benefit from it. At the same time, we also welcome you to provide us with relevant optimization suggestions, to participate in our optimization work.

It is recommended that you understand the overall architecture of TiDB and Percollator transaction model before reading. In addition, this article focuses on principles and best practice paths. The specific TiDB transaction statements can be found in the official documentation.

TiDB transaction definition

TiDB uses the Percolator transaction model to implement distributed transactions.

Speaking of transactions, we have to throw out the basic concept of transactions. Usually we define transactions in terms of ACID (ACID conceptual definition). TiDB implements ACID:

  • A (atomicity) : The atomicity of distributed transactions is realized based on the atomicity of single instances. Like the Percolator paper, TiDB is guaranteed by using the atomicity of the region where the Primary Key is located.
  • C (consistency) : the TiDB checks data consistency before writing data to the memory. Data is written to the memory only after the verification succeeds.
  • I (Isolation) : Isolation is mainly used to handle concurrent scenarios, TiDB currently supports only one isolation level Repeatable Read, i.e. Repeatable Read within a transaction.
  • D (persistence) : once the transaction is successfully committed, all data will be persisted to TiKV, and data will not be lost even if the TiDB server goes down.

At the time of writing, TiDB offers two transaction modes: optimistic and pessimistic. So what’s the difference between optimistic and pessimistic events? The essential difference is when to detect conflicts:

  • Pessimistic transactions: As the name implies, pessimistic, with conflicts detected for each SQL entry.
  • Optimistic transactions: Conflicts are detected only when the transaction finally commits.

We will focus on the implementation of optimistic transactions in TiDB. In addition, if you want to learn more about pessimistic transactions in TiDB, you can read this article and think about how to implement pessimistic transactions in TiDB. We will also provide “Best Practices for Pessimistic Locking Transactions” for your reference.

Principle of Optimistic Transactions

With the foundation of Percolator, let’s introduce the TiDB optimistic lock transaction process.

When TiDB processes a transaction, the process is as follows:

  1. The client begins a transaction.

    A. TiDB gets a globally unique incrementing version number from PD as the start version number of the current transaction, which we define as start_TS for the transaction.

  2. The client initiates a read request.

    A. TiDB obtains data routing information from PD, and the data is stored on which TiKV.

    B. TiDB obtains data related to start_TS from TiKV.

  3. The client initiates a write request. Procedure

    A. TiDB verifies the written data, for example, whether the data type is correct and meets the unique index constraint, to ensure that the new written data transaction complies with the consistency constraint, and stores the passed data in memory.

  4. The client initiates a COMMIT.

  5. TiDB starts a two-phase commit to commit the transaction atomically and the data really falls down.

    A. TiDB selects a Key from the current data to be written as the Primary Key of the current transaction.

    B. TiDB obtains all data write routing information from PD and classifies all keys according to all routes.

    C. TiDB sends prewrite requests concurrently to all involved TiKV. After receiving prewrite data, TiKV checks whether the data version information is conflicting or expired, and locks the data if it meets the conditions.

    D. TiDB receives all prewrite messages successfully.

    E. TiDB gets the second globally unique incremental version from PD as the commit_TS for this transaction.

    F. TiDB initiates the second-phase commit operation to TiKV where the Primary Key resides. After receiving the commit operation, TiKV checks the data validity and clears the locks left in the Prewrite phase.

    G. TiDB receives a success message from F.

  6. TiDB returns a successful transaction commit to the client.

  7. TiDB asynchronously cleans up the lock information left over from this transaction.

Advantages and disadvantages analysis

As you can see from the above procedure, TiDB transactions have the following advantages:

  • Simple, easy to understand.
  • Cross-node transactions are implemented based on single instance transactions.
  • Decentralized lock management.

Disadvantages are as follows:

  • Two – stage submission, network interaction.
  • A centralized version management service is required.
  • Before a commit, data is written to memory. If the data is too large, the memory will burst.

Based on the analysis of the above shortcomings, we have some practical suggestions, which will be discussed in detail below.

The transaction size

1. The little affairs

In order to reduce the impact of network interaction on small transactions, we suggest that small transactions be packaged. In Auto COMMIT mode, each of the following statements becomes a transaction:

# original version with auto_commit
UPDATE my_table SET a='new_value' WHERE id = 1; 
UPDATE my_table SET a='newer_value' WHERE id = 2;
UPDATE my_table SET a='newest_value' WHERE id = 3;
Copy the code

Each of the above statements requires a two-phase commit, and the network interaction is directly *3. If we can package it as a transaction commit, there will be a significant performance improvement, as follows:

# improved version
START TRANSACTION;
UPDATE my_table SET a='new_value' WHERE id = 1; 
UPDATE my_table SET a='newer_value' WHERE id = 2;
UPDATE my_table SET a='newest_value' WHERE id = 3;
COMMIT;
Copy the code

Similarly, it is recommended that insert statements be packaged as transactions.

2. Great transaction

Since small things are problematic, aren’t we better off with bigger things?

Let’s go back and analyze the two-phase commit process. As smart as you are, you can easily see the following problems when the transaction becomes too large:

  • Before the client commit write data in memory, TiDB memory inflation, accidentally will OOM.
  • In the first phase, the probability of writes colliding with other transactions increases exponentially, blocking each other.
  • Transaction commit completion can get very, very long

To solve this problem, we put some restrictions on the transaction size:

  • A single transaction contains no more than 5000 SQL statements (default)
  • Each key-value pair does not exceed 6MB
  • The total number of key-value pairs does not exceed 300,000
  • The total size of key-value pairs cannot exceed 100MB

Therefore, for TiDB optimistic transactions, if the transaction is too large or too small, there will be performance problems. We recommend that you write a transaction every 100 to 500 rows to achieve better performance.

Transaction conflict

Transaction conflicts refer to read and write operations on the same Key when transactions are executed concurrently. There are two types of conflicts:

  • Read/write conflict: Concurrent transactions exist. Some transactions read the same Key and some transactions write to the same Key.
  • Write conflict: concurrent transactions that write to the same Key at the same time.

In TiDB’s optimistic locking mechanism, a two-phase commit is triggered only when the client commits the transaction to check for write conflicts. Therefore, in optimistic locking, the existence of write conflicts is easily exposed during the transaction commit and therefore more easily perceived by the user.

Default conflict behavior

Since we focus on best practices for optimistic locking in this article, we will examine the behavior of TiDB under optimistic transactions.

In the default configuration, if the following concurrent transactions conflict, the result is as follows:

In this case, the phenomenon analysis is as follows:

  • As shown above, transaction A is at the point in timet1Start transaction, transaction B is in transactiont1After thet2Start.
  • Transactions A and B update the same row at the same time.
  • Point in timet4When transaction A wants to updateid = 1This row of data, although this row of data is int3This point in time has been updated by transaction B, but because TiDB optimistic transactions only detect conflicts when the transaction is committed, this point in timet4The execution of the.
  • Point in timet5Transaction B commits successfully and data falls.
  • Point in timet6, transaction A attempted to commit, detected when conflictt1Later, new data is written, and the transaction A fails to commit, prompting the client to retry.

By the definition of optimistic locking, this is perfectly logical.

Retry mechanism

Now that we know the default behavior of transactions under optimistic locks, we know that a Commit is likely to fail when conflicts are high. However, most of TiDB’s users come from MySQL; MySQL uses pessimistic locking internally. In this case, transaction A will fail when T4 is updated, and the client will retry as required.

In other words, MySQL conflict detection is performed during SQL execution, so it is very difficult to get an exception at COMMIT time. However, TiDB’s use of optimistic locking mechanism causes the behavior of the two sides to be inconsistent, requiring the client to modify a lot of code. To solve this problem for MySQL users, TiDB provides an internal default retry mechanism. In this case, when A transaction A commit finds A conflict, TiDB internally replays the SQL with writes. To do this, TiDB provides the following parameters,

  • Tidb_disable_txn_auto_retry: This parameter controls whether automatic retry is enabled. The default value is 1.

  • Tidb_retry_limit: controls the number of retries. Note that this parameter takes effect only when the first parameter is enabled.

How to set the above parameters? Two Settings are recommended:

  1. Session level Settings:

    set @@tidb_disable_txn_auto_retry = 0;
set @@tidb_retry_limit = 10;
Copy the code

  2. Global Settings:

    set @@global.tidb_disable_txn_auto_retry = 0;
set @@global.tidb_retry_limit = 10;
Copy the code

Universal retry

So is retry a panacea? This should start from the principle of retry, retry steps:

  1. To obtainstart_ts.
  2. Replay the SQL with writes.
  3. Two-phase commit.

If you are careful, you may notice that we only play back the written SQL and do not mention reading the SQL. This behavior may seem reasonable, but it raises other questions:

  1. start_tsChanges have taken place. In the current transaction, the data read has changed from the time the transaction actually started, and the version written has also been retriedstart_tsNot the one at the beginning of the transaction.
  2. If there are updates in the current transaction that depend on the read data, the result becomes uncontrollable.

With retry enabled, let’s look at the following example:

Let’s analyze the following case in detail:

  • As shown in the figure, transaction 2 starts at SESSION B at T2 and t5 commits successfully. Transaction 1 of Session A starts before transaction 2 and commits after transaction n2 commits.

  • Transactions 1 and 2 update the same row at the same time.

  • When Session A committed transaction 1, A conflict was detected and TIDB internally retried transaction 1.

    • When retried, re-get the new start_TS as t8 ‘.

    • Update tidb set name=’pd’ where id =1 and status=1

      I. The current version t8 ‘does not contain any statement that meets the requirements, so no update is required.

      Ii. If no data is updated, upper-layer success is returned.

  • Tidb considers transaction 1 retry successful and returns success to the client.

  • Session A considers that the transaction is successfully executed and the query result is found to be inconsistent with the expected data in the absence of other updates.

As you can see here, if the update statement in the retry transaction needs to rely on the query result, the original ReadRepeatable isolation type of the transaction cannot be guaranteed because the version number will be retried as starT_TS, and the result may be inconsistent with the forecast.

In summary, it is recommended not to enable the retry mechanism for TiDB optimistic locks if there are transactions that rely on query results to update SQL statements.

Conflict preview

As can be seen from the above, it is a very heavy operation to detect whether there is a write conflict in the underlying data, because the data needs to be read for detection, and this operation is specifically executed in TiKV during prewrite. To optimize this area of performance, the TiDB cluster performs a collision predetection in memory.

As a distributed system, TiDB’s conflict detection in memory is mainly carried out in two modules:

  • At TiDB level, if a write conflict is found in the TiDB instance itself, then after the first write is sent, subsequent writes can clearly know that they are in conflict, and there is no need to send requests to the underlying TiKV to detect the conflict.

  • TiKV layer, mainly in the prewrite phase. As TiDB cluster is a distributed system, TiDB instances themselves are stateless and cannot perceive the existence of each other, so it is impossible to confirm whether their writes conflict with other TiDB instances. Therefore, specific data conflicts are detected at TiKV layer.

Where conflict detection at TiDB layer can be disabled and configuration items can be enabled:

Txn-local-latches: specifies the configuration of transaction memory locks. You are advised to enable this function when there are many local transaction conflicts.

  • enable
    • open
    • Default value: false
  • capacity
    • The number of slots corresponding to the Hash is automatically adjusted up to an exponential of 2. Each slot occupies 32 Bytes of memory. If data is written to a wide range (for example, derivative data), setting the value too small slows down performance.
    • Default value: 1024000

Careful friends may also notice that there is a capacity configuration, which mainly affects the correctness of the conflict judgment. When implementing collision detection, it is impossible to store all the keys in memory, which takes up too much space. Therefore, what is really saved is the hash value of each Key. If there is a hash algorithm, there will be collisions, that is, the probability of misjudgment. Here, we control the modulus of hash through capacity:

  • The smaller the capacity value is, the smaller the memory usage is and the higher the miscalculation probability is.

  • The larger the capacity value is, the larger the memory usage is and the smaller the miscalculation probability is.

In actual use, if a service scenario can prejudge that data writing does not conflict, for example, when importing data, you are advised to disable it.

Accordingly, collision detection in TiKV memory has a similar set of things. The difference is that TiKV checks more strictly and cannot be turned off. It only provides a configuration item to hash modulo values:

  • scheduler-concurrency
    • Scheduler has a built-in memory lock mechanism to prevent simultaneous operations on the same Key. Each Key hash to a different slot.
    • Default value: 2048000

In addition, TiKV provides monitoring to see the specific time spent on latch waiting:

If the wait duration is particularly high, it indicates that the lock request is waiting for a long time. If there is no underlying slow write problem, it can be determined that there are many conflicts during this period.

conclusion

To sum up, the implementation principle of Percolator optimistic transaction is simple, but there are many disadvantages. In order to optimize the performance and function cost caused by these defects, we have made a lot of efforts. But no one can confidently say that the performance of this piece has reached its peak.

Up to now, we are still making continuous efforts to make this part better and further, hoping that more TiDB users can benefit from it. At the same time, we also look forward to your feedback in the process of using TiDB. If you have more optimization suggestions for TiDB transaction, please contact me at [email protected]. Your seemingly casual a move, are likely to make more tortured Internet students enjoy the fun of distributed transactions.

pingcap.com/blog-cn/bes…