This discussion is archived
12 Replies Latest reply: Dec 12, 2012 4:38 AM by BillyVerreynne RSS

tracing lost updates

946279 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Change requests procedures :)
  • 8. Re: tracing lost updates
    Purvesh K Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points