Oracle enq: TX contention 和 enq: TM contention 等待事件说明

和Oracle性能优化密切相关的一些知识参考如下Blog：

Oracle 常见的33个等待事件

http://blog.csdn.net/tianlesoftware/archive/2010/08/12/5807800.aspx

锁 死锁 阻塞 Latch 等待 详解

http://blog.csdn.net/tianlesoftware/archive/2010/08/20/5822674.aspx

Oracle Latch 说明

http://blog.csdn.net/tianlesoftware/archive/2010/01/27/5263238.aspx

在网上搜到一篇介绍enq: TX 和 enq:TM的文章。原文地址如下：

http://aprakash.wordpress.com/2011/01/17/enq-tx-row-lock-contention-and-enqtm-contention/

Enqueue 这个词其实是lock 的另一种描述语。当我们在AWR 报告中发现长时间的enqueue 等待事件时，说明数据库中出现了阻塞和等待，可以关联AWR报告中的enqueue activity部分来确定是哪一种锁定出现了长时间等待。

关于Enqueue的所有类型参考《Oracle 常见的33个等待事件》中的Equeue说明。 在这里，我们看一下enq:TX 和 enq：TM

一. enq: TX row lock contention

enq:Tx 会在模式6和4 下出现。

Oracle 的enqueue 包含以下模式：

模式代码	类型	解释
1	Null	Null mode
2	SS	Sub-Share
3	SX	Sub-Exclusive
4	S	Share
5	SSX	Share/Sub-Exclusive
6	X	Exclusive

1.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> delete from sa where A=5;

1 row deleted.

Elapsed: 00:00:00.00

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

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

Checking from sys session (session # 3)

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

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

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

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

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<

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

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

1.2. enq: TX row lock contention – Mode 4

The common cause for mode 4, enq: TX row lock contention are

（1）Unique Index
（2）Foreign key
（3）Bitmap indexes

1.2.1 Unique Index

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

ANAND@MATRIX> alter table sa add constraint pk_sa primary key (B);

Table altered.

Elapsed: 00:00:00.29

ANAND@MATRIX> desc sa

Name Null? Type

----------------------------------------------------------------------------------------------

A NUMBER

B NOT NULL NUMBER

ANAND@MATRIX> select * from sa;

A B

---------- ----------

1 1

2 2

3 3

4 4

Elapsed: 00:00:00.04

ANAND@MATRIX> insert into sa values (5,5);

1 row created.

Elapsed: 00:00:00.01

Didn’t commit. Check session 2

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

ANAND@MATRIX> insert into sa values (5,5);

The session hangs.Check the session waits and enqueues.

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

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

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

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

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

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

1.2.2 Foreign key

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

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

Table created.

Elapsed: 00:00:00.25

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

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

ANAND@MATRIX> insert into sa_child values (6,'DANNY');

Session 2 hangs.

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

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

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

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

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

ANAND@MATRIX> insert into sa_child values (6,'DANNY');

1 row created.

Elapsed: 00:05:02.03

1.2.3 Bitmap Index

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

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

ANAND@MATRIX> create bitmap index sa_c_bit_indx on sa(C);

Index created.

Elapsed: 00:00:00.10

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

ANAND@MATRIX> delete from sa where A=1;

Session 2 hangs.

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

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

<span style