ORA_ROWSCN Concurrency Control and Optimistic locking in 10g.

(Doc ID 741848.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4
This 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