Oracle 10g 查询：TX

10g中enqueue TX等待分为4类，分别是 1. enq:TX - row lock contention 2. enq:TX - index contention 3. enq:TX - ITL 4. enq:T

10g中enqueue TX等待分为4类，分别是
1. enq:TX - row lock contention
2. enq:TX - index contention
3. enq:TX - ITL
4. enq:TX - contention
前三种的含义比较明显，第4种是表示其它类型的transaction contention,即除了前三种之外的都包含在其中。
有多种情况都可能造成enq:TX - contention。比如：一个session中执行DML而不提交，另一个session执行alter tablespace XXX read only,就会出现这个等待事件。
测试情况：
单实例情况：
session 1:
SQL> select sid from v$mystat where rownum        SID
----------
       145
SQL> select table_name,tablespace_name from user_tables where table_name='INFO';
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
INFO                           USERS
SQL> update info set note=upper(note);
已更新35行。
SQL> (注意未提交)
session 2:
SQL> select sid from v$mystat where rownum        SID
----------
       148
SQL> alter tablespace users read only;
由于session 1未提交，还在使用users表空间，session 2出现等待。
session 3:
SQL> select sid,event,p1,p2,p3 from v$session_wait where sid=148;
    SID EVENT                                                                    P1         P2         P3
------- ---------------------------------------------------------------- ---------- ---------- ----------
    148 enq: TX - contention                                             1415053316      65563        166
  
查询得知session 2在等待enq: TX - contention事件。其中p1,p2,p3含义可以从如下视图中得到。
SQL> SELECT name, parameter1, parameter2, parameter3 from v$event_name where name = 'enq: TX - contention';
NAME                      PARAMETER1           PARAMETER2           PARAMETER3
------------------------- -------------------- -------------------- --------------------
enq: TX - contention      name|mode            usn从上述结果中可以看到：
parameter1表示enqueue的name和mode。parameter2的高16位表示事务的xidusn,低16位表示事务的xidslot,parameter3表示事务的xidsqn，即p2,p3表示一个特定的事务。
结合v$transaction和v$session,就可以知道阻塞session 2的会话信息了。
检查enqueue的name和mode
SQL> SELECT sid, CHR (BITAND (p1,-16777216) / 16777215) ||
  2         CHR (BITAND (p1, 16711680) / 65535) enq,
  3         DECODE (BITAND (p1, 65535), 1, 'Null', 2, 'Sub-Share',
  4                  3, 'Sub-Exclusive', 4, 'Share', 5, 'Share/Sub-Exclusive',
  5                  6, 'Exclusive', 'Other') lock_mode
  6  FROM   v$session_wait WHERE sid = 148;
    SID ENQ  LOCK_MODE
------- ---- -------------------
    148 TX   Share
检查阻塞session 2的会话：
SQL> select sid from v$session where taddr in
  2  (select b.addr from v$session_wait a,v$transaction b
  3  where a.event='enq: TX - contention' and trunc(a.p2/power(2,16)) = xidusn
  4  and (bitand(a.p2,to_number('ffff','xxxx')) 0) = xidslot and a.p3 = xidsqn);
    SID
-------
    145
  
这里我们可以看到，造成session 2等待的事务是由session 1执行的。
这里还可以用另一种方法找到阻塞session 2的会话：
1、先查看session 2请求和持有的事务锁情况：
SQL> select sid,id1,id2,trunc(id1/power(2,16)) rbs,bitand(id1,to_number('ffff','xxxx')) 0 slot,id2 seq,lmode,request,type from v$lock where type = 'TX' and sid=&sid;
输入 sid 的值:  148
原值    2: from v$lock where type = 'TX' and sid=&sid
新值    2: from v$lock where type = 'TX' and sid=148
    SID        ID1        ID2        RBS       SLOT        SEQ      LMODE    REQUEST TY
------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --
    148      65563        166          1         27        166          0          4 TX
    148     196646        168          3         38        168          6          0 TX
148在请求4(share)类型的锁时出现等待。
注意这里的ID1和ID2与v$session_wait中的p2,p3完全一致，都是表示某个事务的。 
SQL> select sid,event,p1,p2,p3 from v$session_wait where sid=148;
    SID EVENT                                                                    P1         P2         P3
------- ---------------------------------------------------------------- ---------- ---------- ----------
    148 enq: TX - contention                                             1415053316      65563        166
2、查看事务(ID1,ID2)使用锁的情况
SQL> select sid,trunc(id1/power(2,16)) rbs,bitand(id1,to_number('ffff','xxxx'))+0 slot,id2 seq,lmode,request
  from v$lock where type='TX' and id1=&id1 and id2=&id2;
