3 Replies Latest reply on Dec 23, 2013 2:31 PM by Jonathan Lewis

    Bitmap causing deadlock

    rishwinger
    rishwinger Dec 23, 2013 10:59 AM

      Hi Experts

       

      I understand the basic problem why deadlocks can happen with bitmap indexes in concurrent environments.

       

      Thanks to TOM http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:582867200346727043

       

      But, could anyone please explain what exactly is happening in the following case?


      Deadlock graph:

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

      Resource Name process session holds waits process session holds waits

      TX-001a000a-000a9be5 146 393 X 149 947 S

      TX-000b0020-007c4e3e 149 947 X 146 393 S

       

      session 393: DID 0001-0092-000070C6 session 947: DID 0001-0095-0000457B

      session 947: DID 0001-0095-0000457B session 393: DID 0001-0092-000070C6

       

      Rows waited on:

        Session 393: obj - rowid = 00087875 - AACHh1AAAAAAAAAAAA

        (dictionary objn - 555125, file - 0, block - 0, slot - 0)

        Session 947: obj - rowid = 0008785C - AACHhcAAAAAAAAAAAA

        (dictionary objn - 555100, file - 0, block - 0, slot - 0)

       

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

      Session 947:

        sid: 947 ser: 7443 audsid: 80871604 user: 267/xxxxxx

        flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

        flags2: (0x40009) -/-/INC

        pid: 149 O/S info: user: grid, term: UNKNOWN, ospid: 25682

        image: oracle@kkkkkkkk

        client details:

        O/S info: user: ggggg, term: unknown, ospid: 1234

        machine: ffffff program: JDBC Thin Client

        application name: JDBC Thin Client, hash value=2546894660

        current SQL:

       

        INSERT INTO child table

       

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

       

      Information for THIS session:

       

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

        

        UPDATE parent table

        SET fk = :1 ,

        WHERE 

        PK=:4

        

        

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

       

      Session 393: obj - rowid = 00087875 - AACHh1AAAAAAAAAAAA

      (dictionary objn - 555125, file - 0, block - 0, slot - 0)

      Session 947: obj - rowid = 0008785C - AACHhcAAAAAAAAAAAA

      (dictionary objn - 555100, file - 0, block - 0, slot - 0)

       

      555125,555100 data_object_id correspond to Bitamp index on parent ,child table respectively

       

      The point which confuses is

      STEP-1 is an Insert in child table

      STEP-2 is update on parent table

       

      Different bitmap keys are being updated by above steps then why there is a deadlock ?

       

       

      Thanks

      Rishwinger

      • 2213 Views
      • Tags:
        • 1. Re: Bitmap causing deadlock
          Jonathan Lewis
          Jonathan Lewis Dec 23, 2013 12:12 PM (in response to rishwinger)

          The locking SQL statements that started the deadlock sequences need not be the SQL statements that are currently executing. The initial locking statements may not even be in memory any more (see:http://jonathanlewis.wordpress.com/2009/04/19/locking-sql/

           

          Regards

          Jonathan Lewis

          • 2. Re: Bitmap causing deadlock
            rishwinger
            rishwinger Dec 23, 2013 12:52 PM (in response to Jonathan Lewis)

            Jonathan  Thanks for the reply,


            I agree sql that started the deadlock might not exist here but in that case we could have encountered deadlock even with a single DML on any table(parent or child table)


            I don't understand the combination of both tables with BITMAP INDEXES on both tables causing the issue.


            Parent table -  BITMAP_INDEX (555100)

            Child table   -  BITMAP_INDEX (555125)

             

            Could you please explain what could have happened, why two bitmap indexes are involved because different bitmap indexes will refer to different bitmap keys so they cannot lock each other  ?


            Regards

            Rishwinger

            • 3. Re: Bitmap causing deadlock
              Jonathan Lewis
              Jonathan Lewis Dec 23, 2013 2:31 PM (in response to rishwinger)

              I repeat:

              The locking SQL statements that started the deadlock sequences need not be the SQL statements that are currently executing. The initial locking statements may not even be in memory any more

               

              The deadlock could well be showing mode 4 because bitmap indexes are involved; the deadlock may have appeared but you seem to be assuming that those events MUST be the direct cause of the deadlock and demanding an explanation to suit your assumption.  All it takes is:

              1. session 1 modifies parent
              2. session 2 modifies child
              3. session 1 modifies child and waits on an index-related lock
              4. session 2 modifies parent and waits on an index-related lock
              5. session 1 (typically) reports a deadlock.

               

              Regards

              Jonathan Lewis

              1 person found this helpful