autonomous_transaction

[pranavgovind]

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>

[tamilselvan]

Issue a commit, after the update.

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

[pranavgovind]

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

Issue a commit, after the update.

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

[davey23uk]

its because its an autonomous transaction!!!

[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

[pranavgovind]

Thanks Tamil, Thanks Dav.

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