oracle 导出secquence,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;

已更新 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        2743)10和44對應到ID1的值“720940 ”的方法11*2^16+44=6554044)使用ID1的值計算得到XIDUSN和XIDSLOT方法sec@ora10g>select trunc(720940/power(2,16)) XIDUSN from dual;XIDUSN----------11sec@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');

已創建 1 行。

新打開一個會話2：

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

SID

----------

141

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

一直處於等待狀態

再打開一個會話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 等待事件不會引起性能的問題。如果該等待時間比預期還要長，就應該重新檢查應用程序的實現邏輯是否有問題。