Deep understanding of optimistic lock and pessimistic lock in database

In the database lock mechanism, the task of concurrency control in a database management system (DBMS) is to ensure that multiple transactions simultaneously access the same data in the database 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 technical means used in concurrency control.

Whether pessimistic lock or optimistic lock, is defined by the concept of people, can be regarded as a thought. In fact, it is not only the concept of optimistic lock and pessimistic lock in relational database system, such as Memcache, Hibernate, Tair etc. have similar concepts.

For different business scenarios, different concurrency control methods should be chosen. Therefore, do not put optimistic concurrency control and pessimistic concurrency control in the narrow sense of the concept in the DBMS, not to mention them and the data provided by the lock mechanism (row locks, table locks, exclusive locks, shared locks) confused. In fact, in the DBMS, pessimistic lock is the use of the database itself to provide the lock mechanism to achieve.

Below to learn about the pessimistic lock and optimistic lock respectively. Pessimistic lock

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

A pessimistic lock, as its name implies, is conservative (pessimistic) about the data being modified by the outside world (including other transactions of the system, as well as transactions from external systems), so that the data is locked during the entire process of data processing. The realization of the pessimistic lock, often rely on the database to provide the lock mechanism (and only the database layer to provide the lock mechanism to truly guarantee the exclusive access to data, otherwise, even in this system to achieve the lock mechanism, also can not guarantee that the external system will not modify the data) in the database, pessimistic lock process is as follows:

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

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 based on the actual needs.

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

In the meantime, if there are other operations that modify or add exclusive locks to the record, they will wait for us to unlock or throw the exception directly. pessimistic lock used in MySQL InnoDB

To use pessimistic locks, we must turn off the automatic submission property of the MySQL database because MySQL defaults to the autocommit mode, which means that MySQL will immediately submit the results when you perform an update operation. Set autocommit=0;

1 2 3 4 5 6 7 8 9 10

0. Commencement of business Begin;/begin Work;/start transaction; (If you choose one, you can)//1. Query out the product information select status from T_goods where id= 1 for update; 2. Generate order INSERT into t_orders (id,goods_id) VALUES (NULL, 1) based on commodity information; 3. Modify the status of the commodity to 2 update t_goods set status= 2; 4. Submission of services Commit;/commit work;

In the query above, we used the Select...for Update method, so that the pessimistic lock was implemented by opening the exclusive lock. At this point in the T_goods table, the data ID 1 is locked, and other transactions must wait for this transaction to be committed before they can be executed. This allows us to ensure that the current data is not modified by other transactions.

As we mentioned above, using 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 indexed, and if a SQL statement does not use an index, row-level locks are used, and table-level locks are used to lock the entire table, which requires attention. Advantages and Disadvantages

Pessimistic concurrency control is actually a conservative strategy of "lock and Access first", which provides a guarantee for the security of data processing. But in terms of efficiency, the process of locking the database creates additional overhead, there is also an increase in the chance of deadlocks, and there is no need to use locks in read-only transactions because there is no conflict, and it is not necessary to do so, which can only increase the system load, but also reduce parallelism, and if a transaction locks a row of data, Other transactions must wait for the transaction to be processed before it can handle the row number optimistic lock

In the relational database management system, optimistic concurrency control (also known as "optimistic lock", optimistic concurrency control, abbreviated "OCC") is a concurrency controlling method. It assumes that concurrent transactions of multiple users do not interact with each other during processing, and that each transaction can process the part of the data that affects them without creating a lock. Before submitting the data update, each transaction checks to see if there are other transactions and changes to the data after the transaction has been read. If other transactions are updated, the transaction being committed is rolled back. Optimistic transaction control was first proposed by Kongxiang Heavy (H.t.kung) professor.

Optimistic lock (optimistic locking) relative pessimistic lock, optimistic lock hypothesis that the data will not cause conflicts in general, so when the data is submitted to update, it will formally detect the conflict or not, if found conflict, so that 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 locks is to record data versions.

The data version, which is an additional version identifier for the data. When reading the data, the value of the version ID is read together, the data is updated every time, and the version identity is updated. When we submit the update, we judge that the current version information of the corresponding record in the database table is compared with the version ID that was first taken out, if the current version number of the database table is equal to the version ID value that was first taken out, it is updated, otherwise it is considered to be out-of-date 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. use version number to implement optimistic lock

When you use the 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 of the data.

1 2