输入 id1 的值:  65563
输入 id2 的值:  166
原值    2: from v$lock where type='TX' and id1=&id1 and id2=&id2
新值    2: from v$lock where type='TX' and id1=65563 and id2=166
    SID        RBS       SLOT        SEQ      LMODE    REQUEST
------- ---------- ---------- ---------- ---------- ----------
    148          1         27        166          0          4
    145          1         27        166          6          0
这里可以看到148在请求share型事务锁，而145持有exclusive型事务锁，因此造成了session 2的等待。  
RAC情况：与单实例的情况类似
在node 1上执行session：
SQL> conn test/test
Connected.
SQL> select sid from v$mystat where rownum        SID
----------
       617
SQL> insert into info values (1);
1 row created.
不提交
     
在node 2上执行session:
SQL> alter tablespace test read only;
出现等待。
在任意node上执行：
SQL> select c.inst_id,c.sid
from gv$session_wait a,gv$transaction b, gv$session c
where a.event='enq: TX - contention'
and trunc(a.p2/power(2,16)) = b.xidusn
and (bitand(a.p2,to_number('ffff','xxxx'))+0) = b.xidslot
and a.p3 = b.xidsqn
and c.taddr = b.addr;
   INST_ID        SID
---------- ----------
         1        617

 

 

 

 

 

Enqueue （队列等待）：
Enqueue是一种保护共享资源的锁定机制。该锁定机制保护共享资源，以避免因并发操作而损坏数据，比如通过锁定保护一行记录，避免多个用户同时更新。Enqueue采用排队机制，即FIFO（先进先出）来控制资源的使用。

在Oracle 10g之前，Enqueue事件是一组锁定事件的集合，如果数据库中这个等待事件比较显著，我们还需要进一步来追踪是哪个类别的锁定引发了数据库等待。

从Oracle 10g开始，Oracle对于队列等待进行了细分，v$event_name视图中可以查询这些细分后的等待事件，简要摘录几个示例如下：

sys@CCDB> select name,wait_class
  2  from v$event_name     
  3  where name like '%enq%';
NAME                                  WAIT_CLASS
------------------------------------- ------------------------
enq: PW - flush prewarm buffers       Application
enq: RO - contention                  Application
enq: RO - fast object reuse           Application
enq: KO - fast object checkpoint      Application
enq: TM - contention                  Application
enq: ST - contention                  Configuration
enq: TX - row lock contention         Application
enq: TX - allocate ITL entry          Configuration
enq: TX - index contention            Concurrency
enq: TW - contention                  Administrative
enq: HW - contention                  Configuration
......

Oracle 的锁按照类型可以分为排他锁（Exclusive，缩写为X）与共享锁（Share，缩写为S），或者是两者的组合锁。排他锁（X）也被称为独占锁，在排他锁释放之前，一个对象上不能施加任何其他类型的锁定；而共享锁（S）在释放之前，对象上还可以继续加其他类型的共享锁，但是不能加排他锁。

如果按照事务的类型划分，又可以将锁定划分为DML锁，DDL锁以及内存锁（也即通常所说的Latch）。Oracle在数据库内部用Enqueue等待来记录锁定，通过Latch Free等待事件来记录闩。Enqueue等待常见的有ST、HW、TX、TM等，下面择要进行介绍。

1. 最重要的锁定：TM与TX锁
对于数据库来说，最常见的锁定类型是TM以及TX锁定。

TX锁通常被称为事务锁，当一个事务开始时，如执行INSERT/DELETE/UPDATE/MERGE等操作或者使用SELECT ... FOR UPDATE语句进行查询时，会首先获取事务锁，直到该事务结束。Oracle的TX锁定是在行级获得的，每个数据行上都存在一个锁定位（1b-Lock Byte），，用于判断该记录是否被锁定，同时在每个数据块的头部（Header）存在一个ITL的数据结构，用于记录事务信息等，当需要修改数据时，首先需要获得回滚段空间用于存储前镜像信息，然后这个事务信息同样被记录在ITL上，通过ITL可以将回滚信息和数据块关联起来，所以说Oracle的行级锁定是在数据块上获得的，行级锁只有排他锁没有共享模式。

TM锁通常称为表级锁，可以通过手工发出lock命令获得，或者通过DML操作以及SELECT FOR UPDATE获得，表级锁可以防止其他进程对表加X排他锁，防止在对数据修改时，其他任务通过DDL来修改表结构或者truncate、drop表等操作。可以通过v$lock视图来观察锁定信息，其中TYPE字段表示锁定类型。对于TM锁LMODE字段又代表了不同级别的TM锁，这些级别包括2-row-S(SS)、3-row-X(SX)、4-share(S)、5-S/Row-X(SSX)和6-exclusive(X)。

