Re: Autonomous transactions and deadlocks

From: mohamed houri <mohamed.houri_at_gmail.com>
Date: Mon, 25 Mar 2013 09:09:45 +0100
Message-ID: <CAJu8R6izK2NW0nabAHpPOOrM3v+0vEq0_iihSifCdh6xaKUPeA_at_mail.gmail.com>

You can easily simulate a deadlock using one session in which you will use autonomous transaction. See the below link where I have managed to provoque a deadlock using autonoumous transaction http://hourim.wordpress.com/2011/03/20/deadlock-%E2%80%93-part-2-unindexed-foreign-keys/

SQL> create table p as select * from all_users;

Table created.

SQL> alter table p add constraint p_pk primary key (user_id);

Table altered.

SQL> create table c (user_id references p, data varchar2(10));

Table created.

SQL> insert into c(user_id,data) values (0,'test');

1 row created.

SQL> declare
  2 pragma autonomous_transaction;
  3 begin
  4 delete from p where user_id = 72;   5 commit;
  6 end;
  7 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource ORA-06512: at line 4

The reason for that is that an autonomous transaction open a new transaction id inside the same session and this could end up with conflicting locks as the case shown above for unindexed FKs

Best regards
Mohamed Houri
www.hourim.wordpress.com

2013/3/25 <rajugaru.vij_at_gmail.com>

> Hi,
> Can someone explain me, why does AT cause deadlocks.
>
> Eg: I updated a table x in session 1 and want to update same from session
> 2 but with AT. It goes into deadlock. But normal update works fine. I am
> not quite understanding the locking mechanism. Gone through docs, but not
> clear, or unable to understand. Can some one help please?
>
> Thanks
> Sent on my BlackBerry®--
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Bien Respectueusement
Mohamed Houri

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Mar 25 2013 - 09:09:45 CET