Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to understand the distributed transaction model of TiDB". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

In the field of traditional relational database, we often solve the problems of dirty reading, phantom reading and unrepeatable reading by configuring the isolation level of transactions. Different transaction isolation levels have different levels of problem solving. The following table shows the tolerance of different transaction isolation levels for dirty reading, phantom reading, and unrepeatable reading. Let's take a look at it:

Note:

The read lock of Repeatable read will not be released until the end of the transaction.

The read lock of Read committed does not wait until the end of the transaction, but is released immediately after the read is completed.

Of course, the traditional database to solve the means of concurrency control is mvcc, which will not be expanded here.

Above I mentioned read lock, write lock and GAP lock. In fact, the types of locks are much more than these. For us developers, we often talk about optimistic locks and pessimistic locks. Optimistic locks are actually unlocked, while pessimistic locks require real locking. In the field of distributed database, concurrency control is also needed, as well as optimistic and pessimistic transactions.

In the case of TiDB, the v3.0 version began to support pessimistic transactions, and since v3.0.8, the newly built TiDB cluster has used pessimistic transactions by default.

Traditional database locking

The optimistic lock of the traditional database is mainly to add a version number field to the table to judge whether it is successful or not according to the update result. For example, we have a table table_a, and we add a version field to it. Here is a record of table table_ a.

Form

Idnameversion1jinjunzhu4

Let's update the record of this id=1, SQL as follows:

Update table_a set name='xiaoming',version = version + 1 where id=1 and version=4

At this point, if the number of updated rows returned by the SQL execution result is 0, it means that the version field has been updated by another transaction, and a write conflict occurs, and the business code must handle this conflict. Under high concurrency, if there are a lot of modifications to the same record, it is bound to cause a large number of write failures. Therefore, optimistic locks are more suitable for scenarios with more reading and less writing.

The pessimistic lock of the traditional database, which is physically locked, or the table above, does not need the version field, if there are two records:

Idname1jinjunzhu2xiaoming

When we join, we need to update both the record of id=1 and the record of id=2. If it is completed within a transaction, the lock sql is as follows:

Select * from table_a where id in (1,2) for update; update table_a set name = 'zhangsan' where id=1; update table_a set name =' lisi' where id=2

The problem with pessimistic locking is that long transactions are encountered, and other transactions take a long time to wait for locks, so oracle provides the following optimization, that is, a failure is returned as soon as it is found that the modified data is locked:

Select * from table_a for update nowait

Percolator model

Percolator model is a distributed transaction solution based on BigTable proposed by Google. Google's paper is as follows, see the link to extend the reading [1]:

"Large-scale Incremental Processing Using Distributed Transactions and Notifications"

Let's take the classic e-commerce system as an example. If there are three tables of order, account and inventory in the system, the user needs to add one order record for each purchase, the account table needs to deduct the amount, and the inventory table needs to deduct inventory. The records to be operated by these three tables are respectively on three slices of the distributed database, so you need to deal with distributed transactions.

Let's take a look at the Percolator algorithm model:

Initial stage

In the initial stage, we assume that the order quantity recorded in the order table is 0, the account amount recorded in the account table is 1000, and the quantity of goods recorded in the inventory table is 100. after the customer places an order, the order table increases one order, and the deduction amount from the account table is 100. the inventory table deducts the quantity of goods by 1. The initial data for each table is as follows:

In the table above, ":" is preceded by a timestamped version of the data, followed by a data value. The first column is the table name, the lower version of the second column holds the data, and the third column holds the locks placed on the data by the transaction operation. The high version of the fourth column holds a pointer to the saved version of the data. For example, the version 6 holds the pointer 6:data@5 to the version 5.

Prewrite stage

In the Prewrite phase, the orchestrating node sends Prewrite commands to each slice. Percolator defines the concept of primary lock, that is, the master lock. In the Prewrite phase, only one row of data to be modified in each distributed transaction can obtain the master lock. In this case, if the order table acquires the master lock, the locks of other tables are pointers to the master lock, called secondary lock, as follows:

In the Prewrite phase, each row to be modified is logged and the private version of the transaction is recorded according to the timestamp, which is 7, so that other transactions cannot manipulate the three pieces of data.

Note that when acquiring the primary lock, the lock will fail if the following occurs:

1. Other transactions have been locked

two。 After the start of this transaction, the data to be updated is updated by other data.

Commit stage

In the commit phase, the coordinator node only needs to communicate with the slice that owns the primary lock, so this case only needs to communicate with the slice where the order table is located. At this point, the data is as follows:

