文章內容

Oracle 10g enq:TX - contention等待事件

發(fā)布時間: 2012/9/14 17:00:38

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中執(zhí)行DML而不提交，另一個session執(zhí)行alter tablespace XXX read only,就會出現(xiàn)這個等待事件。
測試情況：
單實例情況：
session 1:
SQL> select sid from v$mystat where rownum <2;
       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 <2;
       SID
----------
       148
SQL> alter tablespace users read only;
由于session 1未提交，還在使用users表空間，session 2出現(xiàn)等待。
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<<16 | slot       sequence
從上述結果中可以看到：
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執(zhí)行的。
這里還可以用另一種方法找到阻塞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)類型的鎖時出現(xiàn)等待。
注意這里的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上執(zhí)行session：
SQL> conn test/test
Connected.
SQL> select sid from v$mystat where rownum < 2;
       SID
----------
       617
SQL> insert into info values (1);
1 row created.
不提交

在node 2上執(zhí)行session:
SQL> alter tablespace test read only;
出現(xiàn)等待。
在任意node上執(zhí)行：
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是一種保護共享資源的鎖定機制。該鎖定機制保護共享資源，以避免因并發(fā)操作而損壞數(shù)據，比如通過鎖定保護一行記錄，避免多個用戶同時更新。Enqueue采用排隊機制，即FIFO（先進先出）來控制資源的使用。

在Oracle 10g之前，Enqueue事件是一組鎖定事件的集合，如果數(shù)據庫中這個等待事件比較顯著，我們還需要進一步來追蹤是哪個類別的鎖定引發(fā)了數(shù)據庫等待。

從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）在釋放之前，對象上還可以繼續(xù)加其他類型的共享鎖，但是不能加排他鎖。

如果按照事務的類型劃分，又可以將鎖定劃分為DML鎖，DDL鎖以及內存鎖（也即通常所說的Latch）。Oracle在數(shù)據庫內部用Enqueue等待來記錄鎖定，通過Latch Free等待事件來記錄閂。Enqueue等待常見的有ST、HW、TX、TM等，下面擇要進行介紹。

1. 最重要的鎖定：TM與TX鎖
對于數(shù)據庫來說，最常見的鎖定類型是TM以及TX鎖定。

TX鎖通常被稱為事務鎖，當一個事務開始時，如執(zhí)行INSERT/DELETE/UPDATE/MERGE等操作或者使用SELECT ... FOR UPDATE語句進行查詢時，會首先獲取事務鎖，直到該事務結束。Oracle的TX鎖定是在行級獲得的，每個數(shù)據行上都存在一個鎖定位（1b-Lock Byte），用于判斷該記錄是否被鎖定，同時在每個數(shù)據塊的頭部（Header）存在一個ITL的數(shù)據結構，用于記錄事務信息等，當需要修改數(shù)據時，首先需要獲得回滾段空間用于存儲前鏡像信息，然后這個事務信息同樣被記錄在ITL上，通過ITL可以將回滾信息和數(shù)據塊關聯(lián)起來，所以說Oracle的行級鎖定是在數(shù)據塊上獲得的，行級鎖只有排他鎖沒有共享模式。

TM鎖通常稱為表級鎖，可以通過手工發(fā)出lock命令獲得，或者通過DML操作以及SELECT FOR UPDATE獲得，表級鎖可以防止其他進程對表加X排他鎖，防止在對數(shù)據修改時，其他任務通過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)。

當執(zhí)行DML操作時，首先加TM鎖，如果能獲得鎖定，則繼續(xù)加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鎖用于保護數(shù)據庫文件，使得文件在數(shù)據庫打開、表空間Online時不能執(zhí)行恢復。當進程對數(shù)據文件執(zhí)行恢復時，需要排他的獲得MR鎖。當數(shù)據庫打開時，每個文件上都分配一個MR鎖。注意在以上輸出中ID1代表文件號，其中也包含了201號臨時文件。

從Oracle Database 11g開始，除了每個文件要獲得MR鎖之外，每個登錄數(shù)據庫的會話現(xiàn)在都會缺省獲得一個AE鎖：

sys@CCDB> select * from v$lock where type = 'AE' and rownum <= 5;
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

現(xiàn)在MR鎖定和AE鎖定是數(shù)據庫中最為常見的鎖定。

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

3. ST（空間事務鎖）
ST鎖主要用于空間管理和字典管理的表空間（DMT）的區(qū)間分配，在DMT中典型的是對于uet$和fet$數(shù)據字典表的爭用。對于支持LMT的版本。應該盡量使用本地管理表空間，或者考慮手工預分配一定數(shù)量的區(qū)（Extent），減少動態(tài)擴展時發(fā)生的嚴重隊列競爭。

以下案例說明了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等待事件是我們最為關注的部分。這個系統(tǒng)中，除了enqueue等待事件以外，其他4個都屬于空閑等待事件，無須關注。來關注一下enqueue等待事件，在89.82 (mins)的采樣間隔內，累計enqueue等待長達16,192,686(cs)，即45小時左右。這個等待已經太過顯著，實際上這個系統(tǒng)也正因此遭遇了巨大的困難，觀察到隊列等待以后，這應該關注隊列等待在等待什么資源。快速跳轉的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語句：

-> End Buffer Gets Threshold:   10000
-> Note that resources reported for PL/SQL includes the resources used by
   all SQL statements called within the PL/SQL code. As individual SQL
   statements are also reported, it is possible and valid for the summed
   total % to exceed 100
Buffer Gets    Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
      4,800,073       10,268          467.5    51.0   2913840444
select length from fet$ where file#=:1 and block#=:2 and ts#=:3
        803,187       10,223           78.6     8.5    528349613
delete from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 a
nd ext#=:4
        454,444       10,300           44.1     4.8   1839874543
select file#,block#,length from uet$ where ts#=:1 and segfile#=:
2 and segblock#=:3 and ext#=:4
         23,110       10,230            2.3     0.2   3230982141
insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)
         21,201          347           61.1     0.2   1705880752
select file# from file$ where ts#=:1
….
          9,505           12          792.1     0.1   1714733582
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe
re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
          6,426          235           27.3     0.1   1877781575
delete from fet$ where file#=:1 and block#=:2 and ts#=:3