Database-Pessimistic lock and optimistic lock

Lock (Locking)

In the process of implementing business logic, it is often necessary to guarantee the exclusivity of data access. such as in the final settlement of the financial system

Processing, we want to process the data for a cut-off point in time, and we don't want to be in the process of closing

(may be a few seconds, or maybe a few hours), and the data will change again. At this point, we need to pass some machine

System to ensure that this data is not modified by the outside world during an operation,

"Lock", that is, to lock the target data we have selected so that it cannot be modified by other programs.

Hibernate supports two types of locking mechanisms: commonly referred to as "pessimistic locks (pessimistic Locking)"

and "optimistic Lock (optimistic Locking)".

Pessimistic lock (Pessimistic Locking)

A pessimistic lock, as its name implies, is the data being externally (including other current transactions of the system, as well as from

External System Transaction) modification is conservative, so the data is locked during the entire data processing

State. The implementation of pessimistic locks often relies on the lock mechanism provided by the database (and only the lock mechanism provided by the database layer can

Truly guarantee the exclusivity of data access, otherwise, even in this system to implement the locking mechanism, there is no guarantee that the external system

Data is not modified).

A typical pessimistic lock call that relies on a database:

SELECT * from account where name= ' Erica ' for update

This SQL statement locks all records in the Account table that match the search criteria (name= "Erica").

These records cannot be modified by the outside world before the transaction commits (the locks in the transaction are freed during transaction commits).

Hibernate's pessimistic lock is also a database-based lock mechanism implementation.

Note that it is only possible to set the lock before the query starts (that is, before hiberate generates SQL).

Lock processing is really done through the lock mechanism of the database, otherwise, the data has passed without the for update

Clause of the Select SQL is loaded, so-called database lock also can not be discussed.

Optimistic lock (optimistic Locking)

Relative pessimistic lock, the optimistic locking mechanism adopts a more relaxed locking mechanism. Pessimistic lock in most cases depending on the

The locking mechanism of the database is implemented to ensure the maximum degree of exclusivity of the operation. But it comes with the database.

The high cost of performance, especially for long transactions, is often unsustainable.

such as a financial system, when an operator reads a user's data, and on the basis of the read out user data is motivated

Changes, such as changing the user account balance, if a pessimistic locking mechanism is used, it means that the entire operation

Approached (the entire process of reading data from the operator, starting the modification until the result of the change is submitted, even including the operation

To cook coffee in the middle of the day), the database record is always in the lock state, you can imagine, if the face of a few

Hundreds of thousands of concurrent, such a situation will lead to what consequences.

The optimistic locking mechanism solves this problem to some extent. Optimistic locks, mostly based on the data version

(Version) Recording mechanism implementation. What is a data version? is to add a version ID to the data,

In a version solution for a database table, it is generally by adding a "version" field to the database table to

Realize.

When the data is read, the version number is read together, and then the version number is added one after the update. At this point, the proposed

Data is compared to the current version of the database table corresponding to the record, if the data submitted

The version number is newer than the current version number of the database table, otherwise it is considered to be outdated data.

For an example of modifying user account information above, assume that there is an account information table in the database

The version field, the current value is 1, and the Current Account balance field (balance) is $ $.

1 operator A reads it out at this time (version=1) and deducts the sum from its account balance

($100-$50).

2 during operator a operation, operator B also reads this user information (version=1), and

Deduct $ A ($100-$20) from its account balance.

3 operator a completed the modification work, adding a data version number one (version=2), together with the account buckle

After the balance (BALANCE=$50), submitted to the database update, at this time because the submission data version large

The database records the current version, the data is updated, and the database record version is updated to 2.

4 operator B completes the operation, and the version number plus one (version=2) attempts to submit a number to the database

(balance=$80), but at this time compared to the database record version, operator B commits the

The data version number is 2, the current version of the database record is also 2, and the "commit version must be greater than

The current version to perform an update ", so the submission of operator B is dismissed.

This prevents operator B from overwriting operations with the result of old data modifications based on Version=1

Result of the operation of the operator A.

As can be seen from the above example, the optimistic locking mechanism avoids the database lock overhead in a long transaction (operator A

And operator B during operation, there is no data locking to the database), which greatly improves the system under large concurrency

Overall performance.

It is important to note that the optimistic locking mechanism is often based on the data storage logic in the system, and therefore has a certain bureau

limitations, as in the above example, because the optimistic locking mechanism is implemented in our system, users from external systems

The balance update operation is not controlled by our system and may cause dirty data to be updated in the database. In

During the system design phase, we should take full account of the possibility of these situations and make appropriate adjustments (e.g.

The optimistic locking policy is implemented in the database stored procedure, and only the data update path based on this stored procedure is opened externally.

Rather than exposing the database tables directly to the public).

Database-Pessimistic lock and optimistic lock