5 Replies Latest reply on May 7, 2016 7:04 PM by APY

    Why deadlock

    APY

      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

        • 1. Re: Why deadlock
          John Thorton

          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

          • 2. Re: Why deadlock
            APY

            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.

            • 3. Re: Why deadlock
              John Thorton

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

               

              OK.

              post reproducible test case.

              • 4. Re: Why deadlock
                Jonathan Lewis

                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

                1 person found this helpful
                • 5. Re: Why deadlock
                  APY

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

                   

                  Regards,

                  Anand Yadav