oracle等待事件7——事务上的等待事件

1、enq：TM-contention

执行DML期间，为防止对DML相关的对象进行修改，执行DML的进程必须对该表获得TM锁，若获得TM锁的过程发生争用，则等待enq：TM-contention事件。

TM锁其用途十分明确，但是准确的概念及定义方面有容易混淆的一面。oracle的手册上关于锁的分类说明如下：

DML锁：Date lock。执行DML时保护数据的锁。Row Lock（TX）保护特定行，Table Lock（TM）保护整个表，可以通过dba_kml_locks观察。

DDL锁：Data dictionary lock。保护User/Table/View/Procedure等定义，可以通过dba_ddl_locks观察。

internal locks and latches：

以上说明可能让大家以为存在DML锁和DDL锁这两个锁，给大家带来混乱。实际上，DML锁和DDL锁只是为了合理分配锁而赋予的名称，请注意这点。

DML锁实际上与TM锁一致，DML锁可以通过dba_dml_locks视图观察，这个视图的作用是从V$lock视图上筛选出锁类型为TM的。数据库上允许的tm锁数量，可以利用DML_LOCKS参数指定。若将DML_LOCKS参数设置为0，则对表无法获得TM锁。这时，oracle为了保障表定义被保护，对于表根本上不允许DDL操作。因此，即便不获得TM锁，也允许修改该表的特定行。如OPS环境下，为了减少在全局范围内获得TM锁过程中发生的附加资源消耗，有时也将DML_LOCKS值修改为0。

DDL锁实际上与library cache lock 一致。DDL锁可以通过DBA_DDL_LOCKS视图观察，这个视图世界上起到加工X$KGLLK视图后显示的作用。DDL锁除了DBA_DDL_LOCKS视图之外，还可以通过X$KGLLK，DBA_KGLLOCK等视图观察。

参考文件$ORACLE_HOME/dbms/admin/catblock.sql,可以确认DBA_DML_LOCKS、DBA_DDL_LOCKS、DBA_KGLLOCK视图的定义：

DBA_DML_LOCKS视图在v$locks视图上只选出TM锁并加工得到的；

DBA_DDL_LOCKS视图是加工X$KGLLK视图得到的；

DBA_KGLLOCK视图则是将X$KGLLK视图和X$KGLPN视图合并之后加工得到的视图。

对于表，普通DML语句以Sub-Exclusive（SX）模式获得TM锁。Sub-exclusive模式之间存在共享性，，所以多个会话可以对形同的表执行DML。已执行DML的会话对于表，以Sub-Exclusive莫侯斯获得TM锁，对于已修改的数据Exclusive模式获得TX锁。

下面看一个v$lock视图的分析：

对ObjectID 54679（ID1）相应的对象，可以确认正在以Sub-Exclusive模式（3）获得TM锁。TM锁的ID1相当于表的Object ID。因此结合DBA_OBJECTS，就可以判断TM锁对应的表。因此Sub-Exclusive模式之间存在共享性，所以DML之间不发生围绕TM锁的争用，enq：TM-contention等待也不会发生。

一般发生TM锁争用的情况如下：

1）修改无索引外键（foreign key）的父键时：这是9i之前的故事了。。。在子表的外键没有索引的状态下，若父表的key被修改，则对子表应该以shared模式或shared_sub_exclusive模式（这两种模式除了shared模式与自己可以共享，其他两两之间均不能共享）获得TM锁，已获得的TM锁一直拥有到父表修改key的事务结束为止（commit或rollback），但是9i后算法大幅改进，一般不会再发生争用。

2）DML和DDL之间的TM锁争用

对于事务正运行的表，基本上不可能执行DDL，因此，这时不会发生争用引起的性能问题。接下来，看具体的例子：

SQL> create table t as select object_id,object_name from dba_objects;

表已创建。

在另一个会话中执行：

SQL> alter table t add id2 number;

alter table t add id2 number

*

第 1 行出现错误:

ORA-00054: resource busy and acquire with NOWAIT specified

上面的例子说明：对于已经完成update但还没有提交的表，不可能执行DDL。相反，对于正执行DDL的表，执行DML，可能发生TM争用：

