Detailed description of pessimistic locks and optimistic locks in the database; Detailed description of the Database

Detailed description of pessimistic locks and optimistic locks in the database; Detailed description of the Database

There are two types of locks in data: Pessimistic locks and optimistic locks, as well as table-level locks and row-level locks.
Table-level locks include:
SELECT * FROM table WITH (HOLDLOCK) other transactions can read the table, but cannot update or delete the table.
SELECT * FROM table WITH (TABLOCKX) other transactions cannot read, update, and delete tables.
Row-level locks include:
Select * from table_name where id = 1 for update;
Pressimistic Locking)
External data (including other current transactions of the system, and
Transaction processing of external systems) changes are conservative. Therefore, data is locked throughout the data processing process. Pessimistic locks often rely on the locks provided by the database (and only the locks provided by the database layer can truly guarantee the exclusive data access; otherwise, even if the locking mechanism is implemented in the system, external systems cannot be guaranteed.
Does not modify data ). For example:
Select * from table_name where id = 'xxx' for update;
In this way, the queried data row is locked. Before the update transaction is committed, other external parties cannot modify this data row. However, this processing method is inefficient and is generally not recommended.
Optimistic Locking)
Compared with pessimistic locks, optimistic locks adopt a more loose locking mechanism. Pessimistic locks are implemented by the database lock mechanism in most cases to ensure maximum operation exclusiveness. However, there is a large amount of database performance overhead, especially for long transactions, which is often unbearable. For example, in a financial system, when an operator reads user data and modifies it based on the read user data (such as changing the user account balance), if a pessimistic lock mechanism is used, this means that the database record is always locked during the entire operation (from the operator reading data, starting modification to submitting modification results, or even the time when the operator goes through coffee cooking, we can imagine what the consequences would be if we were dealing with hundreds of thousands of concurrent jobs.
The Optimistic Locking Mechanism solves this problem to some extent. Optimistic locks are mostly implemented based on the data Version record mechanism. What is the data version? Add a version ID for the data. In the database table-based version solution, you can add a "version" field 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 version number is greater than the current version number of the database table, it is updated, otherwise, expired data is considered.
For example, the optimistic lock (database version is 0 by default ):
It is better that there is only one inventory for one piece of clothing now, but two users place orders at the same time. If we do not control it at this time, it is easy to see inventory overselling. At this time, we can do this:
The first user reads the dress (version = 0) and stores the clothes-1,
The second user also reads the dress (version = 0), and the inventory-1,
The first user completes the operation and adds the database version + 1. when updating the inventory, the data is updated because the submitted data version is later than the database record version, and the database version is updated to 2.
Update goods set store = store-1, version = version + 1 where id = xx and version = orginal_version
The second user also completed the operation and updated the version + 1. when updating the inventory, it was found that the version executed was the same as the version recorded in the database, the optimistic lock policy that the submitted version must be later than the database record version is not met. Therefore, the second user's order request is rejected. We can prompt the user that the product has been sold out through human processing exceptions.
The Optimistic Locking mechanism avoids the database lock overhead in long transactions (no database data is locked during two user operations), greatly improving the overall performance of the system with a large amount of concurrency.
Pessimistic lock: it is handed over to the database for processing. It is controlled by transactions (privacy and explicit transactions, and a single SQL statement is an implicit transaction at ordinary times) + locks, the transaction is equivalent to the scope of the lock. The lock opened in the explicit transaction is released based on the transaction commit failure or rollback. (Pre-event handling)
Optimistic lock: it refers to the version number for control. This mechanism has better concurrency and performance (post-event processing)