当执行DML操作时，首先加TM锁，如果能获得锁定，则继续加TX事务锁。在一个会话中，一般只存在一个TX事务锁，在提交或回滚之前，该会话的所有DML操作都属于一个事务，使用一个回滚段，占用一个回滚段事务槽（Slot）。

以下通过SCOTT用户锁定一行记录，暂时不要提交：

scott@CCDB> update emp set sal = 4000 where empno = 7788;
1 row updated.

在另外session通过v$lock视图可以看到相关的锁定信息；

sys@CCDB> select sid,username from v$session where username = 'SCOTT';
       SID USERNAME
---------- ------------------------------
      1075 SCOTT
sys@CCDB> select * from v$lock where sid = 1075;
ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000008F836260 000000008F8362B8       1075 AE         99          0          4          0       1208          0
00002BA14E74A7F8 00002BA14E74A858       1075 TM      69539          0          3          0         16          0
000000008DF49A30 000000008DF49AA8       1075 TX      65551      30498          6          0         16          0

此时表上的行级排他锁会阻塞对于表的DDL语句：

sys@CCDB> truncate table scott.emp;
truncate table scott.emp
                     *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

此外，TM锁定的ID1代表的就是锁定的对象号：

sys@CCDB> select owner,object_name from dba_objects where object_id = 69539;
OWNER           OBJECT_NAME
--------------- ---------------
SCOTT           EMP

而TX锁的ID1代表的是事务的回滚段回滚段号、事务槽号，ID2代表的是顺序号：

sys@CCDB> select trunc(65551/power(2,16)),mod(65551,power(2,16)) from dual;
TRUNC(65551/POWER(2,16)) MOD(65551,POWER(2,16))
------------------------ ----------------------
                       1                     15

通过v$transaction视图也可以找到这个事务的信息（注意XIDSQN正是TX锁的ID2信息）：

sys@CCDB> select XIDUSN,XIDSLOT,XIDSQN from v$transaction;
    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
         1         15      30498

如果转储回滚段信息进行分析，再结合ITL事务槽，可以清晰地看到锁定的含义以及整个事务的处理过程。

 

2. 最常见的锁定：MR与AE锁
可能很多朋友都注意过，在v$lock视图中，最常见的其实是MR锁，也就是介质恢复锁（Media Recovery）：

sys@CCDB> select * from v$lock where type = 'MR';
ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000BC2EE378 00000000BC2EE3D0       1097 MR          1          0          4          0    6984045          0
00000000BC2EE448 00000000BC2EE4A0       1097 MR          2          0          4          0    6984045          0
00000000BC2EE518 00000000BC2EE570       1097 MR          3          0          4          0    6984045          0
00000000BC2EE5E8 00000000BC2EE640       1097 MR          4          0          4          0    6984045          0
00000000BC2EE6B8 00000000BC2EE710       1097 MR          5          0          4          0    6984045          0
00000000BC2EE788 00000000BC2EE7E0       1097 MR          6          0          4          0    6984045          0
00000000BC2EE858 00000000BC2EE8B0       1097 MR          7          0          4          0    6984045          0
00000000BC2EE940 00000000BC2EE998       1097 MR          8          0          4          0    6984045          0
00000000BC2EEA10 00000000BC2EEA68       1097 MR        201          0          4          0    6984045          0
00000000BC2F12F8 00000000BC2F1350       1097 MR          9          0          4          0    1132526          0
10 rows selected.

MR锁用于保护数据库文件，使得文件在数据库打开、表空间Online时不能执行恢复。当进程对数据文件执行恢复时，需要排他的获得MR锁。当数据库打开时，每个文件上都分配一个MR锁。注意在以上输出中ID1代表文件号，其中也包含了201号临时文件。

从Oracle Database 11g开始，除了每个文件要获得MR锁之外，每个登录数据库的会话现在都会缺省获得一个AE锁：

sys@CCDB> select * from v$lock where type = 'AE' and rownum ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000BC2EDF68 00000000BC2EDFC0        822 AE         99          0          4          0    2761930          0
00000000BC2EE108 00000000BC2EE160        946 AE         99          0          4          0    3458645          0
00000000BC2EE1D8 00000000BC2EE230       1003 AE         99          0          4          0     207674          0
00000000BC2EE2A8 00000000BC2EE300       1092 AE         99          0          4          0    6984538          0
00000000BC2EEAE0 00000000BC2EEB38        991 AE         99          0          4          0    3458644          0