对于特定进程的表创建索引时，对于表在创建索引期间以shared莫侯斯获得TM锁。对于这个表执行DML会话，应该以Sub-exclusive模式获得TM锁。因为shared 模式和sub-exclusive模式没有共享性。所以执行DML的会话等到执行DDL执行（此处为创建索引）结束为止时，才发生enq：TM-contention等待事件。减少DDL引起的TM锁争用的方法如下：

***若对于数据多的表执行不当的DDL，则访问此表所有的DML都会陷入等待状态，可能发展至故障状态。通过合理的管理，从根本上防止才是推荐的方法。

***执行DDL时，最好用Online选项。随着oracle版本的升级，online状态下可执行的DDL逐步增加。大部分DDL上，可以使用online选项。例如使用online选项执行create index命令时，不是以shared模式，而是以sub-shared（SS）模式获得TM锁，SS莫侯斯和Siub-Exclusive模式之间可共享，所以在创建索引过程中，执行DML。即，不会发生enq：TM-contention等待。

***使用parallel DDL将 DDL执行的速度最大化，对拥有大量数据的表执行DDL时，若恰当使用parallel选项，可将DDL本身性能最大化，而且同时使用nologing选项也比较好。如果提升了DDL执行速度，TX锁争用引起的等待时间相应也会下降。

3）lock table。。。引起的TM锁争用。

SQL> update t set object_id=1 where rownum=1;

已更新 1 行。

在另一个会话上执行锁住表的语句：

SQL> lock table t in exclusive mode;

这条语句会一直处于等待状态。

会话a上因update以sub-exclusive模式拥有TM锁的状态下，会话b利用lock table 命令，视图以exclusive 模式获得tm锁时，如果发生争用，则等待enq：TM-contention。

4）Direct load工作引起的TM锁争用。

insert/*+append*/into...或SQL*loader的direct path load之类的部分功能，对于相应表以exclusive模式获得tm锁。direct load 工作不经过SGA，而是直接写入到数据文件里，所以在执行工作期间不允许对表进行任何修改。总之，以Exclusive模式获得TM锁，对表不允许发生任何修改，这点应该得到保障，工作才能得以继续。

Direct load 工作在执行期间，不允许对表执行任何DDL或DML。因此，事务多的时刻执行direct load 工作时，需要确认TM锁争用是否可能引发问题。 将sql*loader 利用parallel模式执行时，对表以shared 模式获取TM锁，因此，此种清苦阳下也不会允许其他会话上的DDL或DML。

2、enq：TX-row lock contention，enq：TX-allocate ITL Entry，enq：TX-index contention

从10g开始，根据TX锁所使用的情况赋予了恰当名称的等待事件。TX锁是保护事务的，事务结束（执行conmmit 或rollback时）时便会释放。因此，为获得TX锁而等待的会话，需要等到拥有锁的会话的事务结束为止。

进程需要获得TX锁的情况和这个过程中发生争用时，观察的等待现象如下：

1）欲修改特定的行时，相关的等待事件时enq：TX-row lock contention。

修改相同的行，是发生TX锁引起争用的最普遍的情况，oracle利用“行自身修改信息+ITL+事务表slot（插槽的意思）+TX锁”体现行级别的锁，进程为修改特定行而访问相应行时，若当前行时已经修改的状态，就从ITL确认修改相应行的事务，并将本身添加到TX enquence目录，等待enq：TX-row lock contention事件，直到拥有TX锁的进程释放锁为止.

下面测试两个会话同时修改一个表的同一个行，查看v$lock视图得到的结果：

会话1：SQL> update test set object_id=1 where rownum=1;

会话2：SQL> update test set object_id=1 where rownum=1；

一直在等待

会话3：SQL> select *from v$lock where type='TX';

ADDR               KADDR              SID      TY        ID1            ID2         LMODE    REQUEST   CTIME      BLOCK

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

6CA63898     6CA638AC        144      TX     720940        274            0                  6               110             0

6C278F2C    6C279040         162      TX     720940        274            6                  0              150               1

