Pessimistic locking and optimistic locking in the database

The locks in the data are divided into two categories: pessimistic and optimistic, lock, table-level, row-level, and lock.
Table-level locks such as:
SELECT * from table with (HOLDLOCK) Other transactions can read the table but cannot update the delete
SELECT * from table with (Tablockx) Other transactions cannot read table, update and delete
Row-level locks such as:
SELECT * FROM table_name WHERE id = 1 for update;
Pessimistic lock (pressimistic Locking)
To the data by the outside world (including other current transactions of the system, and from
External System Transaction) modifications are conservative, so the data is locked during the entire data processing process. Pessimistic lock implementation, often rely on the database provided by the lock mechanism (also only the database layer provides a lock mechanism to truly guarantee the exclusivity of data access, otherwise, even in this system to implement the locking mechanism, can not guarantee the external system
Data is not modified). For example:
SELECT * FROM table_name WHERE id = ' xxx ' for update;
This row of data that is queried is locked, and other outsiders cannot modify the data until the update transaction is committed, but this processing is less efficient and generally deprecated.
Optimistic lock (optimistic Locking)
Relative pessimistic lock, the optimistic locking mechanism adopts a more relaxed locking mechanism. Pessimistic locking relies on the lock mechanism of the database in most cases, to ensure the maximum degree of exclusivity of the operation. But it comes with a lot of overhead for database performance, especially for long transactions, which are often unsustainable. As a financial system, when an operator reads a user's data and modifies it on the basis of the user's data being read (such as changing the user account balance), the pessimistic locking mechanism means that the entire operation (from the operator reads the data, starts the modification, and commits the modified result. Even when the operator takes the time to cook the coffee, the database record is always locked, and you can see what happens if you face hundreds of thousand concurrent cases.
The optimistic locking mechanism solves this problem to some extent. Optimistic locking, mostly based on the data version (versions) recording mechanism implementation. What is a data version? is to add a version identity to the data, which is typically done by adding a "version" field to the database table in the version solution based on the database table. 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 version data of the submitted data is compared to the current version information of the database table corresponding to the record, and if the submitted version number is greater than the current version number of the database table, it is updated, otherwise it is considered to be outdated data.
Give an example of an optimistic lock (the database version defaults to 0):
Now a piece of clothing left a stock, but there are two users to order at the same time, if you do not control it is easy to show the situation of inventory selling, this time we can do this:
The first user reads this dress out (version=0) and will stock-1,
The second user also reads this dress out (version=0) and will stock-1,
The first user completes the operation, version+1 the database version, and when the update inventory is performed, the data is updated because the submitted version of the data is larger than the database record, and version in the database 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, also version version+1, when performing the update inventory, found that the execution version and the database record version of the same, does not conform to the committed version must be larger than the database record version of the optimistic locking policy, so the second user's order request was rejected, We can show the user that the product has been sold out and so on by humanized handling exception.
The optimistic locking mechanism avoids the database lock-up overhead in a long transaction (two user operations, no database data locking), greatly improves the overall performance of the system under large concurrency.
Pessimistic lock: To the database to handle, by the transaction (sub-privacy and explicit transactions, usually a single SQL statement is an implicit transaction) + lock that control, where the transaction is equivalent to the scope of the lock, according to the transaction commit failure or rollback to release the open lock in the explicit transaction. (Pre-treatment)
Optimistic Lock: Is the version number to control, this mechanism of concurrency and performance is better (after processing)

Pessimistic locking and optimistic locking in the database