How do INSERTS cause deadlocks?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How do INSERTS cause deadlocks?

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    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?

  2. #2
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Okay - I did some searching and found this example that duplicates my issue:

    SQL> create table t ( x int primary key );

    Table created.

    Elapsed: 00:00:00.02
    SQL> insert into t values ( 1 );

    1 row created.

    Elapsed: 00:00:00.00
    SQL> get afiedt.buf
    1 declare
    2 pragma autonomous_transaction;
    3 begin
    4 insert into t values ( 1);
    5 commit;
    6* end;
    SQL> /
    declare
    *
    ERROR at line 1:
    ORA-00060: deadlock detected while waiting for resource
    ORA-06512: at line 4


    Elapsed: 00:00:03.06
    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?

  3. #3
    Join Date
    Jan 2001
    Posts
    3,131
    Are your FK's indexed?
    I remember when this place was cool.

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,459

    Talking Pk

    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

  5. #5
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Quote Originally Posted by LKBrwn_DBA
    You have an "uncommited" primary key, row is locked due to this (in case you rollback!).
    .

    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?

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Quote Originally Posted by Mr.Hanky
    Are your FK's indexed?
    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.

    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.

  7. #7
    Join Date
    Nov 2001
    Posts
    335
    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!

  8. #8
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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.

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