What will happen if...

So, something else I learned recently...

Say you have a table T:

ops$tkyte%ORA10GR2> create table t ( x int );
Table created.
ops$tkyte%ORA10GR2> insert into t values ( 1 );
1 row created.
ops$tkyte%ORA10GR2> commit;
Commit complete.

and you update a row in that table:

ops$tkyte%ORA10GR2> update t set x = x+1;
1 row updated.

and using an evil autonomous transaction you try to lock that row (in the same session, but a new transaction - one that cannot 'see' the effects of the parent transaction)

ops$tkyte%ORA10GR2> declare
2          pragma autonomous_transaction;
3          l_rec  t%rowtype;
4  begin
5          select * into l_rec from t for update wait 5;
6          commit;
7  end;
8  /
declare
*
ERROR at line 1:
ORA-30006: resource busy; acquire with WAIT timeout expired
ORA-06512: at line 5

So far, it all seems normal. You asked to wait for 5 seconds, you did - and you time out. But, what happens if you wait longer?

ops$tkyte%ORA10GR2> declare
2          pragma autonomous_transaction;
3          l_rec  t%rowtype;
4  begin
5          select * into l_rec from t for update wait 6;
6          commit;
7  end;
8  /
declare
*
ERROR at line 1:
??????????????????????????????????

What error are you expecting that time - hint, it is not ORA-30006 it is

ops$tkyte%ORA10GR2> declare
2          pragma autonomous_transaction;
3          l_rec  t%rowtype;
4  begin
5          select * into l_rec from t for update wait 6;
6          commit;
7  end;
8  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 5

apparently the self deadlock code kicks in before the timeout happens and the deadlock detection code doesn't see the "we will eventually time out"

And yes, this applies to multi-session cases as well - run this script to see that:

drop table t;
set echo on
create table t ( x int );
insert into t values ( 1 );
insert into t values ( 2 );
commit;
select * from t where x = 1 for update;
set echo off
prompt in another session issue:
prompt select * from t where x = 2 for update;;
pause
prompt in another session issue:
prompt select * from t where x = 1 for update wait 10;;
set echo on
select * from t where x = 2 for update;

It will deadlock one of the sessions - without waiting for the "wait" timeout.

So, deadlock detection trumps a waiting period. If the waiting period expires before the deadlock routines kick in - you get the ora-30006, if the deadlock routines kick in before the timeout - you get ora-60.