1 2 Previous Next 19 Replies Latest reply: May 25, 2011 10:58 AM by Dom Brooks RSS

    11.2.0.1: Deadlock not detected

    408327
      Hello,
      We are encoutering a problem where a deadlock seems to not be detected by Oracle.

      SELECT (
      SELECT username
      FROM gv$session
      WHERE sid=a.sid) blocker,
      a.sid, ' is blocking ', (
      SELECT username
      FROM gv$session
      WHERE sid=b.sid) blockee,
      b.sid
      FROM gv$lock a, gv$lock b
      WHERE a.block = 1
      AND b.request > 0
      AND a.id1 = b.id1
      AND a.id2 = b.id2;


      BLOCKER SID 'ISBLOCKING'
      ------------------------------ ---------- --------------------------------
      BLOCKEE SID_1
      ------------------------------ ----------
      CODATA_PROC_ADM 489 is blocking
      CODATA_PROC_ADM 283

      CODATA_PROC_ADM 283 is blocking
      CODATA_PROC_ADM 489



      2 rows selected.

      The two session are inserting records in the same table with a unique index on it. With the same database version on another server and the same treatment, the deadlocks are correctly handled (one session is killed with ora-00060) and the process can continue.
      The only difference noticeable between the two servers are sparc and x86; both are on solaris 10 (x86 is working fine, sparc not)

      My question is : am i missing something that it is not a deadlock or is it a problem on this oracle version (11.2.0.1) ?

      Thanks for your help

      Rgds
        • 1. Re: 11.2.0.1: Deadlock not detected
          John Stegeman
          Your question would be much better handled by [url https://support.oracle.com]Oracle Support.

          John
          • 2. Re: 11.2.0.1: Deadlock not detected
            Jonathan Lewis
            N. Durand wrote:
            Hello,
            We are encoutering a problem where a deadlock seems to not be detected by Oracle.
            SELECT (
            SELECT username
            FROM gv$session
            WHERE sid=a.sid) blocker,
            a.sid, ' is blocking ', (
            SELECT username
            FROM gv$session
            WHERE sid=b.sid) blockee,
            b.sid
            FROM gv$lock a, gv$lock b
            WHERE a.block = 1
            AND b.request > 0
            AND a.id1 = b.id1
            AND a.id2 = b.id2;
            My question is : am i missing something that it is not a deadlock or is it a problem on this oracle version (11.2.0.1) ?
            There are certainly some strange locking changes in 11.2 (see, for example, http://jonathanlewis.wordpress.com/2010/02/15/lock-horror ) but before we can be sure that your output is showing anything strange, you need to correct two errors:

            1) You are using gv$ objects, so should include the inst_id in the join conditions
            2) When joining gv$lock to itself you should also join on the lock type.

            Regards
            Jonathan Lewis
            • 3. Re: 11.2.0.1: Deadlock not detected
              Charles Hooper
              Jonathan Lewis wrote:
              N. Durand wrote:
              Hello,
              We are encoutering a problem where a deadlock seems to not be detected by Oracle.
              SELECT (
              SELECT username
              FROM gv$session
              WHERE sid=a.sid) blocker,
              a.sid, ' is blocking ', (
              SELECT username
              FROM gv$session
              WHERE sid=b.sid) blockee,
              b.sid
              FROM gv$lock a, gv$lock b
              WHERE a.block = 1
              AND b.request > 0
              AND a.id1 = b.id1
              AND a.id2 = b.id2;
              My question is : am i missing something that it is not a deadlock or is it a problem on this oracle version (11.2.0.1) ?
              There are certainly some strange locking changes in 11.2 (see, for example, http://jonathanlewis.wordpress.com/2010/02/15/lock-horror ) but before we can be sure that your output is showing anything strange, you need to correct two errors:

              1) You are using gv$ objects, so should include the inst_id in the join conditions
              2) When joining gv$lock to itself you should also join on the lock type.

              Regards
              Jonathan Lewis
              One more potential problem with the above SQL statement - in the above link provided by Jonathan, Jonathan made the following comment:
              +"in RAC any lock is recorded as “block = 2″ because it might be a blocker of another instance"+

              Your SQL statement specified:
              a.block = 1
              Consider changing that line to:
              a.block > 0
              Charles Hooper
              Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
              http://hoopercharles.wordpress.com/
              IT Manager/Oracle DBA
              K&M Machine-Fabricating, Inc.
              • 4. Re: 11.2.0.1: Deadlock not detected
                408327
                Hello and thanks for your help,

                here the result by the query with the modifications suggested by Jonathan and Charles:

                set lines 200

                SELECT (SELECT username
                FROM gv$session
                WHERE SID = a.SID AND inst_id = a.inst_id) blocker, a.SID,
                ' is blocking ', (SELECT username
                FROM gv$session
                WHERE SID = b.SID AND inst_id = b.inst_id) blockee,
                b.SID,b.type,b.lmode,b.request
                FROM gv$lock a, gv$lock b
                WHERE a.BLOCK > 0
                AND b.request > 0
                AND a.id1 = b.id1
                AND a.id2 = b.id2
                AND a.TYPE = b.TYPE
                AND a.inst_id = b.inst_id;

                BLOCKER SID 'ISBLOCKING' BLOCKEE SID_1 TY LMODE REQUEST
                ------------------------------ ---------- -------------------------------- ------------------------------ ---------- -- ---------- ----------
                CODATA_PROC_ADM 600 is blocking CODATA_PROC_ADM 90 TX 0 4
                CODATA_PROC_ADM 439 is blocking CODATA_PROC_ADM 478 TX 0 4
                CODATA_PROC_ADM 90 is blocking CODATA_PROC_ADM 600 TX 0 4
                CODATA_PROC_ADM 478 is blocking CODATA_PROC_ADM 441 TX 0 4


                We an see that SID 600 and 90 are in an inter-blocking scenario. But, what it is a lock type TX with request mode 4 (Share row)? Is it an attempt to lock for ITL ?

                Rgds

                Edited by: N. Durand on 25 mai 2011 10:28
                • 5. Re: 11.2.0.1: Deadlock not detected
                  Charles Hooper
                  When posting content where the spacing is critical, please use code tags before and after the section of your post where the spacing is critical, for example (do not include the blank space between the curly brackets and the word code - that is only used here so that the keyword prints):
                  { code }
                  SELECT (SELECT username
                            FROM gv$session
                           WHERE SID = a.SID AND inst_id = a.inst_id) blocker, a.SID,
                         ' is blocking ', (SELECT username
                                             FROM gv$session
                                            WHERE SID = b.SID AND inst_id = b.inst_id) blockee,
                         b.SID,b.type,b.lmode,b.request
                    FROM gv$lock a, gv$lock b
                   WHERE a.BLOCK > 0
                     AND b.request > 0
                     AND a.id1 = b.id1
                     AND a.id2 = b.id2
                     AND a.TYPE = b.TYPE
                     AND a.inst_id = b.inst_id;
                  
                  BLOCKER           SID 'ISBLOCKING'  BLOCKEE           SID_1 TY LMODE REQUEST
                  ----------------- --- ------------- ----------------- ----- -- ----- -------
                  CODATA_PROC_ADM   600  is blocking  CODATA_PROC_ADM      90 TX     0       4
                  CODATA_PROC_ADM   439  is blocking  CODATA_PROC_ADM     478 TX     0       4
                  CODATA_PROC_ADM    90  is blocking  CODATA_PROC_ADM     600 TX     0       4
                  CODATA_PROC_ADM   478  is blocking  CODATA_PROC_ADM     441 TX     0       4
                  { code }

                  Here is an interesting problem - what if the blocker is on another RAC instance? Doesn't the following state that the blocking session and the blocked session must be on the same instance?
                  AND a.inst_id = b.inst_id
                  Does the following produce any useful information (this is adapted from a SQL statement found in the Oracle documentation):
                  SELECT
                    S.INST_ID,
                    S.SID,
                    S.USERNAME,
                    S.PROGRAM,
                    S.SQL_ID,
                    S.SQL_CHILD_NUMBER CN,
                    S.EVENT,
                    S.ROW_WAIT_OBJ#,
                    S.ROW_WAIT_FILE#,
                    S.ROW_WAIT_BLOCK#,
                    S.ROW_WAIT_ROW#,
                    L.LMODE,
                    L.REQUEST,
                    L.ID1,
                    L.ID2,
                    L.TYPE,
                    L.BLOCK
                  FROM
                    GV$LOCK L,
                    GV$SESSION S
                  WHERE
                    (L.ID1, L.ID2, L.TYPE) IN
                      (SELECT
                        ID1,
                        ID2,
                        TYPE
                      FROM
                        V$LOCK
                      WHERE
                        REQUEST > 0)
                    AND L.SID=S.SID
                  ORDER BY
                    L.ID1,
                    L.ID2,
                    L.TYPE,
                    L.BLOCK;
                  We might be able to give you a better hint of what the request in mode 4 means based on the reported wait event and the lock mode held by the blocking session.

                  Charles Hooper
                  Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                  http://hoopercharles.wordpress.com/
                  IT Manager/Oracle DBA
                  K&M Machine-Fabricating, Inc.
                  • 6. Re: 11.2.0.1: Deadlock not detected
                    Jonathan Lewis
                    Charles Hooper wrote:

                    Here is an interesting problem - what if the blocker is on another RAC instance? Doesn't the following state that the blocking session and the blocked session must be on the same instance?
                    AND a.inst_id = b.inst_id
                    Correct - I introduced the idea of that join, though, so the OP was only following instruction. Partly it was because there is another problem with RAC which is that ALL locks seem to be "potential blockers" (i.e. block = 2) in RAC, so the first set of useful information is to check for local locks which will have matching inst_ids and block = 1, before checking for global deadlocks (which I think are checked every 60 seconds rather than every three seconds, and get dumped into the lmd trace file.)


                    Regards
                    Jonathan Lewis
                    • 7. Re: 11.2.0.1: Deadlock not detected
                      Jonathan Lewis
                      N. Durand wrote:
                      BLOCKER                               SID 'ISBLOCKING'                     BLOCKEE                                              SID_1  TY      LMODE    REQUEST
                      ------------------------------ ---------- -------------------------------- ------------------------------ ---------- -- ---------- ----------
                      CODATA_PROC_ADM                       600  is blocking                     CODATA_PROC_ADM                           90    TX          0          4
                      CODATA_PROC_ADM                       439  is blocking                     CODATA_PROC_ADM                         478    TX          0          4
                      CODATA_PROC_ADM                         90   is blocking                      CODATA_PROC_ADM                       600    TX          0          4
                      CODATA_PROC_ADM                       478  is blocking                     CODATA_PROC_ADM                         441    TX          0          4
                      We an see that SID 600 and 90 are in an inter-blocking scenario. But, what it is a lock type TX with request mode 4 (Share row)? Is it an attempt to lock for ITL ?
                      There are various events that lead to TX/4. ITL waits are possible, which is why Charles Hooper has suggested a different query. Personally I often do things in steps rather then writing more complex queries (but that's because I'm usually only visiting and have to type things in as I go along). In your case I'd run a quick query against v$session for sids 600 and 90 to check their status, state, event, second_in_wait, p1, p2, p3.

                      TX mode 4 is more commonly a collision on a primary key (or otherwise unique) constraint - such as two processed trying to insert the same key, or one trying to delete the parent of child rows that the other is trying to insert. You need to know if these sessions really are deadlocked or whether it's a variant of the problem in one of the comments in the URL I linked to, viz: the blocked session and the blocking sessions were both set to 1.

                      Quick test: Create a small table with a primary key constraint.
                      Insert the same value from two different sessions
                      Check what they show in v$lock.

                      (Is this a RAC system, by the way, or single instance where you just happen to be querying the gv$ ?)

                      Regards
                      Jonathan Lewis
                      • 8. Re: 11.2.0.1: Deadlock not detected
                        P.Forstmann
                        Jonathan Lewis wrote:
                        which I think are checked every 60 seconds rather than every three seconds, and get dumped into the lmd trace file.)
                        I asked Tom Kyte about this: default in 10.2 is 60 seconds but in 11.2 default is 10 seconds in RAC instances (not tested and not published on AskTom).
                        • 9. Re: 11.2.0.1: Deadlock not detected
                          Jonathan Lewis
                          P. Forstmann wrote:
                          Jonathan Lewis wrote:
                          which I think are checked every 60 seconds rather than every three seconds, and get dumped into the lmd trace file.)
                          I asked Tom Kyte about this: default in 10.2 is 60 seconds but in 11.2 default is 10 seconds in RAC instances (not tested and not published on AskTom).
                          Thanks for that -
                          possibly that's paramenter lmdd_interval which changes from 60 to 10.


                          Regards
                          Jonathan Lewis
                          • 10. Re: 11.2.0.1: Deadlock not detected
                            408327
                            Thanks for your help,

                            We are doing tests, we have recreated the table and unique index increasing the freelists to 20 and initrans to 100.

                            It seems that now the infinite waits have reduced but not disappeared. for precision, we are not in RAC environment (single instance on sparc M5000).

                            So now we can had the time to see that the wait event associated was "TX - Row lock contention":
                            INST_ID                SID                    USERNAME                       PROGRAM                                          SQL_ID        CN                     EVENT                                                            ROW_WAIT_OBJ#          ROW_WAIT_FILE#         ROW_WAIT_BLOCK#        ROW_WAIT_ROW#          LMODE                  REQUEST                ID1                    ID2                    TYPE BLOCK                  
                            ---------------------- ---------------------- ------------------------------ ------------------------------------------------ ------------- ---------------------- ---------------------------------------------------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---- ---------------------- 
                            1                      369                    CODATA_PROC_ADM                JDBC Thin Client                                 fntjfaz9yxh91 0                      enq: TX - row lock contention                                    -1                     0                      0                      0                      0                      4                      262172                 39043                  TX   0                      
                            1                      395                    CODATA_PROC_ADM                JDBC Thin Client                                 fntjfaz9yxh91 0                      enq: TX - row lock contention                                    -1                     0                      0                      0                      6                      0                      262172                 39043                  TX   1                      
                            1                      562                    CODATA_PROC_ADM                JDBC Thin Client                                 fntjfaz9yxh91 0                      enq: TX - row lock contention                                    -1                     0                      0                      0                      0                      4                      1900570                9654                   TX   0                      
                            1                      124                    CODATA_PROC_ADM                JDBC Thin Client                                 fntjfaz9yxh91 0                      enq: TX - row lock contention                                    -1                     0                      0                      0                      6                      0                      1900570                9654                   TX   1                      
                            1                      124                    CODATA_PROC_ADM                JDBC Thin Client                                 fntjfaz9yxh91 0                      enq: TX - row lock contention                                    -1                     0                      0                      0                      0                      4                      1966093                19501                  TX   0                      
                            1                      395                    CODATA_PROC_ADM                JDBC Thin Client                                 fntjfaz9yxh91 0                      enq: TX - row lock contention                                    -1                     0                      0                      0                      0                      4                      1966093                19501                  TX   0                      
                            1                      122                    CODATA_PROC_ADM                JDBC Thin Client                                 fntjfaz9yxh91 0                      enq: TX - row lock contention                                    -1                     0                      0                      0                      0                      4                      1966093                19501                  TX   0                      
                            1                      562                    CODATA_PROC_ADM                JDBC Thin Client                                 fntjfaz9yxh91 0                      enq: TX - row lock contention                                    -1                     0                      0                      0                      6                      0                      1966093                19501                  
                            All the sessions are issuing the same insert statements as you can see in SQL_ID. I'am not able to "decode" this inter-blocking case ....

                            Rgds
                            • 11. Re: 11.2.0.1: Deadlock not detected
                              408327
                              Interesting, now, the sessions which are blocked are receiving an ORA-00060 and for us it is a good thing as we know that we can potentially insert the same unique key as the same moment.

                              I was convinced that deadlock are detected in less than 3 seconds ... one hour later is not acceptable in our case. Are you aware of a modification in oracle 11G or a parameter which can be used to reduce this time ?

                              Rgds
                              • 12. Re: 11.2.0.1: Deadlock not detected
                                Dom Brooks
                                one hour later is not acceptable in our case. Are you aware of a modification in oracle 11G or a parameter which can be used to reduce this time ?
                                Remember what it tells you in the deadlock trace file - deadlocks are an application design issue.

                                Continuing to rely on the deadlock detection mechanism to sort out what seems to be a fundamental application flaw seems ... not terribly sensible.
                                • 13. Re: 11.2.0.1: Deadlock not detected
                                  Charles Hooper
                                  N. Durand wrote:
                                  Thanks for your help,

                                  We are doing tests, we have recreated the table and unique index increasing the freelists to 20 and initrans to 100.

                                  It seems that now the infinite waits have reduced but not disappeared. for precision, we are not in RAC environment (single instance on sparc M5000).

                                  So now we can had the time to see that the wait event associated was "TX - Row lock contention":
                                  (snip)
                                  All the sessions are issuing the same insert statements as you can see in SQL_ID. I'am not able to "decode" this inter-blocking case ....

                                  Rgds
                                  I think that it is important to point out that the sessions last issued the same SQL statement before becoming involved in an enqueue - they may have previously executed another SQL statement, the uncommitted result of which is blocking another session.

                                  Here is what your output indicates:
                                  562 (holding mode 6) is blocking 122 (requesting mode 4) enq: TX - row lock contention
                                  562 (holding mode 6) is blocking 124 (requesting mode 4) enq: TX - row lock contention
                                  562 (holding mode 6) is blocking 395 (requesting mode 4) enq: TX - row lock contention
                                  
                                  395 (holding mode 6) is blocking 369 (requesting mode 4) enq: TX - row lock contention
                                  124 (holding mode 6) is blocking 562 (requesting mode 4) enq: TX - row lock contention
                                  The fact that the wait is "enq: TX - row lock contention" tells us that adding additional ITL slots likely had no effect. The ROW_WAIT_OBJ# at -1 while the lock holder has a mode 6 lock and the requester is asking for a mode 4 lock seems to be consistent with what you would expect to see if there is a primary key index collision (two sessions trying to insert the same primary key value). When you see cases like that, the P1, P2, and P3 values from V$SESSION (or V$SESSION_WAIT) might tell you exactly which primary key index is the source of the problem (and thus the collision columns).

                                  Jumping to the documentation (you will see the original version of the SQL statement I provided here):
                                  http://download.oracle.com/docs/cd/E18283_01/server.112/e16638/instance_tune.htm#sthref752
                                  "•Waits for TX in mode 4 can also occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
                                  The solution is to have the first session holding the lock perform a COMMIT or ROLLBACK.

                                  •Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of rowids. Each entry in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

                                  •Waits for TX in Mode 4 can also occur waiting for a PREPARED transaction."

                                  Take a look at the SQL statement that is identified by SQL_ID fntjfaz9yxh91 (query V$SQL or use DBMS_XPLAN.DISPLAY_CURSOR) - what is that SQL statement requesting? Do you have any bitmap indexes on the affected tables (or child tables if foreign keys are defined for the table)?

                                  Charles Hooper
                                  Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                                  http://hoopercharles.wordpress.com/
                                  IT Manager/Oracle DBA
                                  K&M Machine-Fabricating, Inc.
                                  • 14. Re: 11.2.0.1: Deadlock not detected
                                    408327
                                    The treatment used is:

                                    Batched JDBC (with prepared statement) insert into the table with the unique key constraint.

                                    So this is a normal behaviour to see some row lock contention and sometimes to have two sessions inserting the same keys which can produce a deadlock.
                                    Our application is catching the deadlock detected errors and these rows will be represented after.

                                    This treatment is used on anther system and is working as expected; the problem here is that why the deadlocks are not detected quickly :
                                    if i am right, in the above example SID 562 and 124 are in a deadlock scenario and oracle should raise an ORA-00060 in less than 3 seconds ?


                                    562 (holding mode 6) is blocking 122 (requesting mode 4) enq: TX - row lock contention
                                    *562 (holding mode 6) is blocking 124 (requesting mode 4) enq: TX - row lock contention*
                                    562 (holding mode 6) is blocking 395 (requesting mode 4) enq: TX - row lock contention
                                     
                                    395 (holding mode 6) is blocking 369 (requesting mode 4) enq: TX - row lock contention
                                    *124 (holding mode 6) is blocking 562 (requesting mode 4) enq: TX - row lock contention*
                                    1 2 Previous Next