Solutions to Oracle loss update Problems

Loss of updates is a common and classic problem in data. During project creation, we may not notice this problem. However, this problem is very important and sometimes leads to serious results. Next we will discuss this missing update.

1. What is missing update:

Use an operation procedure to describe:

(1) A transaction in session Session1 obtains (queries) a row of data and displays it to user1.
(2) another transaction in session Session2 also obtains this row, but displays the data to another user user2.
(3) User1 uses the application to modify this line, so that the application can update the database and submit it. The transaction of session Session1 is completed.
(4) User2 also modifies this line to allow the application to update the database and submit it. The transaction of session Session2 is completed.

This process is called"Update loss", Because all the operations performed in step (3) will be lost (overwritten by step 3), the database will only save the UPDATE results in step (4. This situation may not be affected in some systems, but it may be affected in some systems. For example:

If the company decides to add 1 k RMB to employee Zhang San for the current salary adjustment, and two Finance Department operators A and B, the process is like this:

1) operator A queries Michael Jacob's salary information on the application system page, then selects the salary record for modification, opens the modification page, but suddenly A has something to leave, there is no submission for the page.

2) at this time, operator B also queries Michael Jacob's salary information in the application, then selects the salary record for modification, enters the increase of salary by 1000, and then submits the information.

3) at this time, operator A came back and entered A 1000 increase salary on the salary modification page that he opened before, and then submitted it.

In fact, in the above example, operators A and B only need to submit the documents one after the other, and the tragedy will come out. SQL statement for modifying salary in the background: update the payroll table set salary = salary + Add salary where staff_id = 'employee id '. After this process was completed, the result was: Michael Zhang was happy, this time rose by 2 k, and operator A and operator B were depressed.

Ii. solution:

There are two basic ideas:Pessimistic lockAnd the other isOptimistic lockSimply put, it is assumed that such a problem is of a high probability. It is best to lock it at the beginning to avoid the update from always failing. The other assumption is that such a problem is of a low probability, the last step is to lock it when updating, so that the lock time is too long to affect other people's operations.

Iii. solution 1 (pessimistic lock)

A. Traditional pessimistic lock method (not recommended ):

The preceding example illustrates how to use select... for update nowait, you can add a for update nowait statement to lock this record to avoid updates from other users. This ensures that subsequent updates are updated in the correct state. Then, when the link is maintained, the system submits the update. Of course, the premise is to maintain the link, that is, to take a long time for the link, which is obviously unrealistic in the current high concurrency of the web system.

B. The current pessimistic lock method (recommended ):

On the modify salary page, perform a query first when submitting the statement. Of course, this query must also be locked (select... for update nowait), some people will say that it is okay to make a query here to confirm whether the record has changed. Yes, it is necessary to make a confirmation, however, if you do not add for update, you cannot guarantee that this record has not been updated by other sessions during the period from query to update submission. Therefore, you need to lock the record during query, make sure that the record is updated without any change. If there is any change, the system will notify you. 

Iv. solution 2 (optimistic lock)

A. Old Value Condition (pre-image) method:

The old status value is used as the condition during SQL Update. The SQL statement is roughly as follows: Update table set col1 = newcol1value, col2 = newcol2value .... Where col1 = oldcol1value and col2 = oldcol2value ...., In the above example, we can update the current salary as a condition. If this record has been updated by other sessions, this time 0 rows are updated, here, our application system will usually prompt you to re-query for updates. Which old values are used as the condition for updating depends on the actual situation of the system. (This method may be congested. If the application uses the pessimistic lock method to lock this record for a long time elsewhere, this session will have to wait, therefore, we recommend that you use the Optimistic Locking method in a unified manner .)

B. Use the version column method (recommended ):

In fact, this method is a special image method, that is, you do not need to use multiple old values as conditions, you only need to add a version column to the table, this column can be NUMBER orThe DATE/TIMESTAMP column is used to record the version of the data. (During table design, we usually add redundant fields of the NUMBER and DATE types to each table, for extension, these redundant fields can be used as version columns.) in the application, we can maintain the version columns each time. During the update process, the last version is updated as a condition.

C. Use the checksum method (not recommended)

D. Use the ORA_ROWSCN method (not recommended) 

V. Conclusion:

To sum up, we recommend that youThe above pessimistic lock B method or optimistic lock B method (Marked in blue), In fact, the two methods are essentially the same, both of which are made a query at the time of update submission to confirm when the update is submitted,I personally think it is optimistic. The difference is that the method of pessimistic lock B is through select .. for update mode, which may cause blocking of other sessions. Optimistic Locking Method B requires maintenance of one more version column.

Personal suggestion: select the pessimistic lock B method in the application system with a small number of concurrent users and severe conflicts. In other cases, the optimistic lock version column method is used first.