Concepts of optimistic locks and pessimistic locks in Databases

Locking)

During the implementation of business logic, data access needs to be exclusive. For example, end computing on the day of the Financial System

Processing, we want to process the data at a cut-off time point, rather than in the settlement process.

(Several seconds or several hours), and the data changes again. In this case, we need to use some machines

This mechanism ensures that the data is not modified by the outside world during an operation.

To lock the data of the selected target so that it cannot be accessed by otherProgramModify.

Hibernate supports two lock mechanisms: Pessimistic locking )"

And "Optimistic Locking )".

Pessimistic locking)

Pessimistic lock, just like its name, refers to external data (including other current transactions of the system, as well

The transaction processing of the external system.

Status. Pessimistic locks are implemented based on the locks provided by the database (and only the locks provided by the database layer can be implemented.

Data access is exclusive. Otherwise, even if the lock mechanism is implemented in the system, external systems cannot be guaranteed.

Does not modify data ).

A typical pessimistic lock call that relies on databases:

Select * from account where name = "Erica" for update

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

Before the transaction is committed (the lock in the transaction process will be released when the transaction is committed), these records cannot be modified by the outside world.

The pessimistic lock of Hibernate is also implemented based on the database lock mechanism.

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

The database lock mechanism is used to lock the data. Otherwise, the data has passed for update

When the select SQL statement of the clause is loaded, the so-called database locking is impossible.

Optimistic Locking)

Compared with pessimistic locks, optimistic locks adopt a more loose locking mechanism. Pessimistic lock

It is implemented by the database lock mechanism to ensure maximum operation exclusivity. But the following comes the database.

Performance overhead, especially for long transactions, is often unbearable.

For example, in a financial system, when an operator reads user data and reads user data

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

Process (the entire process from the operator reading the data, starting the modification, and submitting the modification results, and even including operations)

The database record is always locked. You can imagine how long it takes

Hundreds of thousands of concurrent jobs.

The Optimistic Locking Mechanism solves this problem to some extent. Optimistic lock, mostly based on data versions

(Version) record mechanism implementation. What is the data version? Add a version ID for the data.

In the database table version solution, a "version" field is added to the database table.

.

When reading the data, read the version number together, and then add one to the version number when updating the data. In this case

The version data of the submitted data is compared with the current version information recorded 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 as expired data.

For the example of modifying user account information above, assume that there is

The version field. The current value is 1, and the current account balance field (balance) is $100.

1 operator A reads it (version = 1) and deducts $50 from its account balance.

($100-$50 ).

2. During operator A's operations, operator B also reads this user information (version = 1) and

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

3. Operator A completes the modification and adds the data version number (version = 2) together with the account

Balance after Division (balance = $50), submit to database update, because the submitted data version is large

The current version is recorded in the database, the data is updated, and the version of the database record is updated to 2.

4 operator B completes the operation, and also adds version 1 (version = 2) to try to submit data to the database

According to (balance = $80), but when comparing the database version, it is found that operator B Submitted

The data version number is 2, and the current database record version is 2, which does not meet the requirement that "the submitted version must be greater than the record

The optimistic lock policy of update is executed only after the current version is recorded. Therefore, the submission of operator B is rejected.

In this way, operator B is prevented from overwriting the operation with the modification result of the old data based on version = 1.

Possible result of Employee A's operation.

The above example shows that the Optimistic Locking mechanism avoids the database lock overhead in long transactions (operator

The database data is not locked during operator B operations), which greatly improves the system

Overall performance.

It should be noted that the optimistic lock mechanism is often based on the data storage logic in the system, so it also has a certain

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

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

In the system design phase, we should fully consider the possibility of such situations and make corresponding adjustments (such

The optimistic lock policy is implemented in the database stored procedure, and only the data update path based on the stored procedure is opened to the outside world.

Path, instead of making the database table public ).