现在MR锁定和AE锁定是数据库中最为常见的锁定。

sys@CCDB> select name from v$event_name where name like '%AE%';
NAME
------------------------------------------------------------
enq: AE - lock

 

3. ST（空间事务锁）
ST锁主要用于空间管理和字典管理的表空间（DMT）的区间分配，在DMT中典型的是对于uet$和fet$数据字典表的争用。对于支持LMT的版本。应该尽量使用本地管理表空间，或者考虑手工预分配一定数量的区（Extent），减少动态扩展时发生的严重队列竞争。

以下案例说明了ST锁可能会导致的严重性能问题。

DB Name         DB Id    Instance     Inst Num Release     OPS Host
------------ ----------- ------------ -------- ----------- --- ------------------
DB           40757346    tqgzs               1 8.1.7.4.0   NO  server
                Snap Id     Snap Time      Sessions
                ------- ------------------ --------
Begin Snap:       2845 31-10月-03 02:10:16      46
  End Snap:       2848 31-10月-03 03:40:05      46
   Elapsed:                  89.82 (mins)

对于一个Statspack的report，采样时间是非常重要的维度，离开时间做参考，任何等待都不足以说明问题。

Top 5 Wait Events
~~~~~~~~~~~~~~~~~                                    Wait               % Total
Event                                                Waits  Time (cs)   Wt Time
-------------------------------------------- ------------ ------------ -------
enqueue                                            53,793   16,192,686   67.86
rdbms ipc message                                  19,999    5,927,350   24.84
pmon timer                                          1,754      538,797    2.26
smon timer                                             17      522,281    2.19
SQL*Net message from client                        94,525      520,104    2.18
          -------------------------------------------------------------

在Statspack分析中，Top 5等待事件是我们最为关注的部分。这个系统中，除了enqueue等待事件以外，其他4个都属于空闲等待事件，无须关注。来关注一下enqueue等待事件，在89.82 (mins)的采样间隔内，累计enqueue等待长达16,192,686(cs)，即45小时左右。这个等待已经太过显著，实际上这个系统也正因此遭遇了巨大的困难，观察到队列等待以后，这应该关注队列等待在等待什么资源。快速跳转的Statspack的其他部分，看到以下详细内容：

Enqueue activity for DB: DB  Instance: aaa  Snaps: 2845 -2848
-> ordered by waits desc, gets desc
Enqueue            Gets      Waits
---------- ------------ ----------
ST                1,554      1,554
          -------------------------------------------------------------

看到主要队列等待在等待ST锁定，对于DMT，我们说这个等待和FET$、UET$的争用紧密相关。再回过头来研究捕获SQL语句：

->结束缓冲区获取阈值：   10000
->请注意，为 PL/SQL 报告的资源包括
在 PL/SQL 代码中调用的所有 SQL 语句使用的资源。  由于还报告了各个 SQL
   语句，因此总和
   总百分比可能超过 100
  缓冲区获取数    每个执行执行数  % 总哈希值
------- -------- ------------ -------------- ------- --------- ---
      4,800,073       10,268          467.5    51.0   2913840444
从 fet$ 中选择长度，其中 file#=:1 和 block#=:2 和 ts#=:3
        803,1 87       10,223           78.6     8.5    528349613
删除uet$ 其中 ts#=:1 和 segfile#=:2 和 segblock#=:3
nd ext#=:4
        454,444       10,300           44.1     4.8   1839874543
选择文件# ,块#,长度自uet$ 其中 ts#=:1 和 segfile#=:
2 和 segblock#=:3 和 ext#=:4
         23,110       10,230            2.3     0.2   3230982141
插入 fe t$（文件#，块# ,ts#,length) 值 (:1,:2,:3,:4)
         21,201          347           61.1     0.2   1705880752
从 file$ 中选择文件#，其中 ts#=:1
….
          9,505           12          792.1     0.1   1714733582
从 fet$ f、ts$ 中选择 f.file#、f.block#、f.ts#、f.length
t.ts# =f。 ts# 和 t.dflextpct!=0 和 t.bitmapped=0
          6,426          235           27.3     0.1   1877781575
从 fet$ 中删除，其中 file#=:1 和 block#=:2 和 t s#=:3

可以看到数据库操作关闭UET$、FET$系统表已经成为系统的主要瓶颈。

至此，已经可以准确地为该系统定位问题，相应的解决方案也很容易确定，在Oracle 8.1.7中使用LMT代替DMT，这是解决问题的根本办法，当然实施起来还要进行综合考虑一下，实际情况需复杂化。

- 结束 -