Talking about database concurrency control-lock and MVCC

After learning a few years of programming, you will find that all the problems are not simple, fast solution, many problems need to weigh and compromise, and this article describes the database in concurrency performance and serializable between the tradeoff and compromise-concurrency control mechanism.

If all the transactions in the database are executed serially, it is very easy to be a performance bottleneck for the entire application, although nodes that cannot scale horizontally will eventually become bottlenecks, but a serial transaction database accelerates the process, and concurrency (Concurrency) makes it possible to make everything happen. It solves a certain performance problem, but it can lead to more bizarre bugs.

After the introduction of concurrent transactions, if you do not control the execution of the transaction, there will be a variety of problems, you may not enjoy the performance of concurrency caused by a variety of strange problems have been tortured by the desire to die.

Overview

How to control concurrency is one of the most important issues in the database domain, but as of today, there are many proven solutions to the control of transactional concurrency, and the principles of these scenarios are what this article wants to introduce, and the three most common concurrency control mechanisms are described in this article:

Respectively pessimistic concurrency control, optimistic concurrency control and multi-version concurrency control, in which pessimistic concurrency control is the most common concurrency control mechanism, that is, lock, and optimistic concurrency control in fact there is another name: Optimistic lock, optimistic lock is actually not a real lock, we will be in the later part of the article specifically introduced And finally, multi-version concurrency control (MVCC), unlike the previous two, MVCC can be used in conjunction with any of the previous mechanisms to improve the read performance of the database.

Since this article describes the different concurrency control mechanisms, it will certainly involve the concurrency of different transactions, and we will analyze how the various mechanisms work in a way.

Pessimistic concurrency control

Controlling the acquisition of the same data by different transactions is the most fundamental way to ensure the consistency of the database, and if we can make the transaction exclusive to the same resource at the same time, then it is guaranteed that the different transactions that operate the same resource will not affect each other.

The simplest and most widely used method is to use the lock to solve, when the transaction needs to operate on the resources to obtain the corresponding lock, to ensure that other transactions do not access the resource, the resources of the various operations, in pessimistic concurrency control, the database program for the data is modified pessimistic attitude, In the process of data processing will be locked, so as to solve the problem of competition.

Read/write Lock

To maximize the concurrency of database transactions, the locks in the database are designed to be two modes, namely shared and mutex. When a transaction obtains a shared lock, it can read only, so a shared lock is also called a read lock, and when a transaction obtains a mutex for a row of data, it can read and write to the row data, so the mutex is also called a write lock.

Shared and mutex locks have a "shared" and "mutually exclusive" relationship in addition to the read and write operations that a transaction can perform, which means that multiple transactions can acquire a shared lock on a row of data at the same time, but the mutex is incompatible with the shared lock and other mutexes. We can naturally understand the reason for this design: there are a variety of bizarre issues that inevitably occur when multiple transactions are written to the same data at the same time.

If the current transaction has no way to fetch the lock for that row's data, it will fall into the waiting state until the other transaction locks the current data corresponding to the lock and perform the appropriate operation.

Two-Phase Lock protocol

The two-Phase lock Protocol (2PL) is a protocol that guarantees transactional serialization, dividing the acquisition and release locks of transactions into two distinct phases of growth (growing) and reduction (Shrinking).

In the growth phase, a transaction can acquire a lock but cannot release the lock, and in the reduction phase the transaction can only release the lock, not the new lock, if you look at the definition of 2PL, then this is done here, but it has two variants:

1. Strict 2PL: The mutex held by the transaction must be released after the commit;
2. Rigorous 2PL: All locks held by the transaction must be released after submission;

While the use of locks can solve the problems caused by concurrent execution between different transactions, the use of two-phase locks introduces another serious problem, deadlock, and a deadlock is caused by different transactions waiting for a resource that has been locked by the other, and here we give a simple example:

Two transactions at the beginning of the Draven and beacon resources to obtain the lock above, and then request the other party has acquired the lock will occur deadlock, both sides have no way to wait until the release of the lock, if there is no deadlock processing mechanism will wait indefinitely, two transactions can not be completed.

