Optimistic lock and pessimistic lock ____oracle for Oracle

first, the problem leads to

1. Suppose that when an online user buys a book, there is an order in the database with order number 001, of which the Status field is ' valid ' to indicate that the order is valid;

2. The backstage management inquires to this 001 order, and sees the status is valid;

3. The user found the next order when the wrong, so the cancellation of orders, assuming running such a sql:update order_table set status = ' Cancel ' where order_id = 001;

4. Background management As a result of the ② this step to see the state is valid, at this time, although the user in ③ this step has revoked the order, but the manager did not refresh the interface, see the order status is still valid, and then click the "Delivery" button, the order sent to the Logistics department, while running similar to the following SQL, Change Order status to shipped: Update order_table Set status = ' shipped ' where order_id = 001;

<<< How should the problems above be solved??? Please see below decomposition >>>

second, pessimistic lock

The so-called pessimistic lock: As the name suggests, is very pessimistic, every time to get the data are thought that others will be modified, so every time to get the data will be locked. This way people will wait until the lock is released when they take the data.

Oracle pessimistic locks need to take advantage of an existing connection, which is divided into two ways, from the point of view of the SQL statement, one is select for update, one is the form of select for update nowait.

1. When performing the Select XXX for update operation, the data is locked and only the execution Comit or rollover is released

2. When performing the Select XXX for update nowait operation, the data will also be locked, other people access or return ORA-00054 error, the content is busy resources, need to take appropriate business measures to deal with.

While pessimistic locks are simple and very safe to use, there are two major problems at the same time:

1. Lock: The user of the application chooses a record to update and then goes to lunch, but does not end or discards the transaction. All other users who need to update the record must wait for the user who is doing the practice to come back and complete the transaction or until the DBA kills the unpleasant transaction and releases the lock.

2. Deadlock: Users A and B update the database at the same time. User A locks a record and attempts to request User B to hold the lock, while User B is also waiting to acquire the lock held by user A. Two transactions enter the deadlock state at the same time as the infinite wait state.

Third, optimistic lock

The so-called optimistic lock: is very optimistic, every time to pick up the data when they think others will not be modified, so will not be locked, but in the update will be judged in the meantime if someone else to update this data. Oracle uses optimistic locks by default

In optimistic locks, we have 3 common practices to achieve:

1. The first is to copy the entire data to the application when the data is obtained, and to make the submission more than the data in the current database and the data obtained before the update begins. When two data is found to be identical, it means no conflict can be committed, otherwise it is concurrency conflict, need to use business logic to solve.

2. The second approach to optimistic locking is to use a version stamp, which is used in hibernate. With a version stamp, you first need to create a new column on the database table where you have an optimistic lock, such as number, which increases by 1 when you update the data every time. For example, there are 2 sessions that also operate on a piece of data. Both are taken to the current data version number 1, when the first session of the data update, at the time of submission to see the current data version is also 1, and the same version of their first access. is formally submitted and then the version number is increased by 1, at which point the current data version is 2. When the second session also updated the data submission, found in the database version of 2, and the beginning of this session to take the version number inconsistent, know that others have updated this data, this time to do business processing, such as the entire transaction rollback and so on operation. When you use the version stamp, you can use the version stamp validation on the application side or trigger (triggers) on the database side to authenticate. However, the trigger performance of the database is relatively large, so can be verified on the application side or recommended not to trigger.

3. A third approach, similar to the second approach, is to add a column to a table, but this time the column uses the timestamp type to store the last update of the data. New data types can be used after oracle9i, that is, timestamp with the time zone type to do the timestamp. This timestamp data precision is the highest in Oracle's time type, accurate to microsecond (not yet to nanosecond level), in general, plus the database processing time and people's thinking action time, microsecond level is very very enough, in fact, as long as accurate to milliseconds or even seconds should have no problem. Similar to the version stamp just now, it is also a comparison of the timestamp of the data in the current database at the time of the update submission and the timestamp that was taken before the update, if it is consistent, OK, or version conflict. You can also write the timestamp optimistic lock logic in trigger or stored procedures if you don't want to write your code in a program or because you can't write the code in an existing program for any other reason.

Iv. Conclusion

1. If the system concurrency is not large and dirty reads are not allowed, pessimistic locks can be used to resolve concurrency problems.

2. If the system concurrency is very large, pessimistic lock will bring great performance problems, so the general use of optimistic locks.

3. If the system reads more, write less, you should also use optimistic locks, you can improve throughput.

V. reference materials

[1] Oracle's optimistic lock and pessimistic lock http://www.blogjava.net/cheneyfree/archive/2008/01/25/177773.html

[2] The difference between optimistic lock and pessimistic lock http://www.cnblogs.com/Bob-FD/p/3352216.html

[3] Optimistic lock http://blog.csdn.net/ivory_lei/article/details/7940117 in Oracle concurrency control