Diffuse words: how to explain to girlfriend what is optimistic lock and pessimistic lock

Recently, may one day holiday holiday time adjustment, decided to take advantage of the holiday to go out to have fun. My girlfriend and I agreed that I would make the itinerary and she would buy the supplies. Peace and quiet, I am in each compare price, do not know what happened, girlfriend buy buy buy actually not happy.

We covered concurrency and parallelism in how to Explain Concurrency and parallelism to Your Girlfriend. When concurrency is possible in the program, we need to ensure that the accuracy of the data in the concurrent situation, by means of the user and other users together, the result is the same as the result of his prayer alone.

This approach is called concurrency control. The purpose of concurrency control is to ensure that the work of one user does not unreasonably affect the work of another user.

Poor concurrency control can lead to dirty reads, phantom reads, and unrepeatable reads.



We often say concurrency control, generally related to database management system (DBMS), the task of concurrency control in DBMS is to ensure that when multiple transactions access the same data in the database at the same time, it does not destroy the isolation and unity of transactions and the unity of the database.

The main means to realize concurrency control can be roughly divided into optimistic concurrency control and pessimistic concurrency control.

Before we start, it’s important to make it clear that both pessimistic and optimistic locks are defined concepts and can be considered an idea. In fact, it is not only the relational database system that has the concept of optimistic locking and pessimistic locking, such as memcache, Hibernate, Tair, etc. Therefore, optimistic locks, pessimistic locks, and other database locks should not be compared.

When we modify a piece of data in a database, it is best to lock the data directly to prevent concurrency in order to avoid being modified by others at the same time.

This approach of using database locks to lock data before changing it is called Pessimistic Concurrency Control (referred to as “Pessimistic lock”).

Pessimistic locking is called pessimistic locking because it is a form of concurrency control with a pessimistic view of data modification. We generally believe that the probability of concurrent data modification is relatively high, so it is necessary to lock the data before modification.

Pessimistic concurrency control is actually a conservative strategy of “lock first, access later”, which guarantees the security of data processing.


However, in terms of efficiency, the mechanism for handling locking can impose additional overhead on the database and increase the chance of deadlocks.In addition, parallelism is reduced; if a transaction locks a row, other transactions must wait for the transaction to complete before processing that row.

Optimistic Locking is relative to pessimistic Locking. Optimistic Locking assumes that data will not cause conflict in general, so when data is submitted for update, whether data conflict will be detected formally. If a conflict is found, the wrong information will be returned to the user so that the user can decide what to do.

In contrast to pessimistic locks, optimistic locks do not use the locking mechanism provided by the database when processing the database. A common way to implement optimistic locking is to record the version of the data.

Optimistic concurrency control believes that the probability of data races between transactions is low, and therefore does not lock until the point of commit, so no locks or deadlocks occur.

The realization of pessimistic locking often relies on the locking mechanism provided by the database. In a database, pessimistic locking flows as follows:

• Before modifying a record, try adding exclusive locking to the record.

• If the lock fails, the record is being modified, and the current query may wait or throw an exception. The specific response mode is determined by the developer based on actual needs.

• If the lock is successfully locked, changes can be made to the record and the transaction will be unlocked after completion.

• In the meantime, if there are other operations to modify the record or add an exclusive lock, they will wait for us to unlock or directly throw an exception.

Let’s take the common MySql Innodb engine as an example to illustrate how pessimistic locking can be used in SQL.

To use pessimistic locks, we must turn off the automatic commit property of mysql database, because mysql uses autoCOMMIT mode by default, which means that when you perform an update operation, mysql will commit the result immediately. set autocommit=0;

Let’s take a simple example, such as the demand of inventory deduction in taobao order process to explain how to use pessimistic lock:

//0.开始事务
begin; 
//1.查询出商品库存信息
select quantity from items where id=1 forupdate; //2. Change the inventory to 2 update itemsset quantity=2 whereid = 1; //3. Commit transaction commit;Copy the code

Update (id = 1); update (id = 1); This is a typical pessimistic locking strategy.

If the above code changes to the inventory concurrently, only one thread can open the transaction and acquire the lock id=1 at a time, and other transactions must wait until the transaction commits. This ensures that the current data will not be modified by other transactions.

As we mentioned above, using select… For UPDATE locks data, but we need to pay attention to some locking levels, MySQL InnoDB default row-level locking. Row-level locks are index-based. If an SQL statement does not use an index, row-level locks are used to lock the entire table.

Optimistic locking eliminates the need for database locking mechanisms.

In fact, the concept of optimistic lock has described its specific implementation details: it is mainly two steps: conflict detection and data update. One typical implementation is Compare and Swap(CAS).

CAS is an optimistic locking technique. When multiple threads attempt to update the same variable using CAS, only one thread can update the value of the variable. However, all other threads fail.

For example, the previous inventory reduction problem can be achieved through optimistic locking as follows:

Quantity = 3 select quantity from itemswhereId =1 // Change the inventory to 2 update itemsset quantity=2 where id=1 and quantity = 3;
Copy the code

Above, before updating, we first query the current quantity in the inventory table, and then use the quantity as a modification condition during the update. When we submit the update, compare the current inventory number of the database table with the inventory number taken out for the first time. If the current inventory number of the database table is equal to the inventory number taken out for the first time, it will be updated; otherwise, it is considered as expired data.

There is a significant problem with the above update statement, which is the legendary ABA problem.

For example, thread one fetches inventory 3 from the database, and thread two also fetches inventory 3 from the database, and two does something to change it to 2, and then two changes it to 3, and then thread One does the CAS operation and finds that the database is still 3, and then thread One succeeds. Although the CAS operation on thread one was successful, it does not mean that the process was problem-free.

A better approach to the ABA problem is to have a separate version field that increases in sequence. Change to the following:

// Query product information, version = 1 select version from itemswhereId =1 // Change the inventory to 2 update itemsset quantity=2,version = 3 where id=1 and version = 2;
Copy the code

Optimistic lock carries a version number every time it modifies data. Once the version number is consistent with the data version number, the modification operation can be performed and the +1 operation can be performed on the version number. Otherwise, the execution fails. Because the version number increases with each operation, there is no ABA problem because the version number only increases, not decreases.

In addition to version, timestamps can also be used, because timestamps naturally increase in sequence.

The above SQL actually has a certain problem, that is, once the high concurrency, only one thread can be modified successfully, then there will be a large number of failures.

For e-commerce sites like Taobao, high concurrency is common, so it is obviously unreasonable for users to perceive failure. So, there are ways to reduce the granularity of optimistic locks.

There is a good suggestion to reduce the strength of optimistic locking, maximize throughput, improve concurrency! As follows:

// Update itemset quantity=quantity - 1 
where id = 1 and quantity - 1 > 0
Copy the code

In the above SQL statement, if the number of users is 1, optimistic lock control is carried out using quantity -1 > 0.

In the above update statement, the value of quantity is queried by itself in an atomic operation and subtracted by 1.

Control of lock granularity in high concurrency environment is an important knowledge. Choosing a good lock can greatly improve throughput and performance while ensuring data security.

In the optimistic lock and pessimistic lock choice above, mainly look at the difference between the two and the applicable scene can be.

1, optimistic lock does not really lock, high efficiency. If the granularity of the lock is not well controlled, the probability of update failure is high, which leads to service failure.

2. Pessimistic locks rely on database locks and are inefficient. The probability of update failure is low.

With the three high architectures of Internet (high concurrency, high performance and high availability), pessimistic locking has been less and less used in production environments, especially in business scenarios with large concurrency.