Optimistic lock and pessimistic lock

Why do I need a lock (Concurrency Control )?

In a multi-user environment, multiple users may update the same records at the same time, which may cause conflicts. This is a well-known concurrency problem.

Typical conflicts include:

L loss update: the update of a transaction overwrites the UPDATE results of other transactions, which is called update loss. For example, if user a changes the value from 6 to 2, and user B changes the value from 2 to 6, user a loses his update.

L dirty read: dirty reads occur when a transaction reads records of half of the other transactions. For example, if user a and B See 6 values, user B changes the value to 2, and user a still reads 6 values.

To solve these concurrency problems. We need to introduce the concurrency control mechanism.

Concurrency Control Mechanism

Pessimistic lock: it is assumed that concurrency conflicts will occur,Block all operations that may violate data integrity. [1]

Optimistic lock: If no concurrency conflict occurs,Check whether data integrity is violated only when submitting the operation. [1] Optimistic locks cannot solve the dirty read problem.

Optimistic lock Application

1. Use an auto-increment integer to indicate the data version number. Check whether the version number is consistent during update. For example, if the data version in the database is 6 and the version is 6 + 1 during update submission, use the version value (= 7) compared with database version + 1 (= 7), if it is equal, it can be updated. If it is not equal, other programs may have updated the record, so an error is returned.

2. Use timestamps.

NOTE: For the above two methods, Hibernate comes with the implementation method: Add annotation: @ version before the optimistic lock field, and hibernate automatically verifies this field when updating.

Pessimistic lock Application

You need to use the database lock mechanism. For example, if the SQL Server tablockx (exclusive table lock) option is selected, SQL Server will set the exclusive lock on the entire table until the command or transaction ends. This prevents other processes from reading or modifying table data.

SqlserverUsed in

Begin tran
Select top 1 @ trainno = t_no
From train_ticket with (updlock) Where s_flag = 0

Update train_ticket
Set t_name = user,
T_time = getdate (),
S_flag = 1
Where [email protected]
Commit

We use the with (updlock) option during query. When querying records, we add an update lock to the records, indicating that we are about to update the records. note that the update lock does not conflict with the shared lock, that is, other users can query the content of this table, but it conflicts with the update lock and the exclusive lock. therefore, other update users will be blocked.

Conclusion

In the actual production environment, if the concurrency is small and dirty reads are not allowed, you can use the pessimistic lock to solve the concurrency problem. However, if the system concurrency is very large, pessimistic locking can cause very high performance problems, so we need to choose an Optimistic Locking method.

References

[1] concurrent control http://en.wikipedia.org/wiki/Concurrency_control

[2] Oracle pessimistic lock and optimistic lock http://space.itpub.net/12158104/viewspace-374745

[3] timestamp application-optimistic lock and pessimistic lock [turn] http://hi.baidu.com/piaokes/blog/item/9b0c6854e4909050564e00b3.html

 

 

Tags: concurrency control, concurrent control, optimistic lock, pessimistic lock