Handling of deadlocks

Deadlocks are often encountered in multithreaded programming, and once multiple threads are involved in contention for a resource, it is necessary to consider whether the current number of threads or transactions will cause a deadlock; In general, there are two ways to resolve deadlocks, one is to prevent the generation and appearance of deadlocks from the source, and the other is to allow the system to enter a deadlock state. However, it can be found and recovered in time when the system deadlock occurs.

Prevent deadlocks

There are two ways to help prevent deadlocks, one is to ensure that the wait between transactions does not occur, that is, the waiting graph between transactions should be a direction-free graph, no loop waiting, or to ensure that all the resources that a transaction wants to obtain are locked in an atomic manner at the beginning of the transaction. All resources are either locked or locked.

However, there are two problems with this approach, it is difficult to determine at the beginning of the transaction which resources need to be locked, and because some late-used data is locked early, the data utilization and transaction concurrency rate is also very low. One solution is to lock all data rows in a certain order, and combine with the 2PL protocol to ensure that all data rows are locked from small to large in a lock-up phase, but this approach still requires that the transaction know the data set that will be locked in advance.

Another way to prevent deadlocks is to use preemption Plus transaction rollback to prevent deadlocks, and when a transaction begins execution, a timestamp is obtained, and the database program determines whether the transaction should wait or rollback based on the timestamp of the transaction, at which point there are two mechanisms for us to choose from, one of which is the Wait-die mechanism:

When the timestamp of the execution of the transaction is less than the other transaction, that is, transaction A begins at B, then it waits for the other transaction to release the lock for the corresponding resource, otherwise it will keep the current timestamp and roll back.

Another mechanism called wound-wait, which is a preemptive solution, is the opposite of the result of the Wait-die mechanism, when the current transaction executes and requests the resources of another transaction before the other transaction, and the other transaction is immediately rolled back, and the resource is ceded to the first executed transaction. Otherwise, you will wait for other transactions to release the resource:

Both methods cause unnecessary transaction rollback, resulting in some performance loss, a simpler way to resolve deadlocks is to use time-outs, but the time-out is set to be carefully considered, otherwise it will cause long-time transactions can not be executed properly, or can not be found in time to resolve the deadlock, So the use of it is still a certain limitation.

Deadlock detection and recovery

If the database program is unable to ensure that the deadlock does not occur through the protocol, it is necessary to detect the deadlock and return from the deadlock state to a normal state to ensure that the database program works correctly. When a deadlock is resolved using detection and recovery, the database program needs to maintain reference information between the data and the transaction, as well as to provide an algorithm to determine whether the current database is in a deadlock state, and finally to provide a timely recovery of the appropriate policy when a deadlock occurs.

In the previous section we actually mentioned that the detection of deadlocks can be judged by a forward-looking wait graph, and if one transaction relies on the data being processed by another transaction, the current transaction waits for the end of another transaction, which is an edge in the entire wait graph:

As shown, if there is a ring in this graph, it indicates that the current database has entered a deadlock state, which TransB -> TransE -> TransF -> TransD -> TransB requires a deadlock recovery mechanism to access it.

How to recover from a deadlock is actually very simple, the most common solution is to select a transaction in the entire loop to roll back to break the entire waiting graph in the loop, there are three things to consider throughout the recovery process:

Each time a deadlock occurs, there will be more than one transaction being affected, and choosing which of these tasks to roll back is something that must be done, and the golden principle when choosing a victim (victim) is to minimize the cost, so we need to consider factors such as the time that the transaction has been calculated, the data rows used, and the transactions involved. When we choose the victim, we can start rolling back, there are two options for rollback, one is all rolled back, the other is partial rollback, a partial rollback is rolled back to a checkpoint before the transaction, and if there is no checkpoint then there is no way to perform a partial rollback.

In the process of deadlock recovery, it is also possible that certain tasks may be chosen as victims in multiple deadlocks, never successfully executed, resulting in starvation (starvation), we need to ensure that the transaction will be executed in a poor time, so the time stamp is taken into consideration when selecting the victim.