对上面的测试做如如下解释：会话1（sid=162）是以exclusive模式（LMODE=6）获得TX锁（ID1=720940  ID2=274）的状态；

会话2（SID=144）是为了以相同的ID1，ID2 以exclusive 模式（REQUEST=6）模式获取TX锁等待中。

TX锁保护的资源是“事务”，事务是通过USN+SLOT+SQN信息表示的，所以：TX锁的ID值相当于USN+SLOT, ID2值相当于SQN。因此可以通过v$lock视图掌握哪个事务发生争用现象。

enq：TX-row lcok contention 时间的P2 , P3 值 与TX锁的ID1，ID2值相匹配。若从v$session_wait视图上查看会话2的等待现象：

SQL> select sid,event,p1,p2,p3,state from v$session_wait;

       SID          EVENT                                                  P1                          P2         P3                     STATE

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

       143    SQL*Net message to  client           1111838976                1          0              WAITED SHORT TIME

       144   enq: TX - row lock  contention        1415053318           720940   274                       WAITING

请注意，若此时会话1结束事务（commit或 rollback），会话2从等待状态如愿获得了TX锁，而且，将已获得的TX资源信息替换为本身事务的信息：即，会话2获得TX锁的ID1， ID2值 替换为会话2事务的USN, SLOT , SQN信息。这是因为锁保护的资源——事务    已经被修改。

那么说了半天，TX锁的ID1 值怎么变为 USN ，SLOT 值？详情参照我的博客：

）使用ID2的值“174”检索V$TRANSACTION视图

sec@ora10g> select XIDUSN,XIDSLOT,XIDSQN from V$TRANSACTION where XIDSQN=274;

   XIDUSN    XIDSLOT     XIDSQN

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

       11         44        274

3）10和44对应到ID1的值“720940 ”的方法

11*2^16+44=655404

4）使用ID1的值计算得到XIDUSN和XIDSLOT方法

sec@ora10g> select trunc(720940/power(2,16)) XIDUSN from dual;

    XIDUSN

----------

        11

sec@ora10g> select bitand(720940 ,to_number('ffff','xxxx'))+0 XIDSLOT from dual;

   XIDSLOT

        44

这便是他们之间既简单有复杂的关系。

2）欲修改特定行上唯一键（unique key ）或主键（primary key）相应的列时，相关的等待事件时enq：TX-row lock contention。

会话1（sid=162）：

SQL> create table test2 (id number, name varchar2(20));

SQL> insert into test2 values(1,'mumu');

已创建 1 行。

会话2（sid=143）：

SQL> insert into test2 values(1,'liu');

会话3：

SQL> select * from v$lock where sid in(143,162)；

ADDR       KADDR                SID    TY        ID1            ID2      LMODE   REQUEST     CTIME      BLOCK

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

6C278F2C 6C279040        143     TX     327707       3377          6          0                      92          0

6C237F30 6C237F48        143    TM      58210          0                3          0                       92          0

6CA63898 6CA638AC        143    TX     262150      14843          0          4                      92          0

6C290364 6C290478        162     TX     262150      14843      

   6          0                     212          1

6C237E9C 6C237EB4        162   TM      58210          0                 3          0                     212          0

 需要特别留意V$lock视图的结果，首先执行insert的162号会话正在以exclusive模式获得一个TX锁（ID1=262150, ID2=14843）。相反，最后执行insert的143号会话在已经以exclusive获得TX锁（ID1=327707，ID2=3377）的情况下（这是一个新的事务，修改的不是同一行，所以在此时还没有争用,这两个会话都是以LMODE=3(Sub-exclusive)获得锁），但是，此时，又出现了一个TX锁，是为了将162号会话获得的TX锁以shared模式拥有，正在等待。从这里我们可以间接推断，oracle为了保护唯一键，使用了哪些方法，oracle在表里添加行之后，索引也一同被添加，在添加索引过程中确认是否违反unique属性。若存在相同的key值，若之前事务还没提交，就像咱们刚才的测试，则为了将之前事务已获得的TX锁

