How do INSERTS cause deadlocks?
I can see how a delete or an update would - because rows are locked. But an insert? Even at a table level 'lock', how would an INSERT statement cause that?
Okay - I did some searching and found this example that duplicates my issue:
I want to know, why? Why do two inserts into the same table (one in an autonomous transaction) produce a deadlock? Is it the Primary Key issue?
SQL> create table t ( x int primary key );
SQL> insert into t values ( 1 );
1 row created.
SQL> get afiedt.buf
2 pragma autonomous_transaction;
4 insert into t values ( 1);
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
I remember when this place was cool.
You have an "uncommited" primary key, row is locked due to this (in case you rollback!).
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Originally Posted by LKBrwn_DBA
Aren't PK's validated before COMMIT? Should it matter if insert # 1 is rolled back? If I did this from 2 different sessions (non autonomous) then I wouldn't get a deadlock would I?
My first thought too, Hanky. Its has no refences to any other table, and is not referenced itself. Just a two-column table holding 'sequence numbers', when we insert a value and then issue an autonomous insert alongside it - it deadlocks.
Originally Posted by Mr.Hanky
I can't explain why it works this way (the app logic) - I just want to know why as I don't understand the logic of Oracle here.
This is because of autonomous transaction :
Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. You can log events, increment retry counters, and so on, even if the main transaction rolls back.
Basically you are instructing oracle to carry on this transaction on matter what and it tries!
One, who thinks that the other one who thinks that know and does not know, does not know either!
Yes, the autonomous transaction. I realise that the autonomous insert cannot insert a "duplicate" PK until the external transaction commits and deletes the original PK.
I'm getting hung up on why this is flagged as a "deadlock" and not a normal error like a PK constraint violation? If the autonomous transaction is just like another user session, then it should - in my eyes - react like that. And until the external transaction COMMITS the delete, then the autonomous INSERT is violating a PK.
Click Here to Expand Forum to Full Width