The concept of optimistic lock and pessimistic lock in database

Lock (Locking)

In the process of implementing business logic, it is often necessary to ensure the exclusivity of data access. such as in the financial system of the day-end settlement

In processing, we want to deal with data at a cut-off point in time, rather than in the process of closing

(It could be a few seconds, maybe a few hours), and the data will change again. At this point, we need to go through some machines

System to ensure that the data in a certain operation will not be modified by the outside, such a mechanism, here is the so-called

"Lock", which locks our selected target data so that it cannot be modified by another program.

Hibernate supports two locking mechanisms: the usual "pessimistic lock (pessimistic locking)"

and "optimistic lock (optimistic locking)".

Pessimistic lock (pessimistic locking)

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

The transaction processing of the external system is conservative, so the data is locked during the whole process of 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 achieve the locking mechanism, can not guarantee the external department

Data is not modified by the EC).

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 until this transaction is committed (the locks in the transaction are released when the transaction is committed).

Hibernate's pessimistic lock is also based on the database lock mechanism implementation.

Note that locking is only set before the query starts (that is, before hiberate generates SQL)

Locks are actually handled through the lock mechanism of the database, otherwise the data has passed without a for update

clause is loaded in the Select SQL, so the so-called database lock is not discussed.

Optimistic lock (optimistic locking)

In the case of pessimistic locks, the optimistic locking mechanism adopts a more relaxed locking mechanism. Pessimistic lock in most cases depending on

Rely on the database lock mechanism to achieve the maximum degree of exclusive operation. But then there's the database.

The overhead of performance, especially for a long transaction, is often unbearable.

such as a financial system, when an operator reads the user's data and reads the user data on the basis of the progressive

When a row is modified (such as changing the user account balance), if the pessimistic lock mechanism is used, it means that the entire operation

Thread (read the data from the operator, start the modification until the whole process of submitting the modification results, even including the operation

The time that the member goes to make coffee, the database record is always in the lock state, can imagine, if face a few

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

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

(Version) Recording mechanism implementation. What is the data version. Adds a version identifier for the data, which is based on the

In the version solution of a database table, you typically add a "version" field to the database table to

Realize.

When you read out the data, read the version number together, and then when you update it, add one to this version number. At this time, will mention

The version data of the data is compared with the current version information of the corresponding record in the database table, if the submitted data

If the version number is greater than the current version number of the database table, it is updated, otherwise it is considered an expired data.

For the above example of modifying user account information, assume that the account information table in the database has a

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

1 operator A at this time read it out (Version=1) and deduct $ from its account balance

($100-$50).

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

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

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

In addition to the balance (balance=$50), submit to the database update, at this time due to the submission of data version of the large

To the current version of the database record, 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 the number to the database

According to (BALANCE=$80), but at this time compared to the record version of the database found that operator B submitted the

The data version number is 2 and the current version of the database record is 2, not satisfied "the submit version must be greater than the recorded

Recording the current version to perform an optimistic lock policy for update, so operator B's submission was dismissed.

This prevents operator B from overwriting the operation with the results of the old data modification based on Version=1

The operation result of the member A May.

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

And operator B in the process, there is no database data lock, greatly increased the large concurrent volume of the system

Overall performance of the EC.

It should be noted that the optimistic locking mechanism is often based on the data storage logic in the system, so there is a certain bureau

Limits, as in the above example, because the optimistic locking mechanism is implemented in our system, the user from the external system

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

In the system design phase, we should take full account of the possibility of these situations and adjust accordingly (e.g.

Implement optimistic lock policy in database stored procedure, only Open Data update path based on this stored procedure externally

path, rather than exposing the database table directly to the outside.