Oracle

enq: TX row lock contention and enq:TM contention

Recently, on a production system i observed number of sessions with “waiting” state and the event being “enq: TM contention” and the reason for it was something which i didn’t knew earlier.So, i thought of writing on both ‘TX’ and ‘TM’ contention.
Here i will discuss only few which i have observed are the common causes of contention.
enq: TX row lock contention
=============================
The “enq: TX – row lock contention” can happen in both mode 6 or mode 4.The different modes are –

     #    Type         Name
     --- -------   ---------------------------
       1   Null         Null
       2   SS          Sub share
       3   SX          Sub exclusive
       4   S            Share
       5   SSX        Share/sub exclusive
       6   X            Exclusive

Lets check them out –
1. enq” TX row lock contention – Mode 6 (Exclusive)
Session 1
==========

[email protected]> select * from sa;
         A          B
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
Elapsed: 00:00:00.03
[email protected]>
[email protected]> delete from sa where A=5;
1 row deleted.
Elapsed: 00:00:00.00
[email protected]>

From session 2
=====================

[email protected]> delete from sa where a=5;
-

Checking from sys session (session # 3)
Session 3
============

22:22:43 [email protected]> select ADDR,XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,SES_ADDR,XID from v$transaction;
ADDR         XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN SES_ADDR XID
-------- ---------- ---------- ---------- ---------- ---------- ---------- -------- ----------------
218B6D98         10         29        848          3       2661        339 232C8B5C 0A001D0050030000
Elapsed: 00:00:00.01
22:23:10 [email protected]> @lock_sid
Enter value for sid: 135
old  82: where sid = &sid
new  82: where sid = 135
  SID Lock Type                                      ID1       ID2 Lock Held      Lock Request       Time Held STATUS
----- ---------------------------------------- --------- --------- -------------- ---------------- ----------- ----------------------------------------
  135 ????                                         65921         1 Row Exclu(3)   None(0)             5079.000 Not Blocking
  135 DML enqueue                                  75404         0 Row Exclu(3)   None(0)            49.000 Not Blocking
  135 ????                                           100         0 Share(4)       None(0)             5224.000 Not Blocking
  135 Transaction                                 655389       848 Exclusive(6)   None(0)            49.000 Blocking
22:23:59 [email protected]> @sw 135
old  38:     sid IN (&1)
new  38:     sid IN (135)
    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT                 P1         P2                 P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ------------------ ------------------ ------------------ ------------------------------------------
    135 WAITING SQL*Net message from client                     193         131 driver id=         #bytes= 1          0
                                                                                0x0000000042455100
22:24:45 [email protected]> @sw 10
old  38:     sid IN (&1)
new  38:     sid IN (10)
    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT                 P1         P2                 P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ------------------ ------------------ ------------------ ------------------------
     10 WAITING enq: TX - row lock contention                    35         146 name|mode=         usn<
22:27:45 [email protected]>@enqueue
   INST_ID SESS                                                  ID1      ID2 Lock Held Lock Requested Lock Type
---------- ------------------------------------------------ -------- -------- --------- -------------- --------------
         1 Holder: 135                                        655389      848         6              0 TX
         1 Waiter: 10                                         655389      848         0              6 TX

Back to session 1
==================

22:28:00 [email protected]> commit;
Commit complete.
Elapsed: 00:00:00.01

Session 2
===============

22:22:52 [email protected]> delete from sa where a=5;
0 rows deleted.
Elapsed: 00:05:07.70
22:28:02 [email protected]>

2. enq: TX row lock contention – Mode 4
The common cause for mode 4, enq: TX row lock contention are
a. Unique Index
b. Foreign key
c. Bitmap indexes
a. Unique Index
Session 1
============

22:31:29 [email protected]> alter table sa add constraint pk_sa primary key (B);
Table altered.
Elapsed: 00:00:00.29
22:31:31 [email protected]>
22:31:32 [email protected]> desc sa
 Name                                                                                            Null?    Type
 ----------------------------------------------------------------------------------------------- -------- -------
 A                                                                                                     NUMBER
 B                                                                                               NOT NULL NUMBER
22:31:34 [email protected]>
22:31:35 [email protected]> select * from sa;
         A          B
---------- ----------
         1          1
         2          2
         3          3
         4          4
Elapsed: 00:00:00.04
22:31:44 [email protected]> insert into sa values (5,5);
1 row created.
Elapsed: 00:00:00.01

Didn’t commit.Check session 2
Session 2
===============

22:33:04 [email protected]> insert into sa values (5,5);
-

The session hangs.Check the session waits and enqueues.
Session 3
==============

22:34:54 [email protected]> @lock_sid
Enter value for sid: 135
old  82: where sid = &sid
new  82: where sid = 135
  SID Lock Type                                      ID1       ID2 Lock Held      Lock Request       Time Held STATUS
----- ---------------------------------------- --------- --------- -------------- ---------------- ----------- ---------------------
  135 ????                                         65921         1 Row Exclu(3)   None(0)             5779.000 Not Blocking
      DML enqueue                                  75404         0 Row Exclu(3)   None(0)              134.000 Not Blocking
      ????                                           100         0 Share(4)       None(0)             5924.000 Not Blocking
      Transaction                                 393229      1069 Exclusive(6)   None(0)              134.000 Blocking
Elapsed: 00:00:00.03
22:33:15 [email protected]> @enqueue
   INST_ID SESS                                                  ID1      ID2 Lock Held Lock Requested Lock Type
---------- ------------------------------------------------ -------- -------- --------- -------------- ----------------------------------------
         1 Holder: 135                                        393229     1069         6              0 TX
         1 Waiter: 10                                         393229     1069         0              4 TX
Elapsed: 00:00:00.04
22:33:26 [email protected]> @sw 135
old  38:     sid IN (&1)
new  38:     sid IN (135)
    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT                 P1         P2                 P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ------------------ ------------------ ------------------ ------------------------
    135 WAITING SQL*Net message from client                     229          45 driver id=         #bytes= 1          0
                                                                                0x0000000042455100
Elapsed: 00:00:00.01
22:33:32 [email protected]> @sw 10
old  38:     sid IN (&1)
new  38:     sid IN (10)
    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT                 P1         P2                 P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ------------------ ------------------ ------------------ ------------------------
     10 WAITING enq: TX - row lock contention                    44          26 name|mode=         usn<<16 | slot=    sequence= 1069     0x54580004: TX mode 4
                                                                                0x0000000054580004 393229

After commit on session1 , the session 2 throws Unique constraint violated error
Session 2
============

22:33:04 [email protected]> insert into sa values (5,5);
insert into sa values (5,5)
*
ERROR at line 1:
ORA-00001: unique constraint (ANAND.PK_SA) violated
Elapsed: 00:02:18.10
22:35:27 [email protected]>

b.) Foreign key
Session 1
===========

