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

This article mainly explains "what is optimistic lock, pessimistic lock and MVCC". Interested friends might as well take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what is optimistic lock, pessimistic lock and MVCC"!

In the actual use of the database, we often encounter situations where we do not want the data to be written or read at the same time. For example, in the second kill scenario, two requests read that there is still one inventory in the system at the same time, and then the inventory is updated to 0 one after another. At this time, it will be oversold, and at this time, the actual inventory of the goods will not match our records.

In order to solve the problem of data inconsistency caused by this kind of resource competition, we need a mechanism to ensure the correct access and modification of the data, and in the database, this mechanism is the concurrency control of the database. Among them, optimistic concurrency control, pessimistic concurrency control and multi-version concurrency control are the main technical means of database concurrency control.

Pessimistic concurrency control

I. essence

Wikipedia: pessimistic concurrency control (also known as "pessimistic lock", PessimisticConcurrency Control, abbreviation "PCC") is a method of concurrency control in relational database management systems. It prevents one transaction from modifying data in a way that affects other users. If the operation performed by a transaction reads that a row of data has a lock applied, other transactions can perform operations that conflict with the lock only when the transaction releases the lock.

In fact, the pessimistic lock we often talk about is not a practical lock, but an idea of concurrency control. Pessimistic concurrency control holds a pessimistic attitude towards the modification of data, believing that conflicts will inevitably occur when data is accessed by the outside world. therefore, locking is used in the process of data processing to ensure the monopoly of resources.

In fact, the database locking mechanism is based on the pessimistic concurrency control point of view, and according to the actual use, database locks can be divided into many types, see my later article.

Second, the mode of realization

The locking process of the database pessimistic lock is roughly as follows:

After starting the transaction, add a certain type of lock to the data that needs to be locked according to the type of operation: for example, shared row lock, etc.

If the lock is successful, the later operation will continue. If the data has been added with another lock and conflicts with the lock to be added now, the lock will fail (for example, an exclusive lock has been added). At this point, you need to wait for other locks to be released (deadlocks may occur)

Release the added lock after completing the transaction

Third, advantages and disadvantages

Advantages:

Pessimistic concurrency control adopts a conservative strategy: "take the lock first, and then access the data after success", which ensures that data acquisition and modification are carried out in an orderly manner, so it is suitable to be used in an environment with more writes and less reads. Of course, using pessimistic locks can not maintain very high performance, but under the premise that optimistic locks can not provide better performance, pessimistic locks can ensure the security of data.

Disadvantages:

Due to the need for locking, and may face the problem of lock conflict or even deadlock, pessimistic concurrency control increases the extra overhead of the system, reduces the efficiency of the system, and also reduces the parallelism of the system.

Optimistic concurrency control

I. essence

Wikipedia: optimistic concurrency control (also known as "optimistic lock", OptimisticConcurrency Control, abbreviation "OCC") is a method of concurrency control in relational database management systems. It assumes that multi-user concurrent transactions will not affect each other, and that each transaction can process that part of the data affected by each other without creating a lock.

Optimistic concurrency control is optimistic about data modification, believing that even in a concurrent environment, external operations on data generally do not cause conflicts, so they do not add locks, but before committing data updates, each transaction will first check whether any other transactions have modified the data after the transaction has read the data. If other transactions are updated, let the conflict information be returned and let the user decide how to take the next step, such as retry or rollback.

It can be seen that optimistic locks are not actual locks, and they are not even used to achieve concurrency control, but to judge whether the data can be modified in other ways. Optimistic lock is generally a locking mechanism implemented by users, although it does not use the actual lock, it can produce the effect of locking.

Second, the mode of realization

CAS (compare and Exchange, Compare and swap) is a well-known lock-free algorithm. Lock-free programming, that is, to achieve variable synchronization between multiple threads without using locks, that is, to achieve variable synchronization without thread blocking, so it is also called non-blocking synchronization (Non-blocking Synchronization). The scheme for achieving non-blocking synchronization is called "lock-free programming algorithm" (Non-blocking algorithm).

Optimistic locks are basically based on CAS (Compare and swap) algorithm. Let's first take a look at the CAS process. The process of a CAS operation can be represented by the following c code:

Intcas (long*addr,longold,longnew) {/ * Executes atomically. * / if (* addringing = old) return0; * addr= new; return1;} copy the code

CAS has three operands, the memory value V, the old expected value A, and the new value B to be modified. If and only if the expected value An is the same as the memory value V, change the memory value V to B, otherwise do nothing. The whole CAS operation is an atomic operation and is inseparable.

The implementation of optimistic locks is similar to the above process, mainly in the following ways:

Version number tag: add a field to the table: version, to save the version number. When getting data, get the version number at the same time, and then update the data with the following command: updatexxx set version=version+1, … Where... Version= "old version" and. At this time, it is determined whether the update is successful by judging whether the number of rows affected by the returned result is 0, and the failure of the update indicates that there are other requests that have updated the data.

Timestamp: just like the version number, it is only judged by the timestamp. Generally speaking, many data tables have an update time field, so there is no need to add another field by this field.

