DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: autonomous_transaction

  1. #1
    Join Date
    Dec 2005
    Posts
    195

    autonomous_transaction

    Dear all, Can any one please help me why deadlock is happening here.


    SQL> create table gtt ( x int primary key )
    2 ;

    Table created.

    SQL> insert into gtt values ( 1 );

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> update gtt set x = 1;

    1 row updated.

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


    SQL>

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Issue a commit, after the update.

    The autonomous transaction tries to delete the row which is not yet committed.

  3. #3
    Join Date
    Dec 2005
    Posts
    195
    Tamil,

    In general, the deadlock occurs when two or more session is waiting data which is locked by each session. Here we are trying to delete the data which is locked by update statement. so this should be lock(not deadlock). To my understanding, it is not dead lock. Please correct me. I think, i am missing some thing here. Thanks

    Quote Originally Posted by tamilselvan
    Issue a commit, after the update.

    The autonomous transaction tries to delete the row which is not yet committed.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    its because its an autonomous transaction!!!

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    After the update the row is locked.

    Then you try to delete the same row in a autonomous transaction. Oracle is not able to delete, hence you get ora-60 error.

    For further reading about dead lock, plz read
    http://www.oracleact.com/papers/deadlock.html

  6. #6
    Join Date
    Dec 2005
    Posts
    195
    Thanks Tamil, Thanks Dav.

    Quote Originally Posted by tamilselvan
    After the update the row is locked.

    Then you try to delete the same row in a autonomous transaction. Oracle is not able to delete, hence you get ora-60 error.

    For further reading about dead lock, plz read
    http://www.oracleact.com/papers/deadlock.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width