23:50:12 [email protected]> create table sa_child (id number references sa(B) ,name varchar2(10));
Table created.
Elapsed: 00:00:00.25
23:50:47 [email protected]> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME,STATUS from user_constraints where TABLE_NAME in ('SA','SA_CHILD');
CONSTRAINT_NAME                C TABLE_NAME                     R_CONSTRAINT_NAME              STATUS
------------------------------ - ------------------------------ ------------------------------ --------
PK_SA                          P SA                                                            ENABLED
SYS_C0011533                   R SA_CHILD                       PK_SA                          ENABLED
Elapsed: 00:00:00.18
23:52:04 ANAN[email protected]> insert into sa values (6,6);
1 row created.
Elapsed: 00:00:00.03

Lets insert the same value 6 in SA_CHILD from Session 2
Session 2
===========

23:52:19 [email protected]> insert into sa_child values (6,'DANNY');
-

Session 2 hangs.
Session 3
===========

23:53:07 [email protected]> @enqueue
   INST_ID SESS                                                   ID1       ID2 Lock Held Lock Requested Lock Type
---------- ------------------------------------------------ --------- --------- --------- -------------- -----------
         1 Holder: 135                                         655360       884         6              0 TX
         1 Waiter: 10                                          655360       884         0              4 TX
23:53:12 [email protected]> @lock_sid
Enter value for sid: 135
old  82: where sid = &sid
new  82: where sid = 135
  SID Lock Type                                      ID1       ID2 Lock Held      Lock Request       Time Held STATUS