以shared模式获得而等待（如果我们插入的行不冲突，就不会以shared模式去获得这个锁，也就不会出现等待）；如果会话1事务已经提交，则出现错误：第 1 行出现错误: ORA-00001: unique constraint (SYS.T2_INX) violated

唯一键冲突引起的TX锁争用完完全全是应用程序的问题，为创建唯一键，使用特别算法或从已存表里筛选最大值（max）等方法，唯一键冲突引起的TX锁争用随时可能发生。最好的解决方法就是使用sequence创建唯一键。

3）欲修改块的ITL上想要登记自身相应事务条目时，先逛逛的等待事件是enq：TX-allocate ITL entry。

所有事务在修改块之前，必须在块头的ITL上登记条目，若ITL超过maxtrans指定的最大值，或块内的剩余空间不足，而不能登记条目时，为了以shared 模式获得TX锁而等待（这个TX锁，因为早已在ITL上登记条目的其他进程以exclusive模式获得，和上面的情况一样，都是以shared模式获得TX锁，而等待。）。这时的等待现象可以通过enq：TX-allocate ITLentry事件来观察。

但是，唯一键冲突引起的TX锁争用和ITL条目不同引起的TX锁争用之间是存在着微秒的区别的：若是唯一键冲突，等待会话在已经以exclusive模式获得属于自己的TX锁的状态下，为了以shared 模式获得之前会话取得的TX锁而等待；相反，若是ITL条目不足，则在获取只属于自己的TX锁之前，为了将之前会话获得的TX锁以shared模式拥有而等待。这些区别源于修改块之前必须先登记ITL条目。

一般情况下，ITL条目不足伴随的TX锁争用现象不会发生，这是因为数十或者数百个会话不大可能同时对一个块的不同行进行修改。但若是发生了行连接（row  chaining）或行迁移（row migration）的行，则更新一行是，对多个块都要分配ITL条目，因此发生ITL条目不足的引起TX锁争用的概率会升高。

利用v$segment_statistics视图，可以得知对哪些段较多发生ITL不足以引起的争用。利用statistic_name='ITL waits' 这个条件，可以了解对哪些段较多发生ITL不足引起的争用。

4）欲修改已创建位图索引（bitmap index）列值时，相关的等待事件时enq：TX-row lock contention

想理解位图索引（bitmap index）冲突引起的TX锁争用，必须掌握关于位图索引内图结构的知识。B*Tree索引的叶节点以排序形式存储索引条目，每个索引条目指向各自的一个rowid。所以唯一键冲突之外，索引条目之间不发生争用。而位图索引的叶节点具有“column值+start rowid+ end rowid+ bitmap值”的形式。即，一个叶节点管理大范围的rowid.每当表的行被修改时，对位图索引相应的列值，每次都要重新计算行所属叶节点的位图。因此，两个会话同时对相同的叶节点执行位图运算时，为保障顺序，应该获取TX锁。即，如果特定会话在exclusive模式获得TX锁后，执行了位图运算，但是还没有提交，则其他会话为了对之前的事务保障，以shared

模式获得TX锁而等待，所以等到位图运算结束为止。一个叶节点管理大范围的ROWID，所以可能出现大量TX锁争用。位图索引冲突引发TX锁争用时，则等待enq：TX-row lock contention事件。

下面做一个测试：

打开一个会话1：

SQL> select sid from v$mystat where rownum<2;

       SID

       148

SQL> create table tx_b(name1 char(10),name2 char(10),name3 char(10));

SQL> create bitmap index tx_b_idx on tx_b(name1,name2,name3);

索引已创建。

SQL> insert into tx_b values('a','b','c');

新打开一个会话2：

       141

一直处于等待状态

再打开一个会话3：

SQL> select * from v$lock where type='TX';  

ADDR              KADDR           SID    TY        ID1            ID2       LMODE    REQUEST     CTIME      BLOCK

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

6C26DA0C  6C26DB20        141   TX     327684       3452          6             0                     111            0

6CA63958  6CA6396C        141     TX     262181      14897          0             4                   111            0

6C2953FC  6C295510        148    TX     262181      14897          6             0                      216          1

