Hello all,
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."
See ORA_ROWSCN Pseudocolumn
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.