Tuesday, April 20, 2010

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.
POST A COMMENT

6 Comments:

Blogger RAJESH said....

Tom,

From where did you learnt this info ?, is that from Product documentation ? Can you post that link please.

Wed Apr 21, 12:45:00 AM EDT  

Blogger Flado said....

There is a very old and faint memory in my head saying that deadlock detection is performed by a waiting session every three seconds, when the enqueue event times out - this has probably changed several times since that memory got there - what is the current situation?

Wed Apr 21, 05:24:00 AM EDT  

Blogger Thomas Kyte said....

@Rajesh

where did I/do I learn these things. Mostly by getting asked "how does this work" (as the case was with this one) and researching it. This case study was how I learned how it worked.

@Flado

it looks like the magic number 3 is still present, but for the self deadlock, it appears to wait once. For the two session deadlock, it is more along the lines of "3". So, it is still a 3 second timer.

Wed Apr 21, 08:30:00 AM EDT  

Anonymous Sokrates said....

fantastic !

a. I never thought this would lead to a deadlock
b. I always thought Tom Kyte knows everything about deadlocks

I find it very interesting, however (and still do not understand) why I seem to have to wait 6 seconds to get the self deadlock, however, I get it then already after around 3 seconds.

See:

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


Elapsed: 00:00:02.01

ok: ORA-30006 after 2 seconds waiting

SQL> 5
5* select * into l_rec from t for update wait 2;
SQL> c/2/3/
5* select * into l_rec from t for update wait 3;
SQL> /
declare
*
ERROR at line 1:
ORA-30006: resource busy; acquire with WAIT timeout expired
ORA-06512: at line 5


Elapsed: 00:00:03.01

ok: ORA-30006 after 3 seconds waiting

SQL> 5
5* select * into l_rec from t for update wait 3;
SQL> c/3/4/
5* select * into l_rec from t for update wait 4;
SQL> /
declare
*
ERROR at line 1:
ORA-30006: resource busy; acquire with WAIT timeout expired
ORA-06512: at line 5


Elapsed: 00:00:04.01

ok: ORA-30006 after 4 seconds waiting

SQL> 5
5* select * into l_rec from t for update wait 4;
SQL> c/4/5/
5* select * into l_rec from t for update wait 5;
SQL> /
declare
*
ERROR at line 1:
ORA-30006: resource busy; acquire with WAIT timeout expired
ORA-06512: at line 5


Elapsed: 00:00:05.01

ok: ORA-30006 after 5 seconds waiting

SQL> 5
5* select * into l_rec from t for update wait 5;
SQL> c/5/6/
5* select * into l_rec from t for update wait 6;
SQL> /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 5


Elapsed: 00:00:03.05

strange: ORA-00060 already after 3 seconds waiting !!

why not after 6 seconds waiting ?

or (probably better ask the other way round):
why did
"... for update wait 5;" and
"... for update wait 4;"
not raise ORA-00060 after 3 seconds waiting ?

Fri Apr 23, 07:12:00 AM EDT  

Blogger Thomas Kyte said....

@Sokrates,

I wondered the same myself, it is tied into the magic 3 second rule - but not the first time around for some reason. Empirically - it seems pretty fixed at six seconds for the self deadlock for whatever internal reason.

Since I do not really go in an try to read the code (that would just hurt my head :) ) and it isn't anything mission critical - I did not follow it any further, other than to note "that it happens".

Fri Apr 23, 08:52:00 AM EDT  

Blogger Tanel Poder said....

Hi!

Enqueue Deadlock detection still happens at every 3 seconds (after sleep timeout), but it's not enabled for requests where the max WAIT time specified is <= 5 seconds.

This is controlled by this parameter:

_enqueue_deadlock_time_sec "requests with timeout <= this will not have deadlock detection", it defaults to 5 seconds.

If the WAIT time specified is greater than this value then deadlock detection is enabled and will happen every 3 seconds.

Sat Apr 24, 07:58:00 AM EDT  

POST A COMMENT

<< Home