This discussion is archived
12 Replies Latest reply: Aug 6, 2008 2:24 AM by 586516 RSS

Deadlock ( holds: S, waits: SSX )

586516 Newbie
Currently Being Moderated
Hello,

I have a deadlock problem on a database.

Here is a summary of dump trace:

Dump file /home/migtt2/appli/expl/udump/migtt2_ora_1024024.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
System name:     AIX
Release:     3
Version:     5
Instance name: MIGTT2

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TM-0003ad7d-00000000 24 568 S SSX 26 577 S SSX
TM-0003ad7d-00000000 26 577 S SSX 24 568 S SSX

3ad7d identifies a table T1.

update T1 set .. where MSGOID=:13 and MSGINSTVERSION=:14
update T1 set .. where MSGOID=:13 and MSGINSTVERSION=:14

After reading on Metalink and studying concepts of lock in Oracle documentation, I have no explanation about this deadlock.

Could you help me.

Thanks.

Stéphane.
  • 1. Re: Deadlock ( holds: S, waits: SSX )
    153119 Pro
    Currently Being Moderated
    After reading on Metalink and studying concepts of lock in Oracle documentation, I have no explanation about this deadlock.

    Really?
    Please try to read the trace file carefully and note session 568 is waiting for session 577 and session 577 is waiting for session 568.
    This is the exact definition of deadlock!

    --
    Sybrand Bakker
    Senior Oracle DBA
  • 2. Re: Deadlock ( holds: S, waits: SSX )
    586516 Newbie
    Currently Being Moderated
    Yes, but I don't find the reason of this deadlock.
  • 3. Re: Deadlock ( holds: S, waits: SSX )
    CharlesHooper Expert
    Currently Being Moderated
    Yes, but I don't find the reason of this deadlock.
    A small demo to potentially help you see the source of the problem. In the demo, the sessions are holding SX (Row exclusive - write access to a compound resource) locks due to a select for update, rather than S (Share - One of more sessions are reading) locks, but with a bit more experimentation, I am sure that it would be possible to create a demo where both the sessions are holding S locks.
    The demo:
    /* First, the creation of the tables with a foreign key index */
    CREATE TABLE T1(C1 NUMBER(10) PRIMARY KEY);

    INSERT INTO T1 VALUES(1);
    INSERT INTO T1 VALUES(2);
    INSERT INTO T1 VALUES(3);
    INSERT INTO T1 VALUES(4);

    COMMIT;

    CREATE TABLE T2(
      C1 NUMBER(10) PRIMARY KEY,
      C2 NUMBER(10),
      CONSTRAINT FK_T1_C1 FOREIGN KEY(C2) REFERENCES T1(C1) ENABLE);

    CREATE INDEX IND_T2_C2 ON T2(C2);

    INSERT INTO T2 VALUES (1,1);
    INSERT INTO T2 VALUES (2,2);
    INSERT INTO T2 VALUES (3,3);
    INSERT INTO T2 VALUES (4,4);
     
    COMMIT;


    /* Test 1 - No problems */
    /* SESSION 1 */
    SELECT
      *
    FROM
      T2
    WHERE
      C2=1
    FOR UPDATE;

            C1         C2
    ---------- ----------
             1          1


    /* SESSION 2 */
    SELECT
      *
    FROM
      T2
    WHERE
      C2=2
    FOR UPDATE;

            C1         C2
    ---------- ----------
             2          2

    UPDATE
      T1
    SET
      C1=2
    WHERE
      C1=2;

    1 row updated.


    /* SESSION 1 */
    UPDATE
      T1
    SET
      C1=1
    WHERE
      C1=1;

    1 row updated.

    ROLLBACK;


    /* SESSION 2 */
    ROLLBACK;


    /* Test 2 - What happens when we repeat the test without the foreign key index */
    DROP INDEX IND_T2_C2;


    /* SESSION 2 */
    SELECT
      *
    FROM
      T2
    WHERE
      C2=2
    FOR UPDATE;

            C1         C2
    ---------- ----------
             2          2


    /* SESSION 1 */
    SELECT
      *
    FROM
      T2
    WHERE
      C2=1
    FOR UPDATE;

            C1         C2
    ---------- ----------
             1          1

    UPDATE
      T1
    SET
      C1=1
    WHERE
      C1=1;
    /* HANGS */


    /* SESSION 2 */
    UPDATE
      T1
    SET
      C1=2
    WHERE
      C1=2;
    /* HANGS */


    /* SESSION 1 */
      T1
      *
    ERROR at line 2:
    ORA-00060: deadlock detected while waiting for resource


    Deadlock graph:
                           ---------Blocker(s)--------  ---------Waiter(s)---------
    Resource Name          process session holds waits  process session holds waits
    TM-0000d63f-00000000        17     208    SX   SSX       18     210    SX   SSX
    TM-0000d63f-00000000        18     210    SX   SSX       17     208    SX   SSX
    session 208: DID 0001-0011-000000CE     session 210: DID 0001-0012-000000D7
    session 210: DID 0001-0012-000000D7     session 208: DID 0001-0011-000000CE
    Rows waited on:
    Session 210: obj - rowid = 000027EB - AAACfrAABAAAG/BAAA
      (dictionary objn - 10219, file - 1, block - 28609, slot - 0)
    Your deadlock graph showed that both sessions were trying to obtain a TM lock in mode 5 (SSX), but the other sessions were already holding locks in mode 4 (S), and neither session was able to continue.

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 4. Re: Deadlock ( holds: S, waits: SSX )
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    Nice Demo.. !!!

    Thanks
    Pavan Kumar N
  • 5. Re: Deadlock ( holds: S, waits: SSX )
    586516 Newbie
    Currently Being Moderated
    Great example, mister Hooper. Thanks.

    In fact, on this database, I also have this kind of deadlock ( holds: SX, waits: SSX ). And it's always on the same table T1. The deadlock looks like your
    example. But I have a foreign key.

    I've already verified the possible problem due to unindexed foreign keys.

    Moreover, the same code ( Java, Hibernate ) performs very well on the other
    "same" databases, particularly in production for 6 months.

    Unfortunately, I can't see the code and correct it.

    Before saying to development team to attempt to correct it, I want to be sure
    it's really a lock management problem and not an database problem.

    It's why I ask you to confirm me it's a code problem and not a database bug.

    Stéphane.
  • 6. Re: Deadlock ( holds: S, waits: SSX )
    CharlesHooper Expert
    Currently Being Moderated
    Great example, mister Hooper. Thanks.

    In fact, on this database, I also have this kind of
    deadlock ( holds: SX, waits: SSX ). And it's always
    on the same table T1. The deadlock looks like your
    example. But I have a foreign key.

    I've already verified the possible problem due to
    unindexed foreign keys.

    Moreover, the same code ( Java, Hibernate ) performs
    very well on the other
    "same" databases, particularly in production for 6
    months.

    Unfortunately, I can't see the code and correct it.

    Before saying to development team to attempt to
    correct it, I want to be sure
    it's really a lock management problem and not an
    database problem.

    It's why I ask you to confirm me it's a code problem
    and not a database bug.
    You mentioned that there are foreign keys. It may not be easy to determine if there are foreign keys that have not been indexed. You might try the following query, which I adapted from a query that I believe Tim Kyte listed in one of his books:
    SELECT
      DECODE(B.TABLE_NAME, NULL, '*Check*', 'OK' ) STATUS,
      A.OWNER,
      A.TABLE_NAME,
      A.COLUMNS,
      B.COLUMNS INDEX_COLUMNS
    FROM
      (SELECT
        A.OWNER,
        SUBSTR(A.TABLE_NAME,1,30) TABLE_NAME,
        SUBSTR(A.CONSTRAINT_NAME,1,30) CONSTRAINT_NAME,
        MAX(DECODE(POSITION, 1,     SUBSTR(COLUMN_NAME,1,30),NULL)) ||
        MAX(DECODE(POSITION, 2,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
        MAX(DECODE(POSITION, 3,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
        MAX(DECODE(POSITION, 4,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
        MAX(DECODE(POSITION, 5,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
        MAX(DECODE(POSITION, 6,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
        MAX(DECODE(POSITION, 7,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
        MAX(DECODE(POSITION, 8,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
        MAX(DECODE(POSITION, 9,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
        MAX(DECODE(POSITION,10,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
        MAX(DECODE(POSITION,11,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
        MAX(DECODE(POSITION,12,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
        MAX(DECODE(POSITION,13,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
        MAX(DECODE(POSITION,14,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
        MAX(DECODE(POSITION,15,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) ||
        MAX(DECODE(POSITION,16,', '||SUBSTR(COLUMN_NAME,1,30),NULL)) COLUMNS
      FROM
        DBA_CONS_COLUMNS A,
        DBA_CONSTRAINTS B
      WHERE
        A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
        AND A.OWNER=B.OWNER
        AND B.CONSTRAINT_TYPE = 'R'
      GROUP BY
        A.OWNER,
        SUBSTR(A.TABLE_NAME,1,30),
        SUBSTR(A.CONSTRAINT_NAME,1,30) ) A,
        (SELECT
          TABLE_OWNER,
          SUBSTR(TABLE_NAME,1,30) TABLE_NAME,
          SUBSTR(INDEX_NAME,1,30) INDEX_NAME,
          MAX(DECODE(COLUMN_POSITION, 1,
          SUBSTR(COLUMN_NAME,1,30),NULL)) ||
          MAX(DECODE(COLUMN_POSITION, 2,', '||
          SUBSTR(COLUMN_NAME,1,30),NULL)) ||
          MAX(DECODE(COLUMN_POSITION, 3,', '||
          SUBSTR(COLUMN_NAME,1,30),NULL)) ||
          MAX(DECODE(COLUMN_POSITION, 4,', '||
          SUBSTR(COLUMN_NAME,1,30),NULL)) ||
          MAX(DECODE(COLUMN_POSITION, 5,', '||
          SUBSTR(COLUMN_NAME,1,30),NULL)) ||
          MAX(DECODE(COLUMN_POSITION, 6,', '||
          SUBSTR(COLUMN_NAME,1,30),NULL)) ||
          MAX(DECODE(COLUMN_POSITION, 7,', '||
          SUBSTR(COLUMN_NAME,1,30),NULL)) ||
          MAX(DECODE(COLUMN_POSITION, 8,', '||
          SUBSTR(COLUMN_NAME,1,30),NULL)) ||
          MAX(DECODE(COLUMN_POSITION, 9,', '||
          SUBSTR(COLUMN_NAME,1,30),NULL)) ||
          MAX(DECODE(COLUMN_POSITION,10,', '||
          SUBSTR(COLUMN_NAME,1,30),NULL)) ||
          MAX(DECODE(COLUMN_POSITION,11,', '||
          SUBSTR(COLUMN_NAME,1,30),NULL)) ||
          MAX(DECODE(COLUMN_POSITION,12,', '||
          SUBSTR(COLUMN_NAME,1,30),NULL)) ||
          MAX(DECODE(COLUMN_POSITION,13,', '||
          SUBSTR(COLUMN_NAME,1,30),NULL)) ||
          MAX(DECODE(COLUMN_POSITION,14,', '||
          SUBSTR(COLUMN_NAME,1,30),NULL)) ||
          MAX(DECODE(COLUMN_POSITION,15,', '||
          SUBSTR(COLUMN_NAME,1,30),NULL)) ||
          MAX(DECODE(COLUMN_POSITION,16,', '||
          SUBSTR(COLUMN_NAME,1,30),NULL)) COLUMNS
        FROM
          DBA_IND_COLUMNS
        GROUP BY
          TABLE_OWNER,
          SUBSTR(TABLE_NAME,1,30),
          SUBSTR(INDEX_NAME,1,30)) B
        WHERE
          A.TABLE_NAME = B.TABLE_NAME (+)
          AND A.OWNER=B.TABLE_OWNER(+)
          AND B.COLUMNS (+) LIKE A.COLUMNS || '%'
    ORDER BY
      A.OWNER,
      A.TABLE_NAME;
    In the output from the above, if STATUS is Check, you might want to investigate that foreign key. You might also want to restrict the query to objects in a specific schema.

    Note: your original deadlock showed that the lock type was TM. If the deadlock points to a type TX, then the problem is probably not related to foreign keys indexing problems.

    Another test setup to produce a deadlock with lock type TX:
    /* The setup (assumes that the tables from the previous test setup still exist) */
    DROP TABLE T2;
    DROP TABLE T1;
    DROP TABLE T3;

    CREATE TABLE T1(C1 NUMBER(10) PRIMARY KEY, C2 NUMBER(10));

    INSERT INTO T1 VALUES(1,NULL);
    INSERT INTO T1 VALUES(2,NULL);
    INSERT INTO T1 VALUES(3,NULL);
    INSERT INTO T1 VALUES(4,NULL);

    COMMIT;

    CREATE TABLE T2(C1 NUMBER(10) PRIMARY KEY, C2 NUMBER(10));

    INSERT INTO T2 VALUES(1,NULL);
    INSERT INTO T2 VALUES(2,NULL);
    INSERT INTO T2 VALUES(3,NULL);
    INSERT INTO T2 VALUES(4,NULL);

    COMMIT;

    CREATE TABLE T3(TRANSACTION_ID NUMBER(10) PRIMARY KEY);

    INSERT INTO T3 VALUES(1);
    INSERT INTO T3 VALUES(2);
    INSERT INTO T3 VALUES(3);
    INSERT INTO T3 VALUES(4);

    COMMIT;

    /* Now we have two data tables and a third table, which could be interesting if there were 3 sessions involved */
    /* Test 3 - session 1 updates a row, waits, session 2 updates 2 rows and hangs */
    /* SESSION 1 */
    UPDATE
      T1
    SET
      C2=C1
    WHERE
      C1=1;


    /* SESSION 2 */
    UPDATE
      T2
    SET
      C2=C1
    WHERE
      C1=1;

    UPDATE
      T1
    SET
      C2=C1
    WHERE
      C1=1;
    /* HANGS */


    /* SESSION 1 */
    UPDATE
      T2
    SET
      C2=C1
    WHERE
      C1=1;
    /* HANGS */


    /* SESSION 2 */
      T1
      *
    ERROR at line 2:
    ORA-00060: deadlock detected while waiting for resource


    Deadlock graph:
                           ---------Blocker(s)--------  ---------Waiter(s)---------
    Resource Name          process session holds waits  process session holds waits
    TX-00010003-0000238f        18     208     X             17     210           X
    TX-00090005-00002383        17     210     X             18     208           X
    session 208: DID 0001-0012-000000D9     session 210: DID 0001-0011-000000D0
    session 210: DID 0001-0011-000000D0     session 208: DID 0001-0012-000000D9
    Rows waited on:
    Session 210: obj - rowid = 0000D644 - AAANZEAAEAAFvr2AAA
      (dictionary objn - 54852, file - 4, block - 1506038, slot - 0)
    Session 208: obj - rowid = 0000D642 - AAANZCAAEAAFvrmAAA
      (dictionary objn - 54850, file - 4, block - 1506022, slot - 0)


    /* SESSION 2 */
    ROLLBACK;


    /* SESSION 1 */
    ROLLBACK;


    /* Test 4 - Transaction table that does not use a sequence contributes to the problem */
    /* SESSION 1 */
    UPDATE
      T1
    SET
      C2=C1
    WHERE
      C1=1;

    INSERT INTO
      T3
    SELECT
      MAX(TRANSACTION_ID)+1
    FROM
      T3;


    /* SESSION 2 */
    UPDATE
      T2
    SET
      C2=C1+1
    WHERE
      C1=1;

    INSERT INTO
      T3
    SELECT
      MAX(TRANSACTION_ID)+1
    FROM
      T3;
    /* HANGS */


    /* SESSION 1 */
    DELETE FROM T2;
    /* HANGS */


    /* SESSION 2 */
      T3
      *
    ERROR at line 2:
    ORA-00060: deadlock detected while waiting for resource


    Deadlock graph:
                           ---------Blocker(s)--------  ---------Waiter(s)---------
    Resource Name          process session holds waits  process session holds waits
    TX-00060029-00002376        18     208     X             17     210           X
    TX-00090016-00002386        17     210     X             18     208           S
    session 208: DID 0001-0012-000000D9     session 210: DID 0001-0011-000000D0
    session 210: DID 0001-0011-000000D0     session 208: DID 0001-0012-000000D9
    Rows waited on:
    Session 210: obj - rowid = 0000D644 - AAANZEAAEAAFvr2AAA
      (dictionary objn - 54852, file - 4, block - 1506038, slot - 0)
    Session 208: obj - rowid = 0000D642 - AAANZCAAEAAFvrmAAA
      (dictionary objn - 54850, file - 4, block - 1506022, slot - 0)
    Test 5 (if setup) might have used 3 sessions, the first session would update a row in T1 and then insert a row into T3. The second session would update a row in T2 and attempt to insert a row into T3 (session 2 would hang). Session 3 would update a different row in T2, and then attempt to insert a row into T3 (session 3 would hang). Session 1 would see that the row originally updated by session 2 was not updated, and attempt to update that row (session 1 would hang). The Deadlock graph?

    Deadlocks almost always point to application design problems (or user design problems when the user attempts to start the same batch process twice).

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 7. Re:  Deadlock ( holds: S, waits: SSX )
    586516 Newbie
    Currently Being Moderated
    I have executed your request: STATUS is always OK.
    I usually use a request written by Steve Adams.

    The kind of lock is always TM.

    Here is an other dump trace:
    ---------Blocker(s)-------- ---------Waiter(s)---------
    Resource Name process session holds waits process session holds waits
    TM-0003ad7d-00000000 25 571 SX SSX 24 565 SX SSX
    TM-0003ad7d-00000000 24 565 SX SSX 25 571 SX SSX

    insert into T2 values ...
    insert into T2 values ...

    According to me, it isn't interesting, but I give you foreign keys about these tables:
    - foreign key 1: T3.C1 references T1.C1
    - foreign key 2: T3.C2 references T2.C2
    Deadlocks almost always point to application design problems (or user design >> problems when the user attempts to start the same batch process twice).
    I agree with you, but I have a doubt for this case.

    Stéphane.
  • 8. Re:  Deadlock ( holds: S, waits: SSX )
    CharlesHooper Expert
    Currently Being Moderated
    One thing that you do not know is what the two sessions were doing before they executed the insert into T2 statement. The first set of numbers in the deadlock graph is the hex value of an OBJECT_ID. If you execute the following query:
    SELECT
      OWNER,
      OBJECT_NAME
    FROM
      DBA_OBJECTS
    WHERE
      OBJECT_ID=241021;
    Does it show the table T2, T1, T3, or some other table?

    You might find the following Metalink documents helpful:
    Note:15476.1 FAQ about Detecting and Resolving Locking Conflicts
    Note:198828.1 Analysing locking issues with LOGMINER
    Note:102925.1 Tracing sessions: waiting on an enqueue

    Have you checked the status of the indexes in the database?

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 9. Re:  Deadlock ( holds: S, waits: SSX )
    651183 Oracle ACE Director
    Currently Being Moderated
     

    Hello
    Can you give me table name from this query ?
       select owner, object_name, object_type from dba_objects
       where object_id=241021 or data_object_id=241021;

    If result of above query is T1, is there any foreign key constraint between T1 and T2? 

    Is it possible for you to create a small test case, so that I can test and understand why?

     
    Cheers
    Riyaj
    blog : http://orainternals.wordpress.com
  • 10. Re:  Deadlock ( holds: S, waits: SSX )
    586516 Newbie
    Currently Being Moderated
    Hello mister Hooper,

    I have a lot of work today, so I only answer you now.
    One thing that you do not know is what the two sessions were doing before they executed the insert into T2 statement.
    Yes. It's why I've decided to ask developer team to trace more their multi-threaded code.
    I will give you more information after the end of these tests.

    The resource name is always TM-0003ad7d and 3ad7d matches up to T1.

    I will study next week your Metaling notes. I believe I already have read the first. The
    other notes seems to be interesting. Thanks.

    Yes, I have checked the status of indexes: enabled.

    Stéphane.
  • 11. Re:  Deadlock ( holds: S, waits: SSX )
    586516 Newbie
    Currently Being Moderated
    Hello mister Shamsudeen,
    If result of above query is T1, is there any foreign key constraint between T1 and T2?
    No.
    Is it possible for you to create a small test case, so that I can test and understand why?
    Perhaps. I will see this next week.

    Stéphane.
  • 12. Re:  Deadlock ( holds: S, waits: SSX )
    586516 Newbie
    Currently Being Moderated
    Hello,

    Developer team traced their multi-threaded code. The result was interesting, because they had succeeded in extracting SQL requests. But there were too much requests.

    So, I wrote small code to complete analysis. Here is the code:

    create or replace trigger dml_T1
    before insert
    or update
    or delete
    on T1
    begin
    steph_trace_trigger('Appel du trigger dml_T1');

    end;

    create or replace procedure STEPH_TRACE_TRIGGER (chaine varchar2)
    is
    pragma autonomous_transaction;

    cpt number;
    begin
    insert into steph_log_trigger
    (horodatage, commentaire)
    values
    (systimestamp, chaine);

    insert into steph_req_sql
    (horodatage, sid, serial, sql_id, sql_text)
    select systimestamp, t1.sid, t1.serial#, t1.sql_id, t2.sql_text
    from v$session t1, v$sql t2
    where t1.status = 'ACTIVE'
    and t1.sql_id is not null
    and t1.sql_id = t2.sql_id;

    select steph_seq_lock.nextval into cpt from dual;

    insert into steph_lock_snapshot
    ( snap_id ,
    object_name ,
    session_id ,
    oracle_username ,
    os_username ,
    sql_actions ,
    lock_mode )
    SELECT cpt,
    DO.object_name,
    lo.SESSION_ID,
    lo.oracle_username,
    lo.OS_USER_NAME,
    DECODE(locked_mode,
    1, 'SELECT',
    2, 'SELECT FOR UPDATE / LOCK ROW SHARE',
    3, 'INSERT/UPDATE/DELETE/LOCK ROW EXCLUSIVE',
    4, 'CREATE INDEX/LOCK SHARE',
    5, 'LOCK SHARE ROW EXCLUSIVE',
    6, 'ALTER TABLE/DROP TABLE/DROP INDEX/TRUNCATE TABLE/LOCK EXCLUSIVE') sql_actions,
    DECODE(locked_mode, 1, 'NULL',
    2, 'SS - SUB SHARE',
    3, 'SX - SUB EXCLUSIVE',
    4, 'S - SHARE',
    5, 'SSX - SHARE/SUB EXCLUSIVE',
    6, 'X - EXCLUSIVE') Lock_mode
    FROM sys.V_$LOCKED_OBJECT lo, sys.dba_objects DO
    WHERE DO.object_id = lo.object_id;

    commit;

    end STEPH_TRACE_TRIGGER;

    Then, I launched this request: update T1 set .. where ...

    I had also seen an enqueue TM-S on two tables: T2 and T4.

    And I had found the reason of this deadlock: there was a bitmap join index between T1, T2 and T4.

    Thanks for your help.

    Stéphane.

    PS: if you like to study the optimizer, the book of mister Antognini has brought out since July.