MySQL clarifies optimistic locking and pessimistic locking


Pessimistic locking and optimistic locking are essentially not specific locks in the database, but a concept defined by people, which can be understood as a kind of thought and a common means (method) to deal with concurrency problems. Row locks, table locks, exclusive locks, and shared locks in the database can be classified according to this lock concept.

Implementation of optimistic locking

Generally speaking, there are two types: version number and timestamp.

Use the data version (Version) recording mechanism, which is the most commonly used implementation of optimistic locking.

This is achieved by adding a numeric "version" field to the database table. When reading data, read the value of the version field together, and add one to the version value every time the data is updated.

When submitting an update, it is judged that the current version information of the corresponding record in the database table is compared with the version value fetched for the first time. If the current version number of the database table is equal to the version value fetched for the first time, the update operation is performed. Otherwise, it is regarded as expired data.

2. The second implementation of optimistic locking is similar to the first. It also adds a field to the table that requires optimistic locking control. The name does not matter. The field type uses timestamp, which is similar to the version above. When the update is submitted, check the timestamp of the data in the current database and compare it with the timestamp taken before the update, if they are consistent, it is OK, otherwise it is a version conflict.

Problem thrown

When multiple people modify the same piece of data at the same time, what is the final result of the data?

This is the concurrency problem we often say, which will cause your modified data to be overwritten by others, and then dirty read. The data you read is not expected, but some inexplicable data, that is, data modified by others.

How to solve this problem? Locks can be divided into optimistic locks and pessimistic locks.

Optimistic lock

Brief description: I am very optimistic and always think that there will be no concurrency problems. Every time data is read, it is always thought that no other thread will modify the data, so it will not be locked, but when updating, it will be judged whether other threads have modified the data before. This judgment is generally implemented using the version number mechanism or CAS operation.

Common implementation: version number version

Specific implementation: Add the version number field in the data table. Before updating a piece of data, find out the version number of the piece of data. Each time the data is updated, the version number will be updated (plus 1).

First find out the version number of the data. When updating, compare it with the current version number of the data in the database. If it is the same, it means that the data has not been modified, and the update is executed successfully. If the results of the comparison are inconsistent, it means that the piece of data has been modified by others, and the update fails (that is, it is not updated), and the client can perform corresponding operation reminders.

Specific examples:

//1.查询出商品信息,包括版本号   select status,version from t_goods where id=#{id}    //2.根据商品信息生成订单  …   //3.修改商品status为2 (比对版本号在这里)   update t_goods    set status=2,version=version+1    where id=#{id} and version=#{version};

Pessimistic lock

Brief description: Very pessimistic, always assuming the worst case, every time you fetch data, you think that other threads will modify it, so it will lock (read lock, write lock, row lock, etc.), when other threads want to access the data (Read or modify), all need to be blocked and suspended. Pessimistic locks can be implemented by the database, such as row locks (shared locks, exclusive locks), read locks, and write locks. These are all locked before operation. In Java, the synchronized idea is also the realization of pessimistic locks.

Pessimistic locks used in MySQL InnoDB

To use the pessimistic lock, we must turn off the autocommit attribute of the mysql database, because MySQL uses the autocommit mode by default, that is, when you execute a SQL operation, MySQL will immediately submit the result.set autocommit=0;


A typical implementation mechanism of optimistic locking is CAS.

Optimistic locking mainly consists of two steps: conflict detection and data update. When multiple threads try to use CAS to update the same variable at the same time, only one thread can update the value of the variable, and other threads will fail. The failed thread will not hang, but it will be notified that the competition has failed. Try again.

The CAS operation includes three operands: the memory location to be read and written (V), the expected original value (A), and the new value (B).

Principle: If the memory location is equal to the expected original value, then the value of the memory location is updated to the new value B. If the memory location is not equal to the expected original value, it will not be updated. In either case, it will return the value of that position before the CAS instruction. (In some special cases of CAS, it will only return whether the CAS is successful, without extracting the current value)

int compare_and_swap (int* reg, int oldval, int newval){  int old_reg_val = *reg;  if (old_reg_val == oldval) {     *reg = newval;  }  return old_reg_val;}

Optimistic locking is a kind of thought, and CAS is just a way to realize this kind of thought.

ABA problem
For example, a thread T1 takes A from memory location V, and another thread T2 also takes A from memory at this time, and T2 performs some operations to become B, and then T2 changes the data at V location back to A. At this time, thread T1 performs a CAS operation and finds that A is still in the memory, and then the T1 operation is successful. (Assuming it does not change back to A, the thread T1 operation fails)

Ps: This article mainly discusses the pessimistic locking of the database. In Java, the same principle is used. According to whether to lock the resource, it is divided into optimistic lock and pessimistic lock. The former will not be locked, and the latter will be locked.