The size of the lock

So far we haven't discussed the different granularity of locks, we've all been talking about data row locks, but at some point we want to treat multiple nodes as a unit of data, locking the data cells, tables, and even databases directly with locks. The implementation of this goal requires us to define different granularity locks in the database:

When we have a different granularity lock, if a transaction wants to lock the whole database or the whole table simply lock the corresponding node will be in the current node plus display (Explicit) lock, on all child nodes with implicit (implicit) lock Although this different granularity lock can solve the problem that the child nodes cannot be locked when the parent node is locking, we have no way to determine immediately that the parent node cannot be locked when the child node is locking.

At this point we need to introduce an intent lock to solve this problem, when it is necessary to lock the child nodes, the first to all the parent node with the corresponding intent lock, the intent lock is not mutually exclusive, just to help the parent node quickly determine whether the node can be locked:

Here is a compatibility relationship between the locks that introduced the two intent locks, the intent shared lock , and the intent mutex , where we sped up the throughput of the database through different granularity of locks and intent locks.

Optimistic concurrency control

In addition to the pessimistic concurrency control mechanism-lock, we actually have other concurrency control mechanisms, optimistic concurrency control (optimistic Concurrency controls). Optimistic concurrency control is also called optimistic lock, but it is not a real lock, many people will mistakenly think that optimistic lock is a real lock, but it is only a kind of concurrency control idea.

In this section, we will first introduce the timestamp -based concurrency control mechanism and then extend it on the basis of this protocol to achieve optimistic concurrency control mechanisms.

Timestamp-based protocol

The lock protocol executes according to the time a different transaction requests the same data item, because the data that is executed by the subsequent transaction is locked by the previous transaction and can only wait for the lock to be released, so the order in which the locks are executed is related to the order in which the locks are acquired. The timestamp-based protocol you want to introduce here determines the order in which transactions are executed before the transaction executes.

Each transaction will have a globally unique timestamp, which can either use the system's clock time or use a counter, as long as it is guaranteed that all timestamps are unique and increment over time.

Timestamp-based protocols ensure that the order in which transactions are executed in parallel is exactly the same as when the transaction is executed serially, with each data item having two timestamps, a read timestamp and a write timestamp, respectively, representing the timestamp of the transaction that is currently successfully executing the corresponding operation.

This protocol ensures that all conflicting read and write operations can be executed serially at the size of the timestamp, and that no other transactions need to be taken into consideration when performing the corresponding operation only the value of the time stamp corresponding to the data item is required:

Both the read and write operations compare read-write timestamp values from left to right, and if less than the current value is directly rejected and then rolled back, the database system adds a new timestamp to the transaction that is rolled back and executes the transaction again.

Authentication-based protocols

optimistic concurrency control is essentially a validation-based protocol, because in most applications read-only transactions account for the vast majority of transactions, which can be very small because of conflicting writes, meaning that most transactions can run very well without the need for concurrency control mechanisms, Also can guarantee the consistency of the database, and the concurrency control mechanism actually adds a lot of overhead to the whole database system, we can actually reduce this part of the cost through other policies.

The authentication protocol is the solution we find, which divides the execution of all transactions into two to three phases, based on a read-only or update of the transaction:

During the read phase, the database performs all read and write operations in the transaction, and stores all the written values in the temporary variable, and does not actually update the contents of the database; At this point, the database program checks whether the current changes are legitimate, that is, whether there are other transactions in RAED PHASE Data is updated during the update, and if you pass the test directly into write PHASE all the changes that exist in the temporary variable are written to the database, and the transaction without the test is terminated directly.

To ensure that optimistic concurrency control works, we need to know the time of the different phases of a transaction, including the start time of the transaction, the start time of the validation phase, and the end time of the write phase; With these three timestamps, we can guarantee that any conflicting transaction will not be written to the database at the same time. Once the validation phase is completed by one transaction, it is immediately written, and other transactions that read the same data are rolled back for re-execution.