We noticed that the lock on the order table was gone and that version 8 was added to point to version 7. Indicates that the order table has been successfully submitted and there is no private version, but the private version of the account table and inventory table is still there. This is because the Percolator model does not synchronize the commit account table and the inventory table, but instead starts an asynchronous thread to commit the two tables and clean up the locks. If the order form submission fails, both the account table and the inventory table need to be rolled back.

After the submission is successful, the final data is as follows:

In the commit phase, because the coordinator node only needs to communicate with the slice that owns the master lock (here is the slice where the order table is located), which ensures atomicity, this avoids the data inconsistency problem caused by the failure of all nodes in commit.

The log and private version are recorded in the Prewrite phase. If the commit of the slice where the account table and inventory table are located fails, you can commit again according to the log to ensure the final consistency of the data.

Pay attention to two points here:

1. The choice of the main lock is random, for example, in this case, the order table is not necessarily selected.

two。 After the orchestration node sends commit, the order form is submitted successfully. If other transactions need to read 2 pieces of data from account service and inventory service, although there is lock on the 2 pieces of data, search primary@order.bal and find that it has been submitted, so it can be read. However, you need to do some secondary lock cleanup when reading.

TiDB optimistic transaction model

We analyzed the Percolator model above, and TiDB's optimistic transaction uses the Percolator model.

TiDB supports MVCC. When a transaction starts, it uses a timestamp start_ts as the current transaction ID and a snapshot version of MVCC. Subsequent read requests will read the data under the current snapshot version. After successful data verification, the client performs two-phase commit. Let's take a look at the following timing chart:

In the first stage, after receiving the client request, TiDB will first find the first one to send the prewrite request from the cached key to be modified. The key plus primary lock returns a success. TiDB then sends prewrite requests to all other key of the transaction, and these key plus secondary lock returns success.

In the second stage, after a successful prewrite, TiDB first obtains a timestamp from PD as the commit_ts of the current transaction, and then sends a commit request to primary lock key. After primary lock key successfully submits the data, it cleans up the primary lock and returns success. TiDB receives the success message from primary lock key and returns success to the client.

The conflict detection of optimistic transactions is mainly in the prewrite phase. If it is detected that the current key has been locked, there will be a waiting time. If the lock has not been acquired after this time, a failure will be returned. Therefore, when multiple transactions modify the same key, it will inevitably lead to a large number of lock conflicts.

Note: TiDB also has a retry mechanism, which is turned off by default. The retry of TiDB refetches the start_ts, but does not reread the data, so there is no guarantee of a repeatable isolation level. Refer to the TiDB official documentation for details.

TiDB pessimistic transaction model

TiDB has introduced pessimistic transactions since v3.0.

Note: after upgrading to a later version of the cluster created in v3.0.7 and earlier, optimistic transactions are still used by default, and pessimistic transactions are used by default only when newly created clusters are created. We can also use the following command to start pessimistic affairs. The following first statement modifies the TiDB system parameters, and the next two statements ignore the system parameters and have a higher priority:

SET GLOBAL tidb_txn_mode = 'pessimistic'

BEGIN PESSIMISTIC

BEGIN OPTIMISTIC

Pessimistic transactions for compatibility with mysql,TiDB are very similar to mysql. Pessimistic transactions support two isolation levels of repeatable read and read committed, using repeatable readings by default. Optimistic transactions and pessimistic transactions can coexist in TiDB, optimistic transactions will be preferred, and pessimistic transactions will be used only when locks conflict.

The statements that use pessimistic transactions are as follows:

UPDATE 、 DELETE 、 INSERT 、 SELECT FOR UPDATE

There are a few things to note about TiDB's pessimistic affairs:

The SELECT FOR UPDATE statement puts pessimistic locks on the latest data that has been submitted rather than the rows that have been modified

TiDB does not support GAP locks, so it can be inserted when the WHERE condition of the FOR UPDATE statement uses a range condition. For example, the following sql can be inserted successfully if the id does not conflict:

SELECT * FROM T1 WHERE id BETWEEN 1 AND 10 FOR UPDATE

You can set the timeout for waiting for a lock through the innodb_lock_wait_timeout variable. The default is 50s.

FOR UPDATE NOWAIT syntax is not supported

If the Point Get and Batch Point Get operators do not read the data, they will still lock the given primary key or unique key, blocking other transactions from locking or writing to the same primary key

During the execution of a pessimistic transaction, if the DDL operation is performed, it can succeed, but then the transaction will fail to commit

The execution time of pessimistic transactions is limited. The default is 10 minutes, which can be configured through parameters.

Summary

The complexity of business scenarios will inevitably lead to more conflicts in optimistic transactions, which is an important reason why subsequent versions of TiDB turn to pessimistic transactions. Optimistic and pessimistic transactions can coexist in TiDB.

That's all for "how to understand TiDB's distributed transaction model". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.