Re: Autonomous transactions and deadlocks
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-lReceived on Mon Mar 25 2013 - 09:09:45 CET