Thanks to comments by Tony Hasler and pingbacks from Jeff Kemp, here’s more detail on how optimistic locking works with SCNs, especially with respect to “restarts” during update.
(I assume you have already read Avoiding Lost Updates with ORA_ROWSCN and Optimistic Locking: One SCN to rule them all before reading this post.)
Jeff’s post lists several methods for dealing with lost updates.
- One of them, the “column compare” method, is different from the others. Suppose application A updates the DNAME column in DEPT, and application B updates the LOC column. If A checks only DNAME for a conflicting change, and B checks only LOC, then both applications can modify the same row independently.
- The other methods, based on hashes, timestamps or version numbers, stop two sessions from updating the same row even if the columns involved are different.
So how does the “SCN” method behave? Well, it depends on whether there is a “restart”.
Again, suppose A in one session wants to update DEPT.DNAME and B in another session wants to update DEPT.LOC.
Scenario 1
- A: query DEPT where DEPTNO=10 and get current SCN
- B: query DEPT where DEPTNO=10 and get current SCN
- B: update LOC
- B: commit
- A: update DNAME, referring to the DNAME column and checking the SCN
Result: since the ORA_ROWSCN has changed, the update is refused.
Scenario 2
- A: query DEPT where DEPTNO=10 and get current SCN
- B: query DEPT where DEPTNO=10 and get current SCN
- B: update LOC
- A: update DNAME, referring to the DNAME column and checking the SCN, which appears OK.
The update will be blocked waiting for B to commit. - B: COMMIT
- A: the update is unblocked and Oracle checks to see if the DNAME column has been updated. Since it has not, there is no restart and the ORA_ROWSCN pseudocolumn is not re-evaluated.
Result: the update is allowed!
So, the behavior of the SCN method is inconsistent: when a restart is involved, applications can update different columns in the same row, but when no restart is involved they cannot.
Scenario 3
This scenario is just to emphasize that there is no “restart” if B rolls back.
- A: query DEPT where DEPTNO=10 and get current SCN
- B: query DEPT where DEPTNO=10 and get current SCN
- B: update LOC and DNAME
- A: update DNAME, referring to the DNAME column and checking the SCN, which appears OK.
The update will be blocked waiting for B to commit. - B: ROLLBACK
- A: the update is unblocked and Oracle checks to see if the DNAME column has been changed. Since it has not (because of the rollback), there is no restart.
Result: the update is allowed.
Dealing with “false positives”
If you want to allow independent updates to different columns in the same row, the SCN method will not work – most of the time. It will only work in a restart scenario, since you can decide which columns will trigger a restart. Even then, you know a column has been updated but you don’t know if its value is different: someone may have updated a column to the same value.
If that is truly a problem for you, our famous SCN can come to the rescue. When a row is not updated, do a flashback query (“AS OF SCN <your SCN>”) on that row. You can then do a “column compare” to see whether the changes concern you or not. [UPDATE 2018-07-09: flashback queries are always hard parsed and create a child cursor in the shared pool. Doing flashback queries too often can cause the shared pool to grow too large, or can cause well-behaved cursors to be flushed, which means even more hard parsing later.]
By the way, if you use the SCN method on a table without ROWDEPENDENCIES you risk getting “false positives” even when the row of interest has not been updated. Again, you can double-check with a flashback query and a “column compare”.
Conclusion
The SCN method will warn of a “lost update”:
- If the table does not have ROWDEPENDENCIES: when any row in the block has been updated, deleted or inserted.
- If the table has ROWDEPENDENCIES and no restart is involved: when any column in the same row has been updated.
- If the table has ROWDEPENDENCIES and a restart is involved: when the columns listed in the WHERE clause of the update have been updated.
To make sure the warning is not a “false positive”, use the SCN in a flashback query, compare the columns of interest and then decide whether to redo the update.
With that extra code, the SCN method can be used with confidence, even on tables without ROWDEPENDENCIES.
If your table has ROWDEPENDENCIES and you accept that two sessions cannot update the same row at the same time no matter what, then you don’t need that extra code.
An Oracle Programmer 
Thanks Stew…of course you are right and I was wrong in my previous comment…thanks for the clarifying my woolly thinking.
Hello Mr.Stew and all those who follow after this thread,
I think that the topic itself of “Lost Updates” is a little bit vaguely defined.
In many applications it is a very common scenario that different modules of the application are updating different columns in a table,
especially when tables do have a very large number of columns and each module is responsible with maintaining only a small part of those,
related to different specific pieces of application functionality.
I don’t think that such scenarios need to be considered as “allowing lost updates”.
The “Lost Updates” makes a strong issue for example for Oracle Forms applications, which most usually do update ALL of the columns
of a table which are part of a block.
Therefore, Oracle Forms in fact implements a “half-Pessimistic locking”, by performing a SELECT FOR UPDATE with ALL columns comparison, but doing this only on the first attempt to edit a queried value in a row’s column.
Thus it prevents accidental overwrites of other transaction’s updates, by “unwillingly” using “too old” values for some of the columns,
without the user being responsible or even aware of this happening.
It also has an option of “Delayed locking”, which in fact implements the optimistic locking with a check on ALL the columns.
So, I think that in most cases, when we have two successive updates on the same row and even on the same columns using different values,
we should not consider the first session’s update as a lost update.
A special case is the one when a session’s decision to update a row and possibly the values used in the update are based on the record’s queried values, which might become “outdated” at the moment when the update happens.
Only in such a case there is a need to actively prevent performing a “wrong” (or maybe not needed) “too late” update.
Otherwise, in normal cases, the “last update prevails” logic is also the correct one.
Following after the content of this last post in the series, I don’t quite understand why is a flashback query “as of SCN” needed,
when the SCN is “my SCN”, because such a flashback query will return exactly the same values that I already do have from my initial
query … having performed an initial query was in fact the “starting point” of the whole lost updates discussion.
And, lastly, a technical remark about ORA_ROWSCN: the value of row’s SCN might change even without an effective update of the row,
if a transaction performs a SELECT FOR UPDATE on that row and later COMMIT-s.
This is regardless of whether ROWDEPENDENCIES is used or not.
So, while the SCN usage is a nice and compact method of checking for ANY updates of a row (if we ignore the false positives),
in most business scenarios, where only the updates of specific columns do matter, I think that we still cannot avoid comparisons
for those specific columns only, though, of course, this is less elegant than using a single SCN.
Thanks a lot & Best Regards,
Iudith Mentzel
Hello Iudith,
Thanks for this long and thought-provoking reply. A few clarifications:
You say “A special case is the one when a session’s decision to update a row and possibly the values used in the update are based on the record’s queried values, which might become “outdated” at the moment when the update happens. Only in such a case there is a need to actively prevent performing a “wrong” (or maybe not needed) “too late” update.”
Yes, that’s what I’m talking about.
You say “Following after the content of this last post in the series, I don’t quite understand why is a flashback query “as of SCN” needed, when the SCN is “my SCN”, because such a flashback query will return exactly the same values that I already do have from my initial query”.
I am writing about APIs: one API to provide the initial data and another to apply changes. The code that implements the APIs is in stored procedures in the database. The code that applies the changes does not have the initial values unless the client program sends them back, and that is what I want to avoid. Just sending back the initial SCN is enough.
You say “the value of row’s SCN might change even without an effective update of the row, if a transaction performs a SELECT FOR UPDATE on that row and later COMMIT-s. This is regardless of whether ROWDEPENDENCIES is used or not.”
That is true.
You say “in most business scenarios, where only the updates of specific columns do matter, I think that we still cannot avoid comparisons for those specific columns only.”
I agree, except that I am not sure about “most” – but that doesn’t matter. It is true that all the methods except “column compare” deal with changes to the row as a whole. The advantage of the SCN method is that “column compare” can be done without sending back the initial values, since the SCN allows the database code to query them. Remember, there will rarely be a warning about “lost updates”, so the flashback query will hardly ever be executed.
As I said in the very last sentence of this post, the SCN method is most appropriate when updates are not restricted to specific columns.
Best regards, Stew
Hello Stew,
Thanks a lot for the clarifications :)
Regarding the API usage …
I know that having several “technical” API-s created for each of the possible DML actions on a table is a technique recommended
by some of the specialists.
It is a completely separate discussion whether this is indeed the best method “to API-ze” an application …
In my opinion, API-s should be created for modularizing the various business operations in an application.
In such a case, for example, performing an UPDATE based on the queried values of a record looks like being two parts of the same
business operation, and I personally would not artificially create two API-s for such an operation, one for SELECT and the other for UPDATE,
though, of course, passing a single value (the SCN) instead of all the queried values from one API to the other is a very elegant solution.
Table level API-s have, in my opinion, many disadvantages, especially if they attempt to support 100% non-repetitive code,
which is in general the main reason invoked by the advocates of such an approach.
In such a case, for example, the query API will probably return full records, the update API will probably update all columns,
and so on.
Such an approach is in geenral not the most efficient one from the database’s point of view,
though it might look attractive especially for those whose basic approach is a somewhat “object-oriented” one.
When I think more over the lost updates issue, it would be a nice fantasy to have Oracle implement a kind of “column level lock”,
something along the lines of having a “SELECT … FOR UPDATE a, b, c” on a table only block concurrent attempts
to lock or update one of the “locked columns” (a, b, c) of the same rows, allowing other updates to proceed.
Such an approach would allow implementing a “less pessimistic locking”, which looks somewhat “more natural”
than the column comparisons required by a selective optimistic locking.
But … well … maybe such an approach would require a far too big change in the lock management functionality,
compared to the “rather academic” benefit.
Thanks a lot once again for your very instructive threads, for me it is always a pleasure to follow them :)
Best Regards,
Iudith Mentzel