----- ---------------------------------------- --------- --------- -------------- ---------------- ----------- --------------------
  135  DML enqueue                                  75594         0 Row Exclu(3)   None(0)            64.000 Not Blocking
      DML enqueue                                  75404         0 Row Exclu(3)   None(0)            64.000 Not Blocking
      ????                                           100         0 Share(4)       None(0)           183421.000 Not Blocking
      Transaction                                 655360       884 Exclusive(6)   None(0)            64.000 Blocking
6 rows selected.
Elapsed: 00:00:00.09
23:53:09 [email protected]> @sw 10
old  38:     sid IN (&1)
new  38:     sid IN (10)
    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT                 P1         P2                 P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ------------------ ------------------ ------------------ -------------------------------
     10 WAITING enq: TX - row lock contention                    58          27 name|mode=         usn<<16 | slot=    sequence= 884      0x54580004: TX mode 4
                                                                                0x0000000054580004 655360
Elapsed: 00:00:00.03
23:53:11 [email protected]> @sw 135
old  38:     sid IN (&1)
new  38:     sid IN (135)
    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT                 P1         P2                 P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ------------------ ------------------ ------------------ -------------------------------
    135 WAITING SQL*Net message from client                     686          59 driver id=         #bytes= 1          0
                                                                                0x0000000042455100

So, the session 2 is waiting for enq: TX row lock contention as before inserting the row it needs check for the value in the parent table.If the row is not there , then it would throw erroe “ORA-02291: integrity constraint (ANAND.SYS_C0011533) violated – parent key not found”.
After commit on session1 ,
Session 2
===========

23:52:19 [email protected]> insert into sa_child values (6,'DANNY');
1 row created.
Elapsed: 00:05:02.03

c.) Bitmap Index
Session 1
==========

00:18:18 [email protected]> select * from sa;
         A          B          C
---------- ---------- ----------
         1          1         99
         2          2         99
         3          3         99
         4          4         99
         6          6         99
         5          5         99
         7          7         99
7 rows selected.
Elapsed: 00:00:00.06
00:18:21 [email protected]> create bitmap index sa_c_bit_indx on sa(C);
Index created.
Elapsed: 00:00:00.10
00:18:43 [email protected]> insert into sa values (8,8,99);
1 row created.
Elapsed: 00:00:00.03

Lets try deleteing a row from sa table from Session 2
Session 2
=============

00:19:42 [email protected]> delete from sa where A=1;
-

Session 2 hangs.
Session 3
===========

00:20:21 [email protected]> @lock_sid
Enter value for sid: 135
old  82: where sid = &sid
new  82: where sid = 135
  SID Lock Type                                      ID1       ID2 Lock Held      Lock Request       Time Held STATUS
----- ---------------------------------------- --------- --------- -------------- ---------------- ----------- ----------------------------------------
  135 DML enqueue                                  75404         0 Row Exclu(3)   None(0)            87.000 Not Blocking
       DML enqueue                                  75594         0 Row Exclu(3)   None(0)            87.000 Not Blocking
       ????                                           100         0 Share(4)       None(0)           185049.000 Not Blocking
       Transaction                                 589847      1046 Exclusive(6)   None(0)            87.000 Blocking
8 rows selected.
Elapsed: 00:00:00.07
00:20:26 [email protected]> @sw 135
old  38:     sid IN (&1)
new  38:     sid IN (135)
    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT                 P1         P2                 P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ------------------ ------------------ ------------------ -------------------------------------
    135 WAITING SQL*Net message from client                     796          71 driver id=         #bytes= 1          0
                                                                                0x0000000042455100
Elapsed: 00:00:00.03
00:20:33 [email protected]> @sw 10
old  38:     sid IN (&1)
new  38:     sid IN (10)
    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT                 P1         P2                 P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ------------------ ------------------ ------------------ -------------------------------------
     10 WAITING enq: TX - row lock contention                   101          37 name|mode=         usn<<16 | slot=    sequence= 1046     0x54580004: TX mode 4
                                                                                0x0000000054580004 589847
