[Database transactions and locks] in detail seven: in-depth understanding of optimistic lock and pessimistic lock

Note: This article is reproduced from http://www.hollischuang.com/archives/934

As described in the lock mechanism of a database, the task of concurrency control in a database management system (DBMS) is to ensure that the same data in the database is accessed simultaneously without disrupting the isolation and uniformity of the transaction and the consistency of the database.

Optimistic concurrency control (optimistic lock) and pessimistic concurrency control (pessimistic lock) are the main techniques used in concurrency control.

Whether pessimistic lock or optimistic lock, is the concept that people define, can think is a kind of thought. In fact, not only the relational database system has optimistic lock and pessimistic lock concept, such as Memcache, Hibernate, Tair, etc. have similar concepts.

For different business scenarios, different concurrency control methods should be chosen. Therefore, the optimistic concurrency control and pessimistic concurrency control should not be understood narrowly as the concept in DBMS, not to confuse them with the locking mechanism (row, table, exclusive, shared) provided in the data. In fact, in DBMS, pessimistic lock is implemented by using the lock mechanism provided by the database itself.

Below to learn the pessimistic lock and optimistic lock respectively.

Pessimistic lock

In the relational database management system, pessimistic concurrency control (aka "Pessimistic lock", pessimistic Concurrency control, abbreviated "PCC") is a method of concurrency control. It can prevent a transaction from modifying the data in a way that affects other users. If a transaction performs an operation where a lock is applied to a row of data, the other transaction is able to perform an operation that conflicts with the lock only if the transaction releases the lock.
Pessimistic concurrency control is primarily used in environments where data contention is intense, and when concurrency conflicts occur, the cost of using locks to protect data is lower than the cost of rolling back the transaction.

Pessimistic locking, as its name implies, is conservative (pessimistic) about the data being modified by the outside world (including other transactions currently in the system, as well as transactions from external systems), so that the data is locked during the entire data processing process. Pessimistic lock implementation, often rely on the database provided by the lock mechanism (also only the database layer provides a lock mechanism to truly guarantee the exclusivity of data access, otherwise, even in the system to implement the locking mechanism, there is no guarantee that the external system will not modify the data)

In the database, the process of pessimistic locking is as follows:

Try to add an exclusive lock (exclusive locking) to the record before making any changes to it.

If the lock fails, indicating that the record is being modified, the current query may have to wait or throw an exception. The specific response is determined by the developer according to the actual needs.

If the lock is successful, the record can be modified and the transaction will be unlocked when it is completed.

In the meantime, if there are other actions to modify or add exclusive locks to the record, it will wait for us to unlock or throw the exception directly.

Use pessimistic lock in MySQL InnoDB

To use pessimistic locking, we must turn off the auto-commit property of the MySQL database, because MySQL uses the autocommit mode by default, which means that when you perform an update operation, MySQL will immediately submit the results.set autocommit=0;

//0. Start a transactionbegin;/begin  Work;/StartTransaction; (You can choose one of the three)//1. Check out the product informationSelectStatus fromT_goodswhereId=1  for Update;//2generate orders based on product information.Insert  intoT_orders (id,goods_id)Values(NULL,1);//3. Modify the item status to 2UpdateT_goodsSetStatus=2;//4. Commit the transactionCommit;/Commit  Work;

In the above query statement, we used select…for update the way, so that by opening an exclusive lock to achieve the pessimistic lock. At this point in the T_goods table, the data with ID 1 is locked, and the other transaction must wait for the transaction to be committed before it can be executed. This allows us to ensure that the current data is not modified by other transactions.

As we mentioned above, the use select…for update will lock the data, but we need to pay attention to some level of lock, MySQL innodb default row level lock. Row-level locks are index-based, and it is important to note that if an SQL statement does not use a row-level lock for the index, the table-level lock is used to lock the entire table.

Advantages and Disadvantages

Pessimistic concurrency control is actually a conservative strategy of "first fetch lock and then access", which guarantees the security of data processing. However, in terms of efficiency, the mechanism for handling locks can incur additional overhead for the database, as well as an increase in the chance of deadlocks, and in the case of read-only transactions where there is no conflict, there is no need to use locks, which can only increase the load on the system and reduce parallelism, if a transaction locks a row of data. Other transactions must wait for the transaction to finish before they can process the number of rows

Optimistic lock

In the relational database management system, optimistic concurrency control (aka "optimistic Lock", optimistic Concurrency control, abbreviated "OCC") is a method of concurrency control. It assumes that multi-user transactions do not interact with each other during processing, and that each transaction can handle the portion of the data that is affected without the lock being generated. Before data updates are committed, each transaction checks that the data has been read by the transaction and that no other transaction has modified it. If other transactions are updated, the transaction being committed is rolled back. Optimistic transaction control was first presented by Professor Kong Xiangzheng (H.t.kung).

Optimistic lock (optimistic Locking) relative pessimistic lock, optimistic locking hypothesis that the data generally do not cause conflict, so when the data is submitted to update the data will be formally conflicting or not detected, if a conflict is found, then let the return of the user error information, Let the user decide how to do it.

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

A version of the data that is identified as an increase in data. When the data is read, the values of the version identity are read together, the data is updated every time, and the version identity is updated. When we submit an update, the current version of the corresponding record of the database table is judged to be compared with the version ID that was first taken out, if the current version number of the database table is equal to the version identity value of the first fetch, it is updated, otherwise it is considered to be outdated data.

There are two ways to implement a data version, the first is to use a version number, and the second is to use a timestamp.

Using version numbers to implement optimistic locking

When you use a version number, you can specify a version number when the data is initialized, and each update operation on the data performs a +1 operation on the version number. and determine if the current version number is the latest version number for that data.

1. Check out the product informationSelect(status,status,version) fromT_goodswhereId=#{id}2generate orders based on product information.3. Modify the item status to 2UpdateT_goodsSetStatus=2, version=Version+1whereId=#{id} andVersion=#{version};

Advantages and Disadvantages

Optimistic concurrency control believes that the probability of data race between transactions is relatively small, so do it as directly as possible, until it is committed, so that no locks and deadlocks are generated. However, if it is straightforward to do so, it is still possible to encounter unexpected results, such as two transactions have read a row of the database, after modification to write back to the database, then encountered a problem.

Resources

Wikipedia-Optimistic concurrency control

Wikipedia-Pessimistic concurrency control

MySQL pessimistic lock summary and practice

MySQL optimistic lock summary and practice

Optimistic lock and pessimistic lock

[Database transactions and locks] in detail seven: in-depth understanding of optimistic lock and pessimistic lock