8 Replies Latest reply on Jul 9, 2017 10:44 AM by Jonathan Lewis

    How can ORA_ROWSCN change between queries when no update?

    Stew Ashton

      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:

      1. I get the current SCN.
      2. I query a row (so block cleanout, if required, should take place). The SCN should never be greater than the current SCN.
      3. 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.

        • 1. Re: How can ORA_ROWSCN change between queries when no update?
          Jonathan Lewis

          Stew,

           

          The important point here is that the documentation doesn't say that the ORA_ROWSCN may go up between queries, it says it may change.

          Thanks to the complexities (and optimisation) of upper bound commits for commit cleanout it is possible for the ORA_ROWSCN for a row to go down between queries.

           

          Session 1 updates a row and commites at SCN 1000 - but the block had been dumped from memory and was not updated with a commit SCN

          Session 2 starts a long-running query

          Session 3 runs 5,000 transactions updating and commiting on another table and overwrites the transaction table slot for the session 1 update

           

          Session 0  (your unlucky session) and queries the row from session 1 - delayed block cleanout takes place but only so far as is necessary for your session to know that the row change has committed - the ORA-ROWSCN will be about 6,000 because your session will see that the transaction table slot has been re-used therefore session 1 MUST have committed prior to whatever SCN was left in that transaction table slot by session 3.  The ITL entry in the table block is updated to reflect an upper bound commit around 6,000.

           

          Session 2 reaches the critical block and needs to know whether the row change from session 1 was committed before or after it started the query, so runs the transaction table slot (not the ITL entry) as far back as it can go (which might result in an ora-01555) until it takes the slot back to SCN = 1000, therefore finding that the session 1 transaction was committed - and it updates the ITL with the correct (or, at least, closer to correct) SCN.

           

          You requery the block, you get an ORA_ROWSCN that is lower than it was on the previous query.

           

          Regards

          Jonathan Lewis

           

           

           

          UPDATE 9th July 2017:

          To avoid confusion, I should point out that I wrote the above for the case of rowdependencies being enabled. If you have a roughh idea of how rowdependencies work you would probably have assumed that from the comments about the ITL entry being updated (and posisbly from Stew's original comment that that was the example he was particularly interested in.)

           

          Unfortunately there is an important error in the above - while the "dependent scn (dscn:)" for a row is set by copying the commit SCN from the relevant ITL entry that happens only once, when the lock flag is cleared from the ITL entry and the lock bytes are cleared from the rows locked by that ITL entry.

           

          In my example, the ITL is cleaned (using the upper bound commit algorithm) and the dscn is set when session 0 first queries for the ora_rowscn - that's two changes to the block that will be written back to disc eventually.  When session 2 runs its query it updates the ITL again (as I described, and it really does update the block, and the updated block will be written back to disc eventually), taking it back to an earlier SCN - but the code does not update the dscn values at the same time.  Session 2 will then report the older SCN as the ora_rowscn for the row by applying the basic read-consistency mechanisms to this modified current block.  (If you've read Oracle Core you may recall the section where I explained that a "consistent read" image of a block doesn't necessarily take a copy of a block back to a state it was at a prior point in time, it could take it to a state that had never actually been in - this is a particular case of that mechanism.)

           

          The upshot of this is that if a session requeries a block in the way I've described it WON'T see the the ora_rowscn decrease; but you will note that different sessions can (at least for a while) see different ora_rowscn values for the same row. You might also consider the interesting little detail that a set of rows modified by a single transaction may end up reporting different ora_rowscn values if the rows are scattered across multple blocks.

           

          The error in my description doesn't affect Stew's requirement, of course, since he was interested only in discovering whether or not the ora_rowscn for a row could INCREASE between two consecutive queries of the same row by the same session in the absence of any intervening updates to the block.

           

          Footnote:

          If you want split hairs with semantic games - there is a way in which the ora_rowscn for a row could increase between two queries from the same session without an intervening update by another session. You get the other session to do an update before the first query and commit between the two queries. But then Stew's algorithm is supposed to see that change and abort its update.

          • 2. Re: How can ORA_ROWSCN change between queries when no update?
            Mark D Powell

            Stew, Jonathan has explained some of the interesting issues you may encounter when attempting to work with ora_rowscn, but why are you looking at the ora_rowscn to try to determine if a row has changed?  It is one thing if you question is purely academic and strings only from the statement in the documentation but if you have a specific use in mind there may be other options you might use instead, but you would need to state your problem and proposed solution for people to comment.

            - -

            HTH -- Mark D Powell --

            • 3. Re: How can ORA_ROWSCN change between queries when no update?
              Stew Ashton

              Jonathan, thank you so much.

               

              I admit when asking this question I hoped you would chime in, but I did not realise the answer was in my copy of Oracle Core, chapter 3! I might not have understood the importance of that chapter without your explanation.

               

              Your explanation and demonstration deal with a "normal" row without ROWDEPENDENCIES, however the same principle surely applies with ROWDEPENDENCIES: if Oracle needs to find a "good enough" SCN, it stores its work in the block for future use. If the SCN is not "good enough", it may be replaced by an older SCN, but never by a newer one.

               

              Thanks again and best regards, Stew

              • 4. Re: How can ORA_ROWSCN change between queries when no update?
                Stew Ashton

                Mark D Powell wrote:

                 

                Stew, Jonathan has explained some of the interesting issues you may encounter when attempting to work with ora_rowscn, but why are you looking at the ora_rowscn to try to determine if a row has changed? It is one thing if you question is purely academic and strings only from the statement in the documentation but if you have a specific use in mind there may be other options you might use instead, but you would need to state your problem and proposed solution for people to comment.

                - -

                HTH -- Mark D Powell --

                Thanks, Mark, for your interest and desire to help.

                 

                I mentioned briefly my use case in the original post:

                 

                I am specifically interested in this scenario:

                1. I get the current SCN.
                2. I query a row (so block cleanout, if required, should take place). The SCN should never be greater than the current SCN.
                3. 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.

                 

                In a word, I want to use the SCN and ORA_ROWSCN for optimistic locking at the row level, in order to avoid lost updates.

                 

                I have blogged recently about this:

                https://stewashton.wordpress.com/2017/06/17/avoiding-lost-updates-with-ora_rowscn/

                https://stewashton.wordpress.com/2017/06/21/optimistic-locking-one-scn-to-rule-them-all/

                 

                If you think I should provide more detail in this thread (and not just a link), I will do it. I would just mention two points:

                1. UPDATE processing may restart if the "current get" shows that columns mentioned in the WHERE clause have been updated more recently than the SCN used for the "consistent get". ORA_ROWSCN in the WHERE clause will not cause such a restart, but it suffices to mention the other columns for the restart to occur.
                2. I do not test ORA_ROWSCN for equality. I just need to know whether it is greater than the SCN I used to query the data in the first place.

                 

                Let me know if you want further details.

                 

                Best regards, Stew

                • 5. Re: How can ORA_ROWSCN change between queries when no update?
                  padders

                  > but it suffices to mention the other columns for the restart to occur.

                   

                  Sorry to be picky but (regarding your first blog post) what guarantees that the optimizer will not remove the short circuit predicates you are using to trigger restarts?

                  • 6. Re: How can ORA_ROWSCN change between queries when no update?
                    Stew Ashton

                    padders wrote:

                     

                    > but it suffices to mention the other columns for the restart to occur.

                     

                    Sorry to be picky but (regarding your first blog post) what guarantees that the optimizer will not remove the short circuit predicates you are using to trigger restarts?

                    I asked myself that question. I would be very surprised if Oracle "optimized out" something that changes the behavior of the statement; however, that is not an absolute guarantee.

                     

                    In a production scenario, I would probably try to think of something that I know will always be the case, but the optimizer can't know: for example, replace the space by a bind variable.

                     

                    Best regards, Stew

                    • 7. Re: How can ORA_ROWSCN change between queries when no update?
                      Stew Ashton

                      Jonathan, I finally found a way to demonstrate ORA_ROWSCN getting lower:

                       

                      Session 1:

                      create table T1 (a number, ts timestamp, val number) NOrowdependencies;
                      
                      create table T2 (a number, ts timestamp, val number);
                      
                      begin
                      insert into T1 values (1, localtimestamp, 0);
                      insert into T1 values (2, localtimestamp, 0);
                      commit;
                      end;
                      /
                      
                      begin
                      insert into T2 values (3, localtimestamp, 0);
                      commit;
                      end;
                      /
                      
                      update T1 set ts = localtimestamp, val = 1 where a = 2;
                      
                      alter system flush buffer_cache;commit;
                      
                      begin
                      for i in 1 .. 5000 loop
                      update T2 set val = val + 1;
                      commit;
                      end loop;
                      end;
                      /
                      

                       

                      Session 2:

                      set transaction read only;
                      

                       

                      Session 1:

                      begin
                      for i in 1 .. 5000 loop
                      update T2 set val = val + 1;
                      commit;
                      end loop;
                      end;
                      /
                      select t.*, ora_rowscn "after" from T1 t
                      order by 1;
                      
                               A TS                                   VAL      after
                      ---------- ----------------------------- ---------- ----------
                               1 2017-07-08 22:58:54.622686000          0   18831036
                               2 2017-07-08 22:58:54.676580000          1   18831036
                      

                       

                      Session 2:

                      select t.*, ora_rowscn from T1 t;
                      
                               A TS                                   VAL ORA_ROWSCN
                      ---------- ----------------------------- ---------- ----------
                               1 2017-07-08 22:58:54.622686000          0   18826316
                               2 2017-07-08 22:58:54.676580000          1   18826316
                      

                       

                      So the ORA_ROWSCN backed up about 5000.

                       

                      Session 1:

                      select t.*, ora_rowscn from T1 t;
                      
                               A TS                                   VAL ORA_ROWSCN
                      ---------- ----------------------------- ---------- ----------
                               1 2017-07-08 22:58:54.622686000          0   18826316
                               2 2017-07-08 22:58:54.676580000          1   18826316
                      

                       

                      Now the lower ORA_ROWSCN is visible from both sessions.

                       

                      Interestingly, I did similar tests using AS OF SCN <nnnn> in Session 2, and I was able to lower the ORA_ROWSCN several times by increments.

                       

                      With ROWDEPENDENCIES, the results are different. The first SELECT that does a delayed block cleanout sets the ORA_ROWSCN for the updated row, and after that it does not change! I guess that is because the row is no longer referenced in the ITL, so when the block SCN is lowered, Oracle doesn't know which rows might be affected.

                       

                      In the scenario above, but with ROWDEPENDENCIES, if Session 1 queried T1 first, the ORA_ROWSCN for the updated row would be 18831036 in both sessions.

                       

                      Best regards, Stew

                      • 8. Re: How can ORA_ROWSCN change between queries when no update?
                        Jonathan Lewis

                        Stew,

                         

                        I must started my response to rp0428 just before you published your worked example, so I didn't see it last night.

                         

                        I wrote a  correction to my original description earlier on this morning - including a description of why you don't see the decrease when rowdependencies are enabled, which is exactly the reason you suggested: copy from the ITL to the dscn happens only once, after which the ITL can be "improved" (i.e. taken further back to be closer to the actual commit SCN) but the improvement won't be duplicated into the dscn.

                         

                        Regards

                        Jonathan Lewis