Optimistic lock and pessimistic lock

Why do I need a lock (concurrency control)?

In a multiuser environment, multiple users may update the same record at the same time, which can create a conflict. This is the famous concurrency problem.

Typical conflicts are:

L lost Update: One transaction update overwrites the update result of other transactions, that is, the so-called update is lost. For example: User A changes the value from 6 to 2, and User B changes the value from 2 to 6, then user a loses his update.

Dirty read: Dirty reads occur when a transaction reads other records that complete half the transaction. For example, the user A, B sees a value of 6, the value is changed to 2, and the value read by user A is still 6.

In order to solve the problems caused by these concurrency. We need to introduce concurrency control mechanisms.

concurrency control mechanism

pessimistic locks: Assume that concurrency conflicts occur, Block all operations that may violate data integrity. [1]

optimistic Lock: Assume that no concurrency conflicts occur. Check that data integrity is violated only when the commit operation is committed. [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.

SQL Server used 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 used the WITH (UPDLOCK) option when querying the records and we added an update lock to the record, indicating that we are about to update this record. Note Update locks and shared locks do not conflict, that is, other users can also query the contents of this table, but the update lock and exclusive lock is a conflict. So the other update users will block.

Conclusion

In the actual production environment, if the concurrency is not large and dirty reading is not allowed, pessimistic locking can be used to solve the concurrency problem, but if the system is very large concurrency, pessimistic locking can cause a lot of performance problems, so we should choose optimistic locking method.

Reference Documents

[1] Concurrent Control Http://en.wikipedia.org/wiki/Concurrency_control

[2] Oracle 's 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

Optimistic lock and pessimistic lock