Forum Stats

  • 3,727,656 Users
  • 2,245,433 Discussions
  • 7,852,906 Comments

Discussions

Why deadlock

Anand Yadav
Anand Yadav Member Posts: 89

CREATE OR REPLACE PROCEDURE PROC1

AS

BEGIN

    INSERT INTO TAB1 ( cust_id, cust_name)

         VALUES (12, 'Some name');

    PROC2;

END;

/

SHOW ERRORS;

CREATE OR REPLACE PROCEDURE PROC2

AS

    PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

    INSERT INTO TAB2 ( item_id, cust_id, item_name)

         VALUES (12, 12, 'Any name');

    COMMIT;

END;

/

SHOW ERRORS;

EXECUTE PROC1;

Error:

ORA-00060: deadlock detected while waiting for resource

ORA-06512: at "schema.PROC2", line 5

ORA-06512: at "schema.PROC1", line 6

ORA-06512: at line 1

I have referential integrity defined on  TAB2 (cust_id) to TAB1(cust_id) .  as per my understanding, Autonomous transaction creates separate transaction and in that situation it should raise parent key not found exception. Please help me to understand.

Also if I insert parent record in one session and without committing it if I insert child record in another session then session related to child record is hanging in place of giving error.

Note:This code is just for illustration purpose

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,578 Gold Crown
    edited May 2016 Accepted Answer

    You've nearly explained the problem yourself by using two separate sessions (which strips the issue to a bare minimum).

    The second session (child insert) can see (under the covers) that there is a parent key which may become available for referential integrity to be satisfied, so it waits until the first session commits or rolls back.  If the first session commits the second session will continue, if the first session rolls back the second session will raise the referential integrity error.

    You get a similar pattern of behaviour if you insert and commit the parent in the first session, then delete the parent from the first session and DON'T commit. Again the second session will wait for the first session to commit when it tries to insert a child row.  In this case if the first session commits the second session will raise an error, if the first session rolls back the second session will continue.

    Moving the processing into the autonomous transaction case - the autonomous transaction has to wait for the main transaction to commit or rollback, but the main transaction can't do either because it's waiting for the autonomous transaction to complete. Hence deadlock.

    Regards

    Jonathan Lewis

    Anand Yadav

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited May 2016
    Anand Yadav wrote:
    
    CREATE OR REPLACE PROCEDURE PROC1
    AS
    BEGIN
        INSERT INTO TAB1 ( cust_id, cust_name)
             VALUES (12, 'Some name');
        PROC2;
    END;
    /
    SHOW ERRORS;
    
    CREATE OR REPLACE PROCEDURE PROC2
    AS
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        INSERT INTO TAB2 ( item_id, cust_id, item_name)
             VALUES (12, 12, 'Any name');
        COMMIT;
    END;
    /
    SHOW ERRORS;
    
    EXECUTE PROC1;
    
    Error:
    ORA-00060: deadlock detected while waiting for resource
    ORA-06512: at "schema.PROC2", line 5
    ORA-06512: at "schema.PROC1", line 6
    ORA-06512: at line 1
    
    
    I have referential integrity defined on  TAB2 (cust_id) to TAB1(cust_id) .  as per my understanding, Autonomous transaction creates separate transaction and in that situation it should raise parent key not found exception. Please help me to understand. 
    
    Also if I insert parent record in one session and without committing it if I insert child record in another session then session related to child record is hanging in place of giving error.
    
    Note:This code is just for illustration purpose
    

    post complete excerpt from alert_SID.log file that shows whole ORA-00060 trace dump

  • Anand Yadav
    Anand Yadav Member Posts: 89
    edited May 2016

    ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file D:\ORACLE\diag\rdbms\o12cr102\o12cr102\trace\o12cr102_ora_5716.trc.

    Deadlock graph:

                                              ---------Blocker(s)--------  ---------Waiter(s)---------

    Resource Name                             process session holds waits  process session holds waits

    TX-00090037-0000014B-00000000-00000000         33     204     X             33     204           S

    session 204: DID 0001-0021-00000027 session 204: DID 0001-0021-00000027

    Rows waited on:

      Session 204: no row

    ----- Information for the OTHER waiting sessions -----

    ----- End of information for the OTHER waiting sessions -----

    Information for THIS session:

    ----- Current SQL Statement for this session (sql_id=g3ur8v65cfhh7) -----

    INSERT INTO TAB2 VALUES (12, 12, 'Any name')

    ----- PL/SQL Stack -----

    ----- PL/SQL Call Stack -----

      object      line  object

      handle    number  name

    000007FF2EE2E500         5  procedure SCOTT.PROC1

    000007FF2EF28630         7  procedure SCOTT.PROC

    000007FF2ED63F18         1  anonymous block

    ===================================================

    PROCESS STATE

    -------------

    Process global information:

         process: 0x000007FF3D5E95B0, call: 0x000007FF32C63E28, xact: 0x000007FF390A6868, curses: 0x000007FF3D28AD68, usrses: 0x000007FF3D28AD68

         in_exception_handler: no

      ----------------------------------------

      SO: 0x000007FF3D5E95B0, type: 2, owner: 0x0000000000000000, flag: INIT/-/-/0x00 if: 0x3 c: 0x3

       proc=0x000007FF3D5E95B0, name=process, file=ksu.h LINE:13949, pg=0 conuid=0

      (process) Oracle pid:33, ser:20, calls cur/top: 0x000007FF32C63E28/0x000007FF3CF11B38

                flags : (0x0) -  icon_uid:0

                flags2: (0x0),  flags3: (0x10)

                intr error: 0, call error: 0, sess error: 0, txn error 0

                intr queue: empty

        ksudlp FALSE at location: 0

        (post info) last post received: 140 0 2

                    last post received-location: ksl2.h LINE:3108 ID:kslpsr

                    last process to post me: 0x7ff3d5da820 1 6

                    last post sent: 0 0 26

                    last post sent-location: ksa2.h LINE:290 ID:ksasnd

                    last process posted by me: 0x7ff3d5da820 1 6

                    waiter on post event: 0

        (latch info) hold_bits=0x0

        Process Group: DEFAULT, pseudo proc: 0x000007FF3D74DC10

        O/S info: user: SYSTEM, term: J1013169W7LT, ospid: 5716

        OSD pid info:

    I'm not sure If I can add full content of trace file here. This error can be applicable on any database with just simple example.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited May 2016

    >This error can be applicable on any database with just simple example

    OK.

    post reproducible test case.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,578 Gold Crown
    edited May 2016 Accepted Answer

    You've nearly explained the problem yourself by using two separate sessions (which strips the issue to a bare minimum).

    The second session (child insert) can see (under the covers) that there is a parent key which may become available for referential integrity to be satisfied, so it waits until the first session commits or rolls back.  If the first session commits the second session will continue, if the first session rolls back the second session will raise the referential integrity error.

    You get a similar pattern of behaviour if you insert and commit the parent in the first session, then delete the parent from the first session and DON'T commit. Again the second session will wait for the first session to commit when it tries to insert a child row.  In this case if the first session commits the second session will raise an error, if the first session rolls back the second session will continue.

    Moving the processing into the autonomous transaction case - the autonomous transaction has to wait for the main transaction to commit or rollback, but the main transaction can't do either because it's waiting for the autonomous transaction to complete. Hence deadlock.

    Regards

    Jonathan Lewis

    Anand Yadav
  • Anand Yadav
    Anand Yadav Member Posts: 89
    edited May 2016

    Thanks Jonathan for the nice explanation. It helped to clear my doubts

    Regards,

    Anand Yadav

This discussion has been closed.