Elapsed: 00:00:00.03
00:20:53 [email protected]> @enqueue
   INST_ID SESS                                                   ID1       ID2 Lock Held Lock Requested Lock Type
---------- ------------------------------------------------ --------- --------- --------- -------------- ----------------------------------------
         1 Holder: 135                                         589847      1046         6              0 TX
         1 Waiter: 10                                          589847      1046         0              4 TX
Elapsed: 00:00:00.04

As soon as session 1, the transaction in session2 completes.
Session 2
=============

00:19:42 [email protected]> delete from sa where a=1;
1 row deleted.
Elapsed: 00:10:00.78
00:29:59 [email protected]>

So, those were the few common causes of enq: TX contention that i have encountered till now.
enq: TM – contention
=======================
Coming on to enq: TM contention, its a table level lock and the locked resource is database objects like table,index, partitions..
The most most common cause of enq:TM contention that i have seen is unindexed foreign keys.But, few days back i came to know about one more which was the main reason behind writing this blog.
1. Unindexed Foreign Keys
Session 1
==========

17:03:09 [email protected]> create table parent_tab as select distinct object_type from all_objects;
Table created.
Elapsed: 00:00:05.82
17:03:48 [email protected]> create table child_tab
17:04:19   2   as
17:04:19   3   select object_id, object_type, object_name
17:04:19   4   from all_objects
17:04:19   5   where rownum   alter table parent_tab add constraint pk_parent_tab primary key (object_type);
Table altered.
Elapsed: 00:00:00.23
17:05:04 [email protected]> alter table child_tab add constraint pk_child_tab primary key (object_id);
Table altered.
Elapsed: 00:00:00.03
17:07:35 [email protected]>  alter table child_tab add constraint fk_child_parent_tab
17:08:36   2   foreign key (object_type) references parent_tab on delete cascade;

Table altered.
Elapsed: 00:00:00.07
17:08:37 [email protected]> select count(*), object_type  from child_tab group by object_type;
  COUNT(*) OBJECT_TYPE
---------- -------------------
         1 EDITION
        39 SEQUENCE
       454 TABLE
       491 INDEX
         5 SYNONYM
        10 CLUSTER
6 rows selected.
Elapsed: 00:00:00.04
17:08:58 [email protected]>
17:10:48 [email protected]> delete from parent_tab where object_type = 'SYNONYM';
1 row deleted.
Elapsed: 00:00:00.01

Session 2
============

17:11:05 [email protected]> delete from parent_tab where object_type = 'OPERATOR';
-

The Session 2 hangs.Lets check the waitevents
Session 3
==============

17:11:08 [email protected]> @locks
SESS                                                   ID1       ID2 Lock Held    REQUEST Lock Type
------------------------------------------------ --------- --------- --------- ---------- -----------
Holder: 134                                          75723         0         3          0 TM
Waiter: 13                                           75723         0         0          5 TM
Elapsed: 00:00:00.04
17:11:22 [email protected]> @sw 13
old  38:     sid IN (&1)
new  38:     sid IN (13)
    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT                 P1         P2                 P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ------------------ ------------------ ------------------ ----------------------
     13 WAITING enq: TM - contention                             37          15 name|mode=         object #= 75723    table/partition= 0 0x544D0005: TM mode 5
                                                                                0x00000000544D0005
Elapsed: 00:00:00.03
17:23:18 [email protected]> select object_name,object_type from dba_objects where object_id=75723;
OBJECT_NAME                                                                                                              OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- ------
CHILD_TAB                                                                                                                TABLE
17:23:26 [email protected]> @lock_sid
Enter value for sid: 134
old  82: where sid = &sid
new  82: where sid = 134
  SID Lock Type                                      ID1       ID2 Lock Held      Lock Request       Time Held STATUS
