Optimistic Locking 7: Restartability

When we UPDATE, Oracle may find a discrepancy between the read-consistent and “current” versions of the data. If so, Oracle “restarts” the process. Our optimistic locking solution must make that happen when appropriate.

(It’s been a long time since my last post on this subject: see Optimistic Locking 4: the Good for context.)

What happens when we UPDATE?

(To simplify, I will only talk about the default READ COMMITTED isolation level.)

If we do an UPDATE without a prior SELECT FOR UPDATE,

  • Oracle will start by doing “consistent gets” of the target rows. This produces a result set that is read-consistent.
  • Next, Oracle will do “current gets” to lock and update the target rows.
    • If there is a predicate in the WHERE clause, such as WHERE HIREDATE > TRUNC(SYSDATE, 'YY'), and the column has been changed in the meantime, then Oracle will check whether the WHERE clause is still satisfied,
    • and if not then Oracle will “restart” the update processing.

Oracle will not stop until it has gotten and locked current versions of all the rows that meet the WHERE clause criteria in a read-consistent manner.

Re-evaluate, then (maybe) Restart

To make sure restarts happen when they should, we need to know:

  1. What columns set off the re-evaluation? To set off the re-evaluation, a changed column must be referenced in the WHERE clause or in a BEFORE UPDATE...FOR EACH ROW trigger. The ORA_ROWSCN pseudocolumn does not set off a re-evaluation.
  2. Once the re-evaluation is under way, what additional data is evaluated to decide whether to restart?
    If the re-evaluation takes place, the entire WHERE clause is evaluated.
    If ORA_ROWSCN is included in the WHERE clause, its “current” value is used in any comparison.

If we want to use ORA_ROWSCN for optimistic locking, we need to reference in the WHERE clause any columns we ourselves change. That will set off the re-evaluation, which will then use ORA_ROWSCN to determine whether some other process has changed the row since we last queried it.

See Avoiding Lost Updates with ORA_ROWSCN for a demonstration. Here is an example of an UPDATE that will restart when necessary:

update emp set sal = 800, deptno = 30
where empno = 7369
and ora_rowscn <= 13865770
and coalesce('*',sal || deptno) is not null;

I decided to use COALESCE to refer to the column values because it uses short-circuit evaluation. Once it determines that ‘*’ is not null, it doesn’t bother evaluating the concatenation of the columns. Also, concatenation implicitly converts every datatype to VARCHAR2 so I can list strings, dates, timestamps, numbers and intervals without worry. To prevent the Optimizer from removing that condition someday, I could replace ‘*’ by a bind variable with a non-null value.


ORA_ROWSCN can indeed be used as a “version number” that can be checked to avoid lost updates. The UPDATE statement just has to list the appropriate columns in the WHERE clause, but it doesn’t have to check them against values from a prior SELECT statement. The only real value that needs to be checked is ORA_ROWSCN.

In the next post I’ll address the problem of “false alarms”: ORA_ROWSCN will tell us that a row has previously been updated, but it will not say whether the values have really changed in the columns we care about.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s