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

What is the two-phase locking protocol of MySQL? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Two-phase locking (2PL) protocol for MySql (innodb only), rather than two-phase commit (2PC) protocol, the differences are as follows:

2PL, two-phase locking protocol: mainly used for consistency and isolation in stand-alone transactions.

2PC, two-phase commit protocol: mainly used for distributed transactions.

MySql itself aims at performance, and there is also a MVCC (multi-version control) control. This paper does not consider this technology, but only the locking protocol of MySql itself. # # when the record will be locked during the record update operation or (select for update, lock in share model), the record will be locked (including shared lock, exclusive lock, intention lock, gap lock, nextkey lock, etc.). For simple consideration, this document does not consider the type of lock. # # what is locked in a transaction in two stages, which is divided into lock phase and unlock phase, that is, all lock operations precede unlock operations, as shown below:

# # Why do you need two-phase locking

The introduction of 2PL is to ensure the isolation of transactions, that is, multiple transactions are equivalent to serial execution in the case of concurrency. The following blocking theorem is proved mathematically:

If the transaction is well-structured and two-phase, then any legitimate scheduling is isolated.

The specific mathematical deduction process can be seen in Section 7.5.8.2 of this book.

This book is a Bible about database affairs and does not need to be explained. (although the Chinese translation is obscure, it can be read on and is highly recommended.)

# # two-phase locking in engineering practice-S2PL in practice, the SQL is ever-changing and the number of entries is variable, so it is difficult for the database to determine what is the locking phase and what is the unlocking phase in the transaction. So S2PL (Strict-2PL) is introduced, that is:

In a transaction, only commit (commit) or rollback (rollback) is the unlock phase.

The rest of the time is the locking phase.

As shown in the following figure:

In this way, it is easy to implement in the actual database. # # impact of two-stage locking on performance

The two-phase locking is well explained above, and now let's analyze its impact on performance. Consider the following two different inventory deductions:

Option 1:

Begin; / / deduct inventory update t_inventory set count=count-5 where id=$ {id} and count > = 5; / / Lock user account table select * from t_user_account where user_id=123 for update; / / insert order record insert into tweeter transfer; commit

Option 2:

Begin; / / Lock user account table select * from t_user_account where user_id=123 for update; / / insert order record insert into tweak transfer; / / deduct inventory update t_inventory set count=count-5 where id=$ {id} and count > = 5; commit

Because they are within the same transaction, these operations on the database should be equivalent. However, there is a big gap in the performance under the two-stage locking. The timing of the two solutions is shown in the following figure:

As inventory is often the most important hot spot, it is the bottleneck of the whole system. So if the second option is adopted,

Tps should theoretically be able to increase 3rt/rt=3 times. This is also the case where there are only three SQL for the business.

One more sql, one more rt, twice the time.

It is worth noting that:

The lock is considered successful only at the point in time it is updated to the database.

The lock is not unlocked until it is submitted to the database.

The first half of these two round_trip will not be counted.

As shown in the following figure:

At present, only the network delay is considered, regardless of the time consumption of the database and the application itself. # # optimizing based on the performance of S2PL

As you can see from the above example, it is necessary to record the hottest spots

Put it into the transaction *, which can significantly improve the throughput. Go a step further:

The hotter the record is, the closer it is to the end of the transaction (whether commit or rollback)

In the author's opinion, the sequence is as follows:

# avoid deadlocks, which is inevitable for any SQL lock. It was mentioned above that transactions are arranged in reverse order according to the heat of recording Key. Then any possible concurrent SQL must be handled in this order when writing code, otherwise it will cause a deadlock. As shown in the following figure:

# select for update and update where predicate calculation We can directly write some simple judgment logic into the update predicate to reduce locking time. Consider the following two options:

Option 1:

Begin: int count= select count from t_inventory for update; if count > = 5: update t_inventory set count=count-5 where id = 123commit else rollback

Option 2:

Begin: int rows = update t_inventory set count=count-5 where id = 123and count > = 5 if rows > 0: commit; ele rollback

The delay is shown in the following figure:

As you can see, by adding predicate calculations in update, 1rt time is reduced.

Because update adds exclusive locks to records that meet predicate conditions during execution, which are the same as select for update.

After reading the above, have you mastered the method of MySQL's two-phase locking protocol? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.