12 Replies Latest reply: Aug 6, 2008 4:24 AM by 586516 RSS

    Deadlock ( holds: S, waits: SSX )

    586516
      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
          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
            Yes, but I don't find the reason of this deadlock.
            • 3. Re: Deadlock ( holds: S, waits: SSX )
              Charles Hooper
              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 )
                Pavan Kumar
                Hi,

                Nice Demo.. !!!

                Thanks
                Pavan Kumar N
                • 5. Re: Deadlock ( holds: S, waits: SSX )
                  586516
                  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 )
                    Charles Hooper
                    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
                      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 )
                        Charles Hooper
                        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 )
                          riyaj
                           

                          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
                            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
                              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
                                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.