Fields to be updated: if you do not have a timestamp field and do not want to add a new field, you can consider using the field to be updated, because the updated data will generally change. Before updating, you can compare the old value of the field to be updated with the present value of the database. If there is no change, update.

All field tags: all fields in the data table are used to determine. This is equivalent to locking not only a few fields, but the entire row of data, so that as long as the data of the row changes, it will not be updated.

Third, advantages and disadvantages

Advantages:

Optimistic concurrency control does not actually add locks, so there is no extra overhead, and it is good to have deadlock problems, which is suitable for concurrent scenarios with more reads and less writes. Because there is no extra overhead, it can greatly improve the performance of the database.

Disadvantages:

Optimistic concurrency control is not suitable for concurrent scenarios with more writes and less reads, because there will be a lot of write conflicts, resulting in multiple data writes waiting for retry. In this case, the overhead is actually higher than pessimistic locks. And the business logic of the optimistic lock is more complex than the pessimistic lock, the business logic should take into account the failure, waiting for retry, and can not avoid other third-party systems to modify the database directly.

Multi-version concurrency control

I. essence

Wikipedia: multi-version concurrency control (Multiversion concurrency control, MCC or MVCC), which is commonly used in database management systems and is also used in programming languages to implement transaction memory.

Both optimistic concurrency control and pessimistic concurrency control ensure the serialization of transactions by delaying or terminating the corresponding transactions to solve the competition conditions between transactions. Although the previous two concurrency control mechanisms can fundamentally solve the problem of serialization of concurrent transactions, they are actually solving the problem of write conflict. the difference between the two lies in the degree of optimism about write conflicts (pessimistic locks can also solve the problem of read-write conflicts, but the performance is average). In the process of practical use, the database read request is many times higher than the write request. If we can solve the problem of read and write concurrency, we can greatly improve the read performance of the database, and this is what multi-version concurrency control can do.

Different from pessimistic concurrency control and optimistic concurrency control, MVCC aims to solve the problem of multiple and long-term read-write operations caused by read-write locks, that is, to solve the problem of read-write conflicts. MVCC can be used in combination with either of the first two mechanisms to improve the read performance of the database.

Pessimistic locks in databases generally implement multi-version concurrency control at the same time, based on the consideration of improving concurrency performance. Not only MySQL, but also other database systems such as Oracle and PostgreSQL also implement MVCC, but their implementation mechanisms are different, because MVCC does not have a unified implementation standard.

Generally speaking, the emergence of MVCC is that the database is dissatisfied with the pessimistic lock to solve the read-write conflict problem, which is proposed because of the low performance.

Second, the mode of realization

MVCC is implemented by saving a snapshot of the data at a certain point in time. The data item read by each transaction is a historical snapshot, which is called a snapshot read. Unlike the current reading, the data read by the snapshot may not be up-to-date, but snapshot isolation enables the data seen during the entire transaction to be in the data state it was when it started. Instead of overwriting existing data items, the write operation creates a new version that is not visible until the transaction commits.

Current read and snapshot read

What are current reads and snapshot reads under MySQL InnoDB?

Current read

Operations like select lock in share mode (shared lock), select for update; update, insert, delete (exclusive lock) are all current reads, why are they called current reads? That is, it reads the latest version of the record, ensures that other concurrent transactions cannot modify the current record, and locks the read record.

Snapshot read

For example, unlocked select operations are snapshot reads, that is, unlocked non-blocking reads; snapshot reads are based on the premise that the isolation level is not the uncommitted read and serialization level, because uncommitted reads always read the latest rows of data, not those that match the current transaction version. Serialization locks all read rows.

Third, advantages and disadvantages

MVCC allows most reads to be unlocked, which is designed to make reading data easy, with good performance, and to ensure that only rows that meet the standard are read. The deficiency is that each row of records requires additional storage space, more line checking work, and some additional maintenance work.

Applicable scenario

1. Pessimistic lock

Locked concurrency control to resolve read-write conflicts and write-write conflicts

It is suitable for situations where there are more writes, less reads and serious write conflicts, because pessimistic locks are locked when reading data, and scenarios with more reads will require frequent locking and a lot of waiting time. The use of pessimistic locks in the case of serious write conflicts can ensure data consistency.

High data consistency requirements

It can solve the problems of dirty reading, phantom reading, unrepeatable reading, lost updates of the first type and missing updates of the second kind.

Optimistic lock

Lock-free concurrency control for solving write-write conflicts

It is suitable for reading more and writing less, because if there are a large number of write operations, the possibility of write conflicts will increase, and the business layer needs to keep retrying, which will greatly reduce system performance.

Data consistency is not required, but very high response speed is required.

Can not solve dirty reading, phantom reading, non-repeatable reading, but can solve the problem of update loss.

III. MVCC

Lock-free concurrency control for solving read-write conflicts

Combine with the above two to improve their reading performance

It can solve dirty reading, phantom reading, unrepeatable reading and other transaction problems, except for update loss.

At this point, I believe you have a deeper understanding of "what is optimistic lock, pessimistic lock and MVCC". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.