7 Replies Latest reply: Feb 18, 2013 7:04 AM by oradba11 RSS

    Deadlock on different tables

    oradba11
      Hi,

      I am working on 11g and AIX...We got deadlock recently and we need to investgate why it happen and resolution, so it will not happen in future..
      When we saw deadlock trace file ,it shows 2 sqls queries on different tables..and lock is execlusive lock.

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

      SELECT 1 FROM HOS_DTL WHERE ( HOS_LCN_DTL.HOS_LCN_DTL_ID = :1 ) FOR UPDATE WAIT 180

      and

      ----- Current SQL Statement for this session ------

      DELETE FROM EI_INV WHERE ( EI_INV.EI_INV_ID = :1 )


      But these two tables not related at all 'HOS_DTL' and 'EI_INV' .
      Can deadlock happen if sqls queries fired on those table are not related at all and deadlock
      trace file can show those two sqls ..?

      Any idea....
        • 1. Re: Deadlock on different tables
          John Stegeman
          Can deadlock happen if sqls queries fired on those table are not related at all and deadlock
          Of course - you have shown us one.

          You should do some reading on deadlocks - they occur when 2 sessions are blocking each other by each of them holding a lock on something that the other session is waiting for
          • 2. Re: Deadlock on different tables
            asahide
            Hi,

            Check your alert log and user dump dir..
            Regards,
            • 3. Re: Deadlock on different tables
              oradba11
              hi,

              but in this case these are only select ..for update and update sql queries on a perticular table.....and these are not trying to lock other tables rows..

              then why it is having deadlock issue.....
              • 4. Re: Deadlock on different tables
                Dom Brooks
                Please paste the relevant bits of the deadlock graph.
                • 5. Re: Deadlock on different tables
                  oradba11
                  please check it below ...i do not know how to format it as this forum required ...pls let me know how to format it.


                  *** 2013-01-25 11:12:22.334
                  *** SESSION ID:(783.11125) 2013-01-23 11:02:22.398
                  *** CLIENT ID:() 2013-01-23 11:02:22.398
                  *** SERVICE NAME:(MOSPK) 2013-01-23 11:02:22.398
                  *** MODULE NAME:(MOSOK@world.woo.com (TNS V1-) 2013-01-23 11:02:22.398
                  *** ACTION NAME:() 2013-01-23 11:02:22.398



                  *** 2013-01-23 11:02:22.398
                  DEADLOCK DETECTED ( ORA-00060 )

                  [Transaction Deadlock]

                  The following deadlock is not an ORACLE error. It is a
                  deadlock due to user error in the design of an application
                  or from issuing incorrect ad-hoc SQL. The following
                  information may aid in determining the deadlock:

                  Deadlock graph:
                  ---------Blocker(s)-------- ---------Waiter(s)---------
                  Resource Name process session holds waits process session holds waits
                  TX-00050010-000768a8 292 783 X 286 2671 X
                  TX-000f0018-001685a2 286 2671 X 292 783 X

                  session 783: DID 0001-0124-00000252     session 2671: DID 0001-011E-00000061
                  session 2671: DID 0001-011E-00000061     session 783: DID 0001-0124-00000252

                  Rows waited on:
                  Session 783: obj - rowid = 00018538 - AAAccAAAsAAAAKPAAG
                  (dictionary objn - 99640, file - 44, block - 655, slot - 6)
                  Session 2671: obj - rowid = 000182EE - AAAcZtAAHAAAAOXAAs
                  (dictionary objn - 99054, file - 7, block - 919, slot - 44)

                  ----- Information for the OTHER waiting sessions -----
                  Session 2671:
                  sid: 2671 ser: 62113 audsid: 14183589 user: 88/WMS
                  flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
                  flags2: (0x40009) -/-/INC
                  pid: 286 O/S info: user: oracle, term: UNKNOWN, ospid: 5227
                  image: oracle@ora1
                  client details:
                  O/S info: user: admin100, term: , ospid: 23562
                  machine: MOSOK@world.woo.com program: MOSOK@world.woo.com (TNS V1-
                  application name: MOSOK@world.woo.com (TNS V1-, hash value=2479023224


                  current SQL:
                  SELECT 1 FROM HOS_DTL WHERE ( HOS_LCN_DTL.HOS_LCN_DTL_ID = :1 ) FOR UPDATE WAIT 180

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

                  Information for THIS session:

                  ----- Current SQL Statement for this session (sql_id=fhz323mbxrjb9) -----
                  DELETE FROM EI_INV WHERE ( EI_INV.EI_INV_ID = :1 )
                  • 6. Re: Deadlock on different tables
                    Jonathan Lewis
                    oradba11 wrote:
                    hi,

                    but in this case these are only select ..for update and update sql queries on a perticular table.....and these are not trying to lock other tables rows..

                    then why it is having deadlock issue.....
                    Deadlocks don't have to be about what is happening RIGHT NOW, they're actually about what has happened (but has not yet committed) in the past:

                    session 1:
                    SELECT 1 FROM HOS_DTL WHERE ( HOS_LCN_DTL.HOS_LCN_DTL_ID = 999 ) FOR UPDATE WAIT 180

                    session 2:
                    DELETE FROM EI_INV WHERE ( EI_INV.EI_INV_ID = 1234)

                    Session 1:
                    DELETE FROM EI_INV WHERE ( EI_INV.EI_INV_ID = 1234)
                    -- starts to wait

                    Session 2:
                    SELECT 1 FROM HOS_DTL WHERE ( HOS_LCN_DTL.HOS_LCN_DTL_ID = 999 ) FOR UPDATE WAIT 180
                    -- starts to wait:

                    Within 3 seconds session 1 will report a deadlock and rollback to an internal savepoint it placed just before executing the delete statement.

                    Your situation will, sometimes, be a little harder to interpret than a typical deadlock because you've got timeout limits on some of your statements - but the basic principal doesn't change.

                    Regards
                    Jonathan Lewis
                    • 7. Re: Deadlock on different tables
                      oradba11
                      Thanks for sharing knowledge...

                      I think we need to look at apllication for more infromation....