943276 wrote:There definitely is:
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.).
2. Use of Checksum:
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.
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.
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?
943276 wrote:Personally, I would prefer them a lot.
Change requests procedures :)
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 ;
943276 wrote:Procedures do not hold lock. It is the SQL statements that lock the records.
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?
943276 wrote:This is not a lost update.
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
s2: proceeds with its update
s1: select * from table where X=x and sees Z=z2