As an optimistic concurrency control mechanism, it assumes that all transactions eventually pass through the validation phase and execute successfully, while the lock mechanism and timestamp-based protocols are pessimistic because they force the transaction to wait or roll back in the event of a conflict, even if a lock is not required to ensure that there is no possibility of a conflict between transactions.

Multi-version concurrency control

The concurrency control mechanism we have introduced so far has been to solve the competing conditions between transactions by delaying or terminating the corresponding transaction (Race condition) to ensure the serializable of the transaction, although the preceding two concurrency control mechanisms are indeed able to fundamentally solve the problem of serializable of concurrent transactions, However, in the real world the transaction of the database is mostly read-only, the read request is many times the write request, if there is no concurrency control mechanism before the write request and the read request, then the worst case is the read request read the already written data, which is completely acceptable for many applications.

In this context, the database system introduces another concurrency control mechanism- multiple versions of concurrency control (multiversion Concurrency controls), which creates a new version of the data for each write operation. Read operations will pick one of the most appropriate results from a limited number of versions of the data to return directly; At this point, conflicts between read and write operations no longer need to be noticed, and managing and quickly selecting versions of the data is a major problem that MVCC needs to address.

MVCC is not an opposite of optimistic and pessimistic concurrency control, it can be a good combination with the two to increase the concurrency of transactions, in the current most popular SQL database MySQL and PostgreSQL in the implementation of the MVCC, but because they achieve pessimistic lock and optimistic lock, so MVCC are implemented in different ways.

MySQL and MVCC

The multi-version two-phase lock protocol implemented in MySQL (Multiversion 2PL) combines the benefits of MVCC and 2PL, with each version of the data row having a unique timestamp, and when there is a read transaction request, The database program returns with the maximum timestamp directly from multiple versions of the data item.

The update operation is slightly more complicated, and the transaction reads the latest version of the data to calculate the results of the updated data, and then creates a new version of the data, the timestamp of the new data is the largest version of the current data row ＋1 :

The deletion of the data version is also selected based on the timestamp, and MySQL clears the lowest-version data from the database to ensure that there are no large amounts of legacy content.

PostgreSQL and MVCC

Unlike the pessimistic concurrency control used in MySQL, PostgreSQL uses optimistic concurrency control, which leads to a number of differences in the implementation of the optimistic lock combination, which is called the multi-version timestamp sequencing protocol (multiversion Timestamp MVCC). Ordering), in this protocol, all transactions are assigned a unique timestamp before execution, and each data item has read and write two timestamps:

When PostgreSQL's transaction makes a read request, the database directly returns the latest version of the data and is not blocked by any action, while the write operation, at execution time, must be either large or equal to the read timestamp of the data row, or it will be rolled back.

The implementation of this MVCC guarantees that the read transaction will never fail and does not need to wait for the lock to be released, and the optimistic lock plus MVCC greatly improves the performance of the database for applications where the read request is much more than the write request. Although this protocol can make some noticeable performance improvements However, it can also cause two problems, one is that each read operation will update the read timestamp resulting in two disk writes, and the second is that the conflict between transactions is resolved by rollback, so if the likelihood of a conflict is very high or the rollback cost is huge, the database read and write performance is not as good as using the traditional lock wait mode.

Summarize

The concurrency control mechanism of the database today has a very mature and perfect solution, we do not need to design a new set of protocols to deal with the conflict between different transactions, from the database concurrency control mechanism learned from the relevant knowledge, Whether the lock or optimistic concurrency control is widely used in other fields or applications, it is necessary to understand and familiarize yourself with the principles of different concurrency control mechanisms.

Reference

• Talking about database concurrency control-lock and MVCC Faith-Oriented programming
• Pessimistic vs. optimistic concurrency control
• PostgreSQL Concurrency with MVCC
• Well-known Databases use Different approaches for MVCC
• Serializability
• Race condition

SOURCE Link: A brief talk on database concurrency control-locks and MVCC Faith-Oriented programming

Follow:draveness GitHub

Talking about database concurrency control-lock and MVCC