Oracle optimistic Lock and Pessimistic lock overview

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: it assumes that a transaction may make changes to the data between the read data and the updated data. To prevent data inconsistencies caused by this change, the read statement locks the data to prevent any other process from making changes to 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: when the data read into the time does not lock those records, and assume that in the read operation, the updated data has not been modified, at the end of the submission of data conflict detection. Oracle uses optimistic locks by default, for example: Update ... set ...

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.

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/