Re: Lock detection and resolution

A copy of this was sent to onlyforposting_at_yahoo.com (Vikas Agnihotri) (if that email address didn't require changing) On 24 May 2001 08:54:19 -0700, you wrote:

>Oracle 8.1.5
>
>John does the following in a session:
>
>insert into emp(empno) values (1);
>commit;
>update emp set ename='Vikas' where empno=1
>[He does NOT commit and goes for a cup of coffee]
>
>Sue does the following in her session:
>update emp set ename='Vikas' where empno='1';
>
>Sue's session will hang miserably until John commits or rolls back.
>
>Not a desirable situation.
>

correct -- but not because of the blocking in my opinion but because Sue will overwrite Johns work without seeing it -- it is called a LOST UPDATE in database speak.

What your transactions should look like in this interactive application is this (assume you are working on the DEPT table in the scott/tiger schema):

1. your application "selects * from dept"
2. your end user decides they want to modify the data -- they want to rename deptno=10
3. you will issue:

    select * from t where deptno = <the value of deptno you selected out>

                      and dname = <the value of dname you selected out>
                      and loc = <the value of loc you selected out>
      FOR UPDATE NOWAIT;

one of three things will happen here:

1. you get 1 row back. Thats great, continue on. The row is yours. No one else will lock it.
2. you get 0 rows back. Thats bad -- someone else updated the row since you read it out. You MUST requery this record and let your end user see it, else you would blindly overwrite their changes (lost update)
3. you get an ORA-00054. That row is currently locked by another session. You'll have to try again later

If 2 or 3 happens -- stop here. Else, you have the row -- go ahead and let the end user play with it. When they are ready , do the update and commit the changes.

You could take another approach which is:

do steps a) and b) above.

let them play with the data, make their changes. YOU will keep a before copy of the data (the data you selected out) and let them work on an "after copy" of the data. When they get to the "update" part -- you will then do step C) above (waiting until the last moment to check and lock the row). If 1) is the outcome -- great, do the update and commit. If 2) is the outcome -- your end user is the end loser, they have to requery the data and redo their work (or you try to merge the changes/whatever). If 3) is the outcome, they gotta wait.

This is what many tools such as Oracle Forms do automagically for you in the background.

>What can be done in the app code to detect such a situation before
>issuing the deadly update/delete on a uncommited row?
>
>I see from v$lock that John is holding a row-x (shared?) lock of
>lmode=3 and type=TM. But this, in itself, is not a problem.
>
>How can I tell if issuing a DML on the row is going to hang?
>
>Basically, my developers are asking if there is a way that, given a
>empno, they can query Oracle internals to find out if that row is
>free'n'clear or locked by a uncommited tranaction?
>

the select for update nowait will do that HOWEVER, you really need to do the above logic in an interactive application to avoid the lost update that your blocking is indicative of!

>Thanks...

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp