The difference between optimistic lock and pessimistic lock in database __ database

Pessimistic lock

As the name implies is the use of a pessimistic attitude to deal with the concurrency problem, we believe that the system of concurrent updates will be very frequent, and the transaction failed after the cost is very large, so we need to adopt the real sense of the lock to implement. The basic idea of pessimistic locking is that every time a transaction reads a record, the record is locked, so
For other transactions to be updated, you must wait for the previous transaction to commit or rollback the unlock.

If our database transaction isolation level is set to read committed or lower, then through pessimistic lock, we control the non-repeatable read problem, but can not avoid the Phantom read problem, because to avoid we need to set the database isolation level of serializable, In general, we will take the read committed or lower isolation level, and with optimistic or pessimistic lock to achieve concurrency control, so phantom read problems can not be avoided, if you want to avoid phantom reading problems, Then you can only rely on the Serializable isolation level of the database (fortunately the Phantom read problem is generally not critical)

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, there is no guarantee that the external system will not modify the data).

optimistic Lock

Optimistic locking is a strategy we often take in the same database transaction, because it enables our system to maintain high performance while improving the concurrency of access control. Optimistic lock, as the name implies is to maintain an optimistic attitude, we believe that the system of transaction concurrency update will not be very frequent, even if the conflict is OK, the big deal again. The basic idea is that every time a transactional update is committed, we want to see if something has been modified since the last time it was read, and if it has been modified, the update will fail. (thus being able to solve the second type of loss modification problem)

Because optimistic locking does not actually lock any records, it is not possible to avoid non-repeatable read problems if the transaction isolation level of our database is set to read committed or lower (because the read transaction does not block other transactions at this time), so when using optimistic locking, The system should allow non-repeatable reading problems to occur.

It should be noted that the optimistic locking mechanism is often based on the data storage logic in the system, so there are some limitations, because the optimistic locking mechanism is implemented in our system, the user update from the external system is not controlled by our system, it may cause dirty data to be updated into the database. In the system design phase, we should take full account of the possibility of these situations, and make appropriate adjustments (such as the optimistic locking policy implemented in the database stored procedures, external only open the data update path based on this stored procedure, rather than the database table directly to the public).

difference

pessimistic Lock: it is assumed that concurrency conflicts occur and that all operations that may violate data integrity are masked. [1]

optimistic Lock: Assume that there will be no concurrency conflicts and only check for violation of data integrity when committing the operation. [1] Optimistic locking does not solve the problem of dirty reading. optimistic lock application

1. Use a self-growing integer to represent the data version number. The update checks whether the version number is consistent, such as the data version in the database is 6, update commits version=6+1, use the version value (=7) and the database Version+1 (=7) to compare, if equal, it can be updated, if not unequal, it is possible that other programs have updated the record. So the error is returned.

2. Use timestamp to implement.

Note: For both of these ways, hibernate comes with the implementation method: add annotation before using optimistic lock fields: @Version, Hibernate automatically validates the field on update. Pessimistic lock Application

You need to use a lock mechanism for the database, such as SQL Server's TABLOCKX (exclusive table Lock) When this option is selected, SQL Server will lock the entire table until the command or transaction ends. This prevents other processes from reading or modifying the data in the table.

Article reprinted from: Optimistic lock of database and pessimistic lock http://www.studyofnet.com/news/252.html