----- ---------------------------------------- --------- --------- -------------- ---------------- ----------- ------------------------------------
  134 Transaction                                 262171       982 Exclusive(6)   None(0)            25.000 Not Blocking
      DML enqueue                                  75722         0 Row Exclu(3)   None(0)            25.000 Not Blocking
      ????                                           100         0 Share(4)       None(0)            10303.000 Not Blocking
      DML enqueue                                  75723         0 Row Exclu(3)   None(0)            25.000 Blocking

The simple way to resolve is, create an index on the foreign key of child table.Tom Kyte has provided a script to check all the unindexed foreign keys in tables, which you can check out at
http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteunindex.html
2. Direct path load or insert /*+ append */ –> This is somethinh which i didn’t know, unless i saw it on one of my production box.
Session 1
===========

17:36:36 [email protected]> desc sa
 Name                                                                                            Null?    Type
 ----------------------------------------------------------------------------------------------- -------- -------
 A                                                                                                     NUMBER
 B                                                                                                     NUMBER
 C                                                                                                     NUMBER
17:50:14 [email protected]> select constraint_name,constraint_type from user_constraints where table_name='SA';
no rows selected
Elapsed: 00:00:00.15
17:50:21 [email protected]> insert /*+ APPEND */ into sa select * from sa;
11 rows created.
Elapsed: 00:00:00.06

Session 2
===========

17:51:42 [email protected]> insert /*+ APPEND */ into sa select * from sa;
-

Session 2 hangs.
Session 3
===============

17:51:53 [email protected]> @locks
SESS                                                   ID1       ID2 Lock Held    REQUEST Lock Type
------------------------------------------------ --------- --------- --------- ---------- ------------------------------------
Holder: 69                                           75726         0         6          0 TM
Waiter: 13                                           75726         0         0          6 TM
Elapsed: 00:00:00.04
17:51:54 [email protected]> @lock_sid
Enter value for sid: 69
old  82: where sid = &sid
new  82: where sid = 69
  SID Lock Type                                      ID1       ID2 Lock Held      Lock Request       Time Held STATUS
----- ---------------------------------------- --------- --------- -------------- ---------------- ----------- ---------------
   69 Transaction                                 458754       968 Exclusive(6)   None(0)            55.000 Not Blocking
   69 ????                                           100         0 Share(4)       None(0)              930.000 Not Blocking
   69 DML enqueue                                  75726         0 Exclusive(6)   None(0)            55.000 Blocking
Elapsed: 00:00:00.04
17:52:15 [email protected]> @sw 13
old  38:     sid IN (&1)
new  38:     sid IN (13)
    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT                 P1         P2                 P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ------------------ ------------------ ------------------ ----------------------
     13 WAITING enq: TM - contention                             43          35 name|mode=         object #= 75726    table/partition= 0 0x544D0006: TM mode 6
                                                                                0x00000000544D0006
Elapsed: 00:00:00.03
17:56:23 [email protected]> select object_name,object_type from dba_objects where object_id=75726;
OBJECT_NAM OBJECT_TYPE
---------- -------------------
SA         TABLE
Elapsed: 00:00:00.00

So, direct mode insert, or insert /*+ append */ will take TM enqueue in exclusive mode.And if the transaction doesn’t commit, we can see lots of bunch of sessions stack up behind it and that is what happened in my case.
References :-
http://oracle-randolf.blogspot.com/2008/12/concurrency-issues-when-do.html
http://knol.google.com/k/franck-pachot/oracle-table-lock-modes#

4 thoughts on “enq: TX row lock contention and enq:TM contention

  1. Another source of enq: TM – contention can be a lock manually put on the table:
    LOCK TABLE IN SHARE ROW EXCLUSIVE MODE
    I know this sounds dumb but I actually spent a lot of time finding it on a database I audited. I didn’t get why a statement was getting so much TM contention whereas the foreign key on the table was correctly indexed. I looked for direct path / append insert but couldn’t find any either.
    After much search I saw this lock table and secretly hated the guy that coded it πŸ™‚

    1. Its really a good work and i learnt a lot from this blog, keep up your good work..
      Regards
      Thangaraj, DBA

Leave a Reply