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
WHEREclause, such as
WHERE HIREDATE > TRUNC(SYSDATE, 'YY'), and the column has been changed in the meantime, then Oracle will check whether the
WHEREclause is still satisfied,
- and if not then Oracle will “restart” the update processing.
- If there is a predicate in the
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:
- What columns set off the re-evaluation? To set off the re-evaluation, a changed column must be referenced in the
WHEREclause or in a
BEFORE UPDATE...FOR EACH ROWtrigger. The
ORA_ROWSCNpseudocolumn does not set off a re-evaluation.
- 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
WHEREclause is evaluated.
ORA_ROWSCNis included in the
WHEREclause, 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
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.