This discussion is archived
1 2 Previous Next 19 Replies Latest reply: May 25, 2011 8:46 AM by 408327 RSS

11.2.0.1: Deadlock not detected

408327 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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
    CharlesHooper Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    CharlesHooper Expert
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    CharlesHooper Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points