1 2 Previous Next 16 Replies Latest reply: Feb 19, 2013 1:06 PM by P.Forstmann RSS

    Identifying a deadlock with completely unrelated tables

    Michael-O
      Hi folks,

      we receive an ORA-00060 in our database occasionally. I fully understand what could cause a deadlock. I have read several Oracle notes and threads but we do neither

      - update a PK
      - use a FK
      - nor have a bitmap index in place

      The weird thing is that the deadlock occurs when two completely unrelated tables are updated. In both cases a status column is updated with the respective PK.

      Here is the trace file excerpt:
      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-000a001a-0011f392        21     175     X             22     176           X
      TX-00050012-0002d3fd        22     176     X             21     175           X
      
      session 175: DID 0001-0017-0000127B     session 176: DID 0001-0017-00001273
      session 176: DID 0001-0017-00001273     session 175: DID 0001-0017-0000127B
      
      Rows waited on:
        Session 175: obj - rowid = 00004040 - AAAEEOAAFAAD4kOAAg
        (dictionary objn - 16448, file - 5, block - 1018126, slot - 32)
        Session 176: obj - rowid = 00004012 - AAAEEcAAFAAG6OUAAk
        (dictionary objn - 16402, file - 5, block - 1811348, slot - 36)
      
      ----- Information for the OTHER waiting sessions -----
      Session 176:
        sid: 176 ser: 2271 audsid: 2291749 user: 35/SMARTLD
          flags: (0xe5) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
          flags2: (0x40008) -/-
        pid: 22 O/S info: user: oracle, term: UNKNOWN, ospid: 3700
          image: oracle@blnn726x.ww004.siemens.net (S001)
        client details:
          O/S info: user: smartld, term: JDBC, ospid: 1234
          machine: blnn725x program: SMART-LD Web Application 2.4.17
          application name: SMART-LD Web Application 2.4.17, hash value=682445801
        current SQL:
        update st_pr_prg_aufrufe set speicherung = :1  where vbs_id = :2
      
      ----- End of information for the OTHER waiting sessions -----
      
      Information for THIS session:
      
      ----- Current SQL Statement for this session (sql_id=cy1wynvmwusug) -----
      update st_td_arbeitsdatensatz set status = :1  where id = :2
      ===================================================
      PROCESS STATE
      As you can see tables st_pr_prg_aufrufe and st_td_arbeitsdatensatz are updated. Tables, ROWIDs and blocks are completely different.
      I am completely lost where to start digging. Is anyone able to guide me into the right direction?

      My system is:

      - Red Hat Enterprise Linux Workstation release 6.3 (Santiago)
      - Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production; With the Partitioning and Real Application Testing options
        • 1. Re: Identifying a deadlock with completely unrelated tables
          Dom Brooks
          These are mode 6 TX locks - normally, this is as simple a deadlock scenario as you get.

          This is about transactions, not necessarily just about what statements are currently executing.

          At time T1, session S1 updates row in table T1, no commit.
          At time T2, session S2 updates row in table T2, no commit.
          At time T3, session S1 tries to updates row in table T2, waits on S2.
          At time T4, session S2 tries to updates row in table T1, waits on S1.
          Deadlock detected.

          This is a situation where the deadlock trace file is often correct about this being a flaw in the design of the application.
          • 2. Re: Identifying a deadlock with completely unrelated tables
            Michael-O
            Dom, thanks for the reply. Do you mean that the updates I see are not necessarily related to the deadlock but simply happen in the deadlock transactions?
            If so, how am I supposed to find the error in my app at all?
            • 3. Re: Identifying a deadlock with completely unrelated tables
              sb92075
              Michael-O wrote:
              Dom, thanks for the reply. Do you mean that the updates I see are not necessarily related to the deadlock but simply happen in the deadlock transactions?
              If so, how am I supposed to find the error in my app at all?
              Inspect the application code to find where UPDATE statements below exist
              update st_pr_prg_aufrufe set speicherung = :1 where vbs_id = :2
              update st_td_arbeitsdatensatz set status = :1 where id = :2
              • 4. Re: Identifying a deadlock with completely unrelated tables
                Dom Brooks
                Do you mean that the updates I see are not necessarily related to the deadlock but simply happen in the deadlock transactions?
                Both statements are involved in the deadlock otherwise you wouldn't have a deadlock.

                But you have more than two locked resources involved in the deadlock and therefore two different table updates involved.

                What the deadlock graph cannot tell you is all the statement executions that were involved in getting to the deadlock situation.
                • 5. Re: Identifying a deadlock with completely unrelated tables
                  Michael-O
                  sb92075 wrote:
                  Michael-O wrote:
                  Dom, thanks for the reply. Do you mean that the updates I see are not necessarily related to the deadlock but simply happen in the deadlock transactions?
                  If so, how am I supposed to find the error in my app at all?
                  Inspect the application code to find where UPDATE statements below exist
                  update st_pr_prg_aufrufe set speicherung = :1 where vbs_id = :2
                  update st_td_arbeitsdatensatz set status = :1 where id = :2
                  Well, actually those are completely distinct parts of the webapp (Java) where this update statement is triggered but both do have one in common. They both call a helper class with contains a static method for manipulating column values. First, the column is fetched, then modified and finally updated. Select and update happen in two differnt connections. I have modified that right now.

                  Generally, can this be a case due to static methods and deadlocks? Even though, this has never happens in conjunction with other tables but only these two.
                  • 6. Re: Identifying a deadlock with completely unrelated tables
                    sb92075
                    Michael-O wrote:
                    sb92075 wrote:
                    Michael-O wrote:
                    Dom, thanks for the reply. Do you mean that the updates I see are not necessarily related to the deadlock but simply happen in the deadlock transactions?
                    If so, how am I supposed to find the error in my app at all?
                    Inspect the application code to find where UPDATE statements below exist
                    update st_pr_prg_aufrufe set speicherung = :1 where vbs_id = :2
                    update st_td_arbeitsdatensatz set status = :1 where id = :2
                    Well, actually those are completely distinct parts of the webapp (Java) where this update statement is triggered but both do have one in common. They both call a helper class with contains a static method for manipulating column values. First, the column is fetched, then modified and finally updated. Select and update happen in two differnt connections. I have modified that right now.

                    Generally, can this be a case due to static methods and deadlocks? Even though, this has never happens in conjunction with other tables but only these two.
                    Oracle is too dumb to lie about the deadlock & only solution is to modify the application
                    such that the deadlock contention does not occur.
                    • 7. Re: Identifying a deadlock with completely unrelated tables
                      Michael-O
                      sb92075 wrote:
                      Michael-O wrote:
                      sb92075 wrote:
                      Michael-O wrote:
                      Dom, thanks for the reply. Do you mean that the updates I see are not necessarily related to the deadlock but simply happen in the deadlock transactions?
                      If so, how am I supposed to find the error in my app at all?
                      Inspect the application code to find where UPDATE statements below exist
                      update st_pr_prg_aufrufe set speicherung = :1 where vbs_id = :2
                      update st_td_arbeitsdatensatz set status = :1 where id = :2
                      Well, actually those are completely distinct parts of the webapp (Java) where this update statement is triggered but both do have one in common. They both call a helper class with contains a static method for manipulating column values. First, the column is fetched, then modified and finally updated. Select and update happen in two differnt connections. I have modified that right now.

                      Generally, can this be a case due to static methods and deadlocks? Even though, this has never happens in conjunction with other tables but only these two.
                      Oracle is too dumb to lie about the deadlock & only solution is to modify the application
                      such that the deadlock contention does not occur.
                      That's easier said than done. Identifying that code is really hefty. I try a few options on the static method and see if this really applies.
                      • 8. Re: Identifying a deadlock with completely unrelated tables
                        sb92075
                        Michael-O wrote:

                        That's easier said than done. Identifying that code is really hefty. I try a few options on the static method and see if this really applies.
                        The other option is to do nothing & live with the (occasional?) deadlocks.
                        • 9. Re: Identifying a deadlock with completely unrelated tables
                          869405
                          Try this simple query to see who is blocking who:

                          select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
                          from v$lock l1, v$session s1, v$lock l2, v$session s2
                          where s1.sid=l1.sid and s2.sid=l2.sid
                          and l1.BLOCK=1 and l2.request > 0
                          and l1.id1 = l2.id1
                          and l2.id2 = l2.id2;
                          • 10. Re: Identifying a deadlock with completely unrelated tables
                            sb92075
                            Pierre wrote:
                            Try this simple query to see who is blocking who:

                            select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
                            from v$lock l1, v$session s1, v$lock l2, v$session s2
                            where s1.sid=l1.sid and s2.sid=l2.sid
                            and l1.BLOCK=1 and l2.request > 0
                            and l1.id1 = l2.id1
                            and l2.id2 = l2.id2;
                            Oracle automagically detects & resolves Deadlock before you can even think to launch SQL above!
                            • 11. Re: Identifying a deadlock with completely unrelated tables
                              P.Forstmann
                              Try to look for other places in code where same tables are updated but in a different order. A frequent deadlock cause is to lock same rows from different tables but not in the same order.
                              • 12. Re: Identifying a deadlock with completely unrelated tables
                                869405
                                pmdba@gemprod > select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking '
                                || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
                                2 from v$lock l1, v$session s1, v$lock l2, v$session s2
                                3 where s1.sid=l1.sid and s2.sid=l2.sid
                                4 and l1.BLOCK=1 and l2.request > 0
                                5 and l1.id1 = l2.id1
                                6 and l2.id2 = l2.id2;

                                BLOCKING_STATUS
                                ----------------------------------------------------------------------------------------------------
                                PMDBA@WAI\PGH068 ( SID=301 ) is blocking PMDBA@WAI\PGH068 ( SID=203 )
                                PMDBA@WAI\PGH068 ( SID=203 ) is blocking PMDBA@WAI\PGH068 ( SID=301 )
                                • 13. Re: Identifying a deadlock with completely unrelated tables
                                  sb92075
                                  Pierre wrote:
                                  pmdba@gemprod > select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking '
                                  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
                                  2 from v$lock l1, v$session s1, v$lock l2, v$session s2
                                  3 where s1.sid=l1.sid and s2.sid=l2.sid
                                  4 and l1.BLOCK=1 and l2.request > 0
                                  5 and l1.id1 = l2.id1
                                  6 and l2.id2 = l2.id2;

                                  BLOCKING_STATUS
                                  ----------------------------------------------------------------------------------------------------
                                  PMDBA@WAI\PGH068 ( SID=301 ) is blocking PMDBA@WAI\PGH068 ( SID=203 )
                                  PMDBA@WAI\PGH068 ( SID=203 ) is blocking PMDBA@WAI\PGH068 ( SID=301 )
                                  >
                                  pmdba@gemprod > select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking '
                                  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
                                  2 from v$lock l1, v$session s1, v$lock l2, v$session s2
                                  3 where s1.sid=l1.sid and s2.sid=l2.sid
                                  4 and l1.BLOCK=1 and l2.request > 0
                                  5 and l1.id1 = l2.id1
                                  6 and l2.id2 = l2.id2;

                                  BLOCKING_STATUS
                                  ----------------------------------------------------------------------------------------------------
                                  PMDBA@WAI\PGH068 ( SID=301 ) is blocking PMDBA@WAI\PGH068 ( SID=203 )
                                  PMDBA@WAI\PGH068 ( SID=203 ) is blocking PMDBA@WAI\PGH068 ( SID=301 )
                                  above is NOT a DEADLOCK

                                  not every LOCK is a DEADLOCK.

                                  Deadlocks get recorded into alert_SID.log file
                                  post the ORA-00060 error from your alert logfile.
                                  [oracle@localhost ~]$ oerr ora 60
                                  00060, 00000, "deadlock detected while waiting for resource"
                                  // *Cause:  Transactions deadlocked one another while waiting for resources.
                                  // *Action: Look at the trace file to see the transactions and resources
                                  //          involved. Retry if necessary.
                                  • 14. Re: Identifying a deadlock with completely unrelated tables
                                    Michael-O
                                    P. Forstmann wrote:
                                    Try to look for other places in code where same tables are updated but in a different order. A frequent deadlock cause is to lock same rows from different tables but not in the same order.
                                    Obviously I can look only for those statements which run in parallel, right?

                                    Edited by: Michael-O on 19.02.2013 01:35
                                    1 2 Previous Next