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

ANAND@MATRIX> select * from sa;

         A          B
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5

Elapsed: 00:00:00.03
ANAND@MATRIX>
ANAND@MATRIX> delete from sa where A=5;

1 row deleted.

Elapsed: 00:00:00.00
ANAND@MATRIX>

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

ANAND@MATRIX> delete from sa where a=5;
-

Checking from sys session (session # 3)

Session 3
============

22:22:43 SYS@MATRIX> 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 SYS@MATRIX> @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 SYS@MATRIX> @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 SYS@MATRIX> @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 SYS@MATRIX>@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 ANAND@MATRIX> commit;

Commit complete.

Elapsed: 00:00:00.01

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

22:22:52 ANAND@MATRIX> delete from sa where a=5;

0 rows deleted.

Elapsed: 00:05:07.70
22:28:02 ANAND@MATRIX>

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 ANAND@MATRIX> alter table sa add constraint pk_sa primary key (B);

Table altered.

Elapsed: 00:00:00.29
22:31:31 ANAND@MATRIX>
22:31:32 ANAND@MATRIX> desc sa
 Name                                                                                            Null?    Type
 ----------------------------------------------------------------------------------------------- -------- -------
 A                                                                                                     NUMBER
 B                                                                                               NOT NULL NUMBER

22:31:34 ANAND@MATRIX>
22:31:35 ANAND@MATRIX> select * from sa;

         A          B
---------- ----------
         1          1
         2          2
         3          3
         4          4

Elapsed: 00:00:00.04
22:31:44 ANAND@MATRIX> 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 ANAND@MATRIX> insert into sa values (5,5);
-

The session hangs.Check the session waits and enqueues.

Session 3
==============

22:34:54 SYS@MATRIX> @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 SYS@MATRIX> @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 SYS@MATRIX> @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 SYS@MATRIX> @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 ANAND@MATRIX> 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 ANAND@MATRIX>

b.) Foreign key

Session 1
===========

23:50:12 ANAND@MATRIX> create table sa_child (id number references sa(B) ,name varchar2(10));

Table created.

Elapsed: 00:00:00.25
23:50:47 ANAND@MATRIX> 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 ANAND@MATRIX> 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 ANAND@MATRIX> insert into sa_child values (6,'DANNY');
-

Session 2 hangs.

Session 3
===========

23:53:07 SYS@MATRIX> @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 SYS@MATRIX> @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 SYS@MATRIX> @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 SYS@MATRIX> @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 ANAND@MATRIX> insert into sa_child values (6,'DANNY');

1 row created.

Elapsed: 00:05:02.03

c.) Bitmap Index

Session 1
==========

00:18:18 ANAND@MATRIX> 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 ANAND@MATRIX> create bitmap index sa_c_bit_indx on sa(C);

Index created.

Elapsed: 00:00:00.10
00:18:43 ANAND@MATRIX> 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 ANAND@MATRIX> delete from sa where A=1;
-

Session 2 hangs.

Session 3
===========

00:20:21 SYS@MATRIX> @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 SYS@MATRIX> @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 SYS@MATRIX> @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 SYS@MATRIX> @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 ANAND@MATRIX> delete from sa where a=1;

1 row deleted.

Elapsed: 00:10:00.78
00:29:59 ANAND@MATRIX>

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 ANAND@MATRIX> create table parent_tab as select distinct object_type from all_objects;

Table created.

Elapsed: 00:00:05.82
17:03:48 ANAND@MATRIX> 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 ANAND@MATRIX> alter table child_tab add constraint pk_child_tab primary key (object_id);

Table altered.

Elapsed: 00:00:00.03
17:07:35 ANAND@MATRIX>  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 ANAND@MATRIX> 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 ANAND@MATRIX>
17:10:48 ANAND@MATRIX> delete from parent_tab where object_type = 'SYNONYM';

1 row deleted.

Elapsed: 00:00:00.01

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

17:11:05 ANAND@MATRIX> delete from parent_tab where object_type = 'OPERATOR';
-

The Session 2 hangs.Lets check the waitevents

Session 3
==============

17:11:08 SYS@MATRIX> @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 SYS@MATRIX> @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 SYS@MATRIX> select object_name,object_type from dba_objects where object_id=75723;

OBJECT_NAME                                                                                                              OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- ------
CHILD_TAB                                                                                                                TABLE

17:23:26 SYS@MATRIX> @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 ANAND@MATRIX> desc sa
 Name                                                                                            Null?    Type
 ----------------------------------------------------------------------------------------------- -------- -------
 A                                                                                                     NUMBER
 B                                                                                                     NUMBER
 C                                                                                                     NUMBER

17:50:14 ANAND@MATRIX> select constraint_name,constraint_type from user_constraints where table_name='SA';

no rows selected

Elapsed: 00:00:00.15
17:50:21 ANAND@MATRIX> insert /*+ APPEND */ into sa select * from sa;

11 rows created.

Elapsed: 00:00:00.06

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

17:51:42 ANAND@MATRIX> insert /*+ APPEND */ into sa select * from sa;
-

Session 2 hangs.

Session 3
===============

17:51:53 SYS@MATRIX> @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 SYS@MATRIX> @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 SYS@MATRIX> @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 SYS@MATRIX> 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. Its really a good work and i learnt a lot from this blog, keep up your good work..

      Regards
      Thangaraj, DBA

  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 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s