The version 12.2 documentation on ORA_ROWSCN states "If a block is queried twice, then it is possible for the value of
ORA_ROWSCN to change between the queries even though rows have not been updated in the time between the queries."
Can anyone help me understand how that can be, and eventually help me reproduce the phenomenon?
I am specifically interested in this scenario:
- I get the current SCN.
- I query a row (so block cleanout, if required, should take place). The SCN should never be greater than the current SCN.
- I update the row, checking the columns to make sure there is a restart if necessary, and checking whether the SCN is greater than the "current SCN" I got in step 1 - and if so I refuse the update because someone else updated the row between steps 2. and 3.
If the value of ORA_ROWSCN can increase between steps 2. and 3. "even though rows have not been updated", I may get a false positive. That is my concern.
I already know that SELECT FOR UPDATE followed by COMMIT will change the SCN. Is that what the documentation is referring to?
If it makes any difference, I am more interested in "fine-grained
ORA_ROWSCN" - when the table has been created with ROWDEPENDENCIES.
Thanks for any help you can provide.
Best regards, Stew Ashton
P.S. Here is the entire paragraph from the documentation:
Whether at the block level or at the row level, the
ORA_ROWSCN should not be considered to be an exact SCN. For example, if a transaction changed row R in a block and committed at SCN 10, it is not always true that the
ORA_ROWSCN for the row would return 10. While a value less than 10 would never be returned, any value greater than or equal to 10 could be returned. That is, the
ORA_ROWSCN of a row is not always guaranteed to be the exact commit SCN of the transaction that last modified that row. However, with fine-grained
ORA_ROWSCN, if two transactions T1 and T2 modified the same row R, one after another, and committed, a query on the
ORA_ROWSCN of row R after the commit of T1 will return a value lower than the value returned after the commit of T2. If a block is queried twice, then it is possible for the value of
ORA_ROWSCN to change between the queries even though rows have not been updated in the time between the queries. The only guarantee is that the value of
ORA_ROWSCN in both queries is greater than the commit SCN of the transaction that last modified that row.