Lock detection and resolution

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.

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?

Thanks... Received on Thu May 24 2001 - 10:54:19 CDT