Last updated on DECEMBER 06, 2016
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4This problem can occur on any platform.
Symptoms
The Database Concurrency Control and Optimistic Locking using ORA_ROWSCN pseudo column seems to be not working as desired.Changes
ORA_ROWSCN is a pseudocolumn of any table that is not fixed or external. It represents the SCN of the most recent change to a given row; that is, the latest COMMIT operation for the row. For example:SELECT ora_rowscn, last_name, salary
FROM employees
WHERE employee_id = 7788;
ORA_ROWSCN NAME SALARY
---------- ---- ------
202553 Fudd 3000
The latest COMMIT operation for the row took place at approximately SCN 202553. To convert an SCN to the corresponding TIMESTAMP value, use the function SCN_TO_TIMESTAMP.
ORA_ROWSCN is a conservative upper bound of the latest commit time—the actual commit SCN can be somewhat earlier. ORA_ROWSCN is more precise (closer to the actual commit SCN) for a row-dependent table (created using CREATE TABLE with the ROWDEPENDENCIES clause).
Your application examines a row of data and records the corresponding ORA_ROWSCN as 202553. Later, the application must update the row, but only if the row has not changed. The operation is made conditional on the ORA_ROWSCN being still 202553. An equivalent interactive statement is:
UPDATE employees
SET salary = salary + 100
WHERE employee_id = 7788
AND ora_rowscn = 202553;
0 rows updated.
The conditional update fails in this case, because the ORA_ROWSCN is no longer 202553. Therefore, a user or another application changed the row and performed a COMMIT more recently than the recorded ORA_ROWSCN.
Your application queries again to obtain the new row data and ORA_ROWSCN. Suppose that the ORA_ROWSCN is now 415639. The application tries the conditional update again, using the new ORA_ROWSCN. This time, the update succeeds, and it is committed. An interactive equivalent is:
SQL> UPDATE employees SET salary = salary + 100
WHERE empno = 7788 AND ora_rowscn = 415639;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT ora_rowscn, name, salary FROM employees WHERE empno = 7788;
ORA_ROWSCN NAME SALARY
---------- ---- ------
465461 Fudd 3100
The SCN corresponding to the new COMMIT is 465461.Besides using ORA_ROWSCN in an UPDATE statement WHERE clause, you can use it in a DELETE statement WHERE clause or the AS OF clause of Oracle Flashback Query.
Cause
Sign In with your My Oracle Support account |
|
Don't have a My Oracle Support account? Click to get started |
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms