12 Replies Latest reply: Dec 12, 2012 6:38 AM by Billy~Verreynne RSS

    tracing lost updates

    946279
      situation is that probably currently application suffers from lost updates. but it must be proved :) pure update statements were rewritten into select for update nowait so that in case updates conflicts one of them will be interrupted suggesting that indeed lost update was going to happen, which can be traced more deeply to be 100% sure. and here is the question: is it possible to get more detailed information about which particular row/rows/session prevented select for update nowait to be successful? I would like to have most data gathered possible during unsuccessful select for update.

      generally I'm looking for a solution. If you have more adequate approach please share, I will think how it is applicable within my current situation (permissions etc.).

      thanks
        • 1. Re: tracing lost updates
          Hoek
          What exactly do you mean when you write about 'probably suffers from lost updates'?
          http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:30562552526857

          Also, please post usual details like database version, and a small example of the situation.
          • 2. Re: tracing lost updates
            946279
            11g R2.

            by lost updates I mean the following scenario:

            s1: update set Z=z1 where value X=x
            s2: update set Z=z2 where value X=x -- it gets blocked and waits
            s1: commit
            s2: proceeds with its update
            s2: commit
            s1: select * from table where X=x and sees Z=z2

            now with select for update nowait s2 session will interrupt, which will indicate that the lock for a row couldn't be applied. this needs to be further investigated to be able to present to the person responsible: "look, now z1 was overwritten. if it happened that s2 was first then z2 would be overwritten. so how come the person be from Z=Oregon if s2 wins or Z=Texas if s1 wins. this must be addressed and such situiation resolved which one is right".

            as for a situation: it is a stateless scenario (as Kyte uses this word) so I cannot reproduce it in controlled environment. But hopefully I can catch it with select for update and investigate deeper..
            • 3. Re: tracing lost updates
              Purvesh K
              943276 wrote:
              situation is that probably currently application suffers from lost updates. but it must be proved :) pure update statements were rewritten into select for update nowait so that in case updates conflicts one of them will be interrupted suggesting that indeed lost update was going to happen, which can be traced more deeply to be 100% sure. and here is the question: is it possible to get more detailed information about which particular row/rows/session prevented select for update nowait to be successful? I would like to have most data gathered possible during unsuccessful select for update.

              generally I'm looking for a solution. If you have more adequate approach please share, I will think how it is applicable within my current situation (permissions etc.).
              There definitely is:
              You are resorting to a Pessimistic approach to counter Lost Updates. There is an Optimistic approach too.

              1. Use of Timestamp:
              You will have to add a new Column (if not existed) that shall be populated with the SYS_TIMESTAMP, every time a DML activity occurs on each row. This new column shall be updated via a Trigger on table.
              Logic for DML would go as follows:
              1. Fetch the current timestamp of the record to be modified.
              2. While updating, add the Timestamp condition to the Where predicate. Thus ensuring, if another session has already updated the record, the current update will not over-write the same.
              2. Use of Checksum:
              You will have to add a new column that will store the hash value of combination of columns, preferentially the columns participating in Primary key.
              1. Fetch the current checksum of the record to be modified.
              2. While updating, add the checksum condition to the Where predicate. Thus ensuring, if another session has already updated the record, the current update will not over-write the same.
              • 4. Re: tracing lost updates
                946279
                Purvesh K, I read Kyte's book too to some extend :) I know this.

                I'm not looking for a complete solution which you provide, I'm looking for evidences. I want to know most about the row/rows/sessions that caused conflict so that I can get particular examples of values, time etc.
                • 5. Re: tracing lost updates
                  Hoek
                  I can catch it with select for update and investigate deeper..
                  Isn't catching and logging ORA-00054 (and other possible situations of interest) an option then?
                  http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:30562552526857#41741666386121
                  http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:30562552526857#47129478163120
                  • 6. Re: tracing lost updates
                    Purvesh K
                    943276 wrote:
                    Purvesh K, I read Kyte's book too to some extend :) I know this.

                    I'm not looking for a complete solution which you provide, I'm looking for evidences.

                    Edited by: 943276 on 2012-12-05 10:51
                    Exactly, when you know the solution, what stops from trying.
                    • 7. Re: tracing lost updates
                      946279
                      Change requests procedures :)
                      • 8. Re: tracing lost updates
                        Purvesh K
                        943276 wrote:
                        Change requests procedures :)
                        Personally, I would prefer them a lot.

                        If you just need to find which rows have prevented an Update or some information about them, you can use the below query to find them:
                        select do.object_name,
                            row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
                            dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) row_id
                            from v$session s, dba_objects do
                            where sid in (
                                          select s2.sid
                                            from v$lock l1, v$session s1, v$lock l2, v$session s2
                                            where s1.sid=l1.sid and s2.sid=l2.sid
                                            and l1.BLOCK=1 and l2.request > 0
                                            and l1.id1 = l2.id1
                                            and l2.id2 = l2.id2
                                         )
                            and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;
                        • 9. Re: tracing lost updates
                          946279
                          thanks Purvesh K! looks like your code gives important answers to me.

                          is is possible to know more about session that holds a lock? for example: if session runs procedures one by one, and the lock was taken inside some procedure, possible to know it's name? or "identificator" for that session along with a time at which it began?

                          thanks!
                          • 10. Re: tracing lost updates
                            Purvesh K
                            943276 wrote:
                            thanks Purvesh K! looks like your code gives important answers to me.

                            is is possible to know more about session that holds a lock? for example: if session runs procedures one by one, and the lock was taken inside some procedure, possible to know it's name? or "identificator" for that session along with a time at which it began?

                            thanks!
                            Procedures do not hold lock. It is the SQL statements that lock the records.

                            So, all that you can know is the SQL that has held the lock. There actually is not a way to accurately pin point the SQL, explains Jonathan Lewis. read Locking SQL by J Lewis. Hope this helps.
                            • 11. Re: tracing lost updates
                              Stefan Jager
                              Hi 943276,

                              Sounds like Workspace Manager could be a solution for you: http://docs.oracle.com/cd/E11882_01/appdev.112/e11826/long_intro.htm#g1032767

                              HTH,
                              Stefan
                              • 12. Re: tracing lost updates
                                Billy~Verreynne
                                943276 wrote:
                                11g R2.

                                by lost updates I mean the following scenario:

                                s1: update set Z=z1 where value X=x
                                s2: update set Z=z2 where value X=x -- it gets blocked and waits
                                s1: commit
                                s2: proceeds with its update
                                s2: commit
                                s1: select * from table where X=x and sees Z=z2
                                This is not a lost update.

                                A lost update means:
                                t1) s1 reads row1 (X=x1,Y=y1)
                                t2) s2 reads row1 (X=x1,Y=y1)
                                t3) s1 sets X=x1, Y=y2 for row1 and commits
                                t4) s2 sets X=x2, Y=y1 for row1 and commits
                                t5) row 1 is now (X=x2,Y=y1)

                                This means that row1's column Y has been overwritten with an older value - and that the valid non-conflicting update of session 1 has been undone by a fault on the part of session 2.

                                Lost updates only happens (should happen) with optimistic locking that has been incorrectly implemented. The pessimistic locking scenario you sketched is not a lost update.

                                The actual problem with your scenario is 2 separate sessions (business processes/users) attempting to change the EXACT SAME data at the SAME time. Pessimistic locking will prevent lost updates. However, it does not prevent session 2 from changing what session 1 did - whether immediately after session 1's commit, or a year afterwards.

                                Your scenario describes an application logic/business conflict issue - and not a locking or transaction isolation issue.