这个结果与唯一键冲突引起的等待现象完全相同。只通过等待现象，不能区别唯一键冲突和位图索引冲突之间的差异，只有在同时考虑到创建索引的准确信息和sql语句，才能掌握准确的原因。

5）索引叶节点（leaf node）上发生分割时，相关的等待时间是enq：TX-index contention

B*Tree索引在添加数据的过程中，如果叶节点已满就会进行分割（split），以此到大平衡，会话A在exclusive模式已获得TX锁的情况下，执行分割的过程中，会话B正要修改叶节点时，会话B为了以shared模式获得会话A拥有的TX锁只好等待，在此期间会发生enq：TX-index contention等待事件。

一般情况下enq：TX-index contention等待不会发生，它主要是在多个会话对已有索引的表执行较多量的DML时发生。这个等待现象虽然不经常发生，但创建的数量多，组成索引的列值大而指针叶节点的块频繁被分割时，成为性能下降的原因，特别是使用sequence等方式生成值的列在创建索引时，一直出现只在最后的叶节点添加值的现象，所以可能经常发生索引分割。这是以排序形式保持叶节点的B*Tree 索引属性引起的，因此多个会话将大量的数据执行insert

时，与buffer busy waits 等待一起发生enq：TX-index contention等待。

减少索引分割引发的争用的基本方法，就是阻止在相同的叶节点块里集中添加数据的现象，例如可以应用partitioning方法进行物理分散，或是修改该组成索引的列的顺序而自然分散等方法。单若存在以特定键为基准排序这样的约束条件，就无法使用此方法。具有代表性的情况是利用sequence赋予主键值，利用/*INDEX_DESC*/ 之类的提示，对此索引以排序的方式执行扫描数据查询。通过这种方式使用索引时，必须保障相应键为基准排序，所以不能在索引上应用partitioning或修改该索引的列顺序。

另一种方法是将索引的块设定的较大。使用较大的块时，一个块上的条目数量多，因此较少发生分割。但是若块大小增加，就可能引发buffer lock 争用引起的buffer busy waits 等待现象，所以要谨慎使用。

请注意一点，修改没有创建索引的表过程中，有时能发生enq：TX-index contention等待。表里有lob列时，就会从内部创建对于LOB数据的索引（称为LOB索引）因此多个会话同时修改LOB数据时会发生索引争用。

6）其他情况时，相关的等待时间是enq：TX-contention

***分布式事务（distributed transaction）环境下，通过prepared transaction读取已获得锁的行时，知道事务结束为止，为了一shared 模式获取TX锁而需要等待。

***将FLM以段空间管理方法使用时，想要分配TFL（transaction free list）的进程无法分配到TFL时，为了一shared 模式获得已经占有TFL的事务的TX锁，需要等待。

***回滚段头的事务表上西药分配新的slot时，应该以exclusive模式获得TX锁。

3、enq：UL-contention  PL/SQL lock Timer

使用DBMS_LOCK程序包，可以对任意假想的资源挂起锁，如果是因为DML发生的锁时，虽然必须需要物理资源（表，事务，段等），但使用DBMS_LOCK程序包没有这种限制，使用DBMS_LOCK程序包获取锁称为UL(Userdefined  Lock)锁，为了获得UL锁 而等待的会话，将发生enq：UL-contention等待事件。

利用DBMS_LOCK.REQUEST函数可以将UL锁以Exclusive模式获得，利用DBMS_LOCK.RELEASE函数，可以释放锁。记住UL锁的释放只能在拥有锁的会话上实现，若特定会话因长时间拥有UL锁，而引发并发性问题，则除了强制结束会话之外没有其他方法。使用DBMS_LOCK.REQUEST函数时，尽量使用RELEASE_ON_COMMIT选项，以避免多余的拥有锁，使用这个选项若发生提交或回滚，则自动释放该事务所拥有的UL锁。

另外，还有一个与DBMS_LOCK程序包相关的等待事件，利用DBMS_LOCK.SLEEP Procedure 暂时中断事务时，则该进程等待PL/SQL lock timer事件。

PL/SQL lock timer 等待事件不会引起性能的问题。如果该等待时间比预期还要长，就应该重新检查应用程序的实现逻辑是否有问题。