enq: TX – row lock contention

概要

　enq:TX – row lock contention待機イベントは行レベル・ロックによる競合が発生したことを意味します。
　下記のような3つの場合に行レベル・ロック競合が発生します。

• 複数セッションが同一行を更新する場合
• 複数セッションが同一一意キーでの競合を起こす場合
• 複数セッションがビットマップ索引で競合を起こす場合

複数セッションが同一行を更新する場合(モード6)

TXロック競合が発生する最も一般的な原因には、同じ行を更新する場合があります。
あるプロセスが特定の行を更新するためにアクセスした時、行が更新された状態であれば、
ITLから該当行を更新したトランザクションを確認します。その後、自分をTXエンキュー・リストに追加し、
enq:TX – row lock contention待機イベントで待機します。
この待機は、TXロックを保有したプロセスがロックを解除するまで発生し続けます。下記の例を見てみます。

セッションA:(SID = 150)
SQL> update test set id = 1 where rownum = 1;

セッションB:(SID = 148)
SQL> update test set id = 1 where rownum = 1;
... Wait ...

　セッションBはセッションAがすでに更新した行に対して、また更新をしようと要求しているため、待機状態におちいっている状況です。
この状態でTXロックの待機状況をV$LOCKビューで確認すると、次の通りです。

セッションC:
SQL> exec print_table('select * from v$lock where type = TX');
ADDR                         : C0000000ED2DC938
KADDR                        : C0000000ED2DCAC0
SID                          : 150
TYPE                         : TX
ID1                          : 1507368
ID2                          : 7763
LMODE                        : 6  <-- TXロックを排他モードで獲得中
REQUEST                      : 0
CTIME                        : 235
BLOCK                        : 1
----------------------------------------------------
ADDR                         : C0000000EE0A78D8
KADDR                        : C0000000EE0A78F8
SID                          : 148
TYPE                         : TX
ID1                          : 1507368
ID2                          : 7763
LMODE                        : 0
REQUEST                      : 6    <-- TX ロックを排他モードで待機中
CTIME                        : 226
BLOCK                        : 0

　上記SQLの結果で、
　セッションA(SID=150)がTXロック(ID1=1507368、ID2=7763)を排他モード(LMODE=6)で獲得した状態で、セッションB(SID=148)が同じID1、ID2に対してTXロックを排他モード(REQUEST=6)で獲得するために待機しているとのことが分かります。
　TXロックが保護するリソースはトランザクションであり、トランザクションの情報はUSN+SLOT+SQNの組み合わせで表現されます。
　TXロックのID1の値がこの組み合わせのUSN+SLOTまでの数字を表し、ID2の値がSQNを表します。
　このように、V$LOCKビューでは、どのようなトランザクションで競合が発生したのかを把握することができます。

　また、この例のトランザクションが待機しているenq:TX – row lock contention待機イベントのP2、P3パラメータの値はTXロックのID1、ID2値にそれぞれ結びつけられます。
　V$SESSION_WAITビューでセッションB(SID=148)の待機状況が確認できます。

SQL> exec print_table('select * from v$session_wait where sid = 148');
SID                           : 148
SEQ#                          : 341
EVENT                         : enq: TX - row lock contention
P1TEXT                        : name|mode
P1                            : 1415053318
P1RAW                         : 0000000054580006
P2TEXT                        : usn<<16 | slot
P2                            : 1507368
P2RAW                         : 0000000000170028
P3TEXT                        : sequence
P3                            : 7763
P3RAW                         : 0000000000001E53
WAIT_CLASS_ID                 : 4217450380
WAIT_CLASS#                   : 1
WAIT_CLASS                    : Application
WAIT_TIME                     : 0
SECONDS_IN_WAIT               : 30
STATE                         : WAITING
----------------------------------

複数セッションが同一一意キーで競合する場合

　複数のセッションが一意キーで競合する場合(モード4)や、主キーで競合する時もTXロックが発生します。
　セッションAがINSERTを実行した後、セッションBが一意キーで競合するようなINSERTを実行すると、セッションBは共有モードでTXロックを獲得するため待機します。
　この待機が起きた時は、enq:TX – row lock contention待機イベントが発生します。セッションBはセッションAがコミットを実行するかもしくはロールバックを実行するまで待機します。セッションAでコミットを実行すると、セッションBではORA-0001エラーが発生しますが、ロールバックを実行すると正常にINSERTされます。

セッションA:(SID=144) 一意キーを作成して、値をINSERTする。
SQL> create unique index test_idx on test(id);
SQL> insert into test values(1);

セッションB:(SID=148)同じ値をINSERTする。
SQL> insert int test values(1);
... Wait ...
 
セッションC:
SQL> insert int test values(1);
... Wait ...

セッションC:
SQL> exec print_table('select * from v$lock where sid in (148,144) 
and type = TX');
 
ADDR                          : C0000000ED2EE058
KADDR                         : C0000000ED2EE1E0
SID                           : 144
TYPE                          : TX
ID1                           : 917514
ID2                           : 9024
LMODE                         : 6     <-- 排他モードでTXロックを獲得中
REQUEST                       : 0
CTIME                         : 114
BLOCK                         : 1
--------------------
ADDR                          : C0000000ED2B6738
KADDR                         : C0000000ED2B68C0
SID                           : 148
TYPE                          : TX
ID1                           : 3407878  
ID2                           : 1985
LMODE                         : 6     <-- 排他モードでTXロックを獲得中
REQUEST                       : 0
CTIME                         : 102
BLOCK                         : 0
-----------------
ADDR                          : C0000000EE0A78D8
KADDR                         : C0000000EE0A78F8
SID                           : 148
TYPE                          : TX
ID1                           : 917514
ID2                           : 9024
LMODE                         : 0
REQUEST                       : 4    <-- 共有モードでTXロックを獲得するため待機

　V$LOCKビューの結果を確認してみると、先にINSERTを実行した144番セッションは一つのTXロック(ID1=917514、ID2=9024)を排他的に獲得しています。
　一方、後でINSERTを実行した148番セッションは、すでに一つのTXロック(ID1=3407878、ID2=1985)を排他的に獲得した状態で、さらに144番セッションが保持しているTXロックを共有モードで獲得するために待機しています。
　このような現象からOracleが一意キーを保護するためにどのような方法を使っているのかが推論できます。
　つまり、Oracleは表に行を追加した後、索引を追加しますが、索引を追加する時に一意制約に違反しているかどうかをチェックします。もし、同じキー値が存在していて、先に実行されたトランザクションがすでに終了している状態であればORA-0001エラーが発生します。しかし、まだ先に実行されたトランザクションが終了していなかった場合は、そのトランザクションが獲得しているTXロックを共有モードで獲得するために待機するようになります。

複数セッションがビットマップ索引で競合する場合(モード4)

　ビットマップ索引の競合によるTXロック発生を理解するには、ビットマップ索引の内部構造に関する知識が必要です。ビットマップ索引のリーフ・ノードはBツリー索引方式で管理されます。
　Bツリー索引のリーフ・ノードはソートされた形で索引エントリを保存し、個々の索引エントリは一つのROWIDを表します。
　従って、一意キー以外での索引エントリの間では競合が発生しません。一方、ビットマップ索引のリーフ・ノードは“カラムの値+ 開始ROWID + 終了ROWID + ビットマップ値”の形になります。
すなわち、一つのリーフ・ノードが広い範囲のROWIDを管理するようになります。表の行が更新される度にビットマップ索引に該当するカラム値に対して行が属したリーフ・ノードのビットマップを毎回新しく計算する必要があります。従って、同時に2つのセッションが同じリーフ・ノードに対してビットマップ演算を実行する場合、順序を保障するためTXロックを獲得しなければなりません。
　例えば、あるセッションがTXロックを排他モードで獲得し、ビットマップ演算が行われる際にコミットしなかった場合、他のセッションはこのトランザクションに対してTXロックを共有モードで確保するため、ビットマップ演算が終わるまで待機するようになります。一つのリーフ・ノードが広い範囲のROWIDを管理するのでTXロック競合が広い範囲で発生する可能性があります。

ビットマップ索引の競合でTXロックが発生した場合、enq:TX – row lock contention待機イベントで待機します。

セッションA: 
SQL> -- ビットマップ索引を作成
create table tx_bitmap_test
   (name1 char(1000), name2 char(1000), name3 char(1000));
create bitmap index tx_bitmap_test_idx 
    on tx_bitmap_test(name1, name2, name3);

セッションB: (SID = 148)
SQL> insert into tx_bitmap_test values('a', 'b', 'c');
1 row created.

セッションC: (SID = 159)
SQL> insert into tx_bitmap_test values('a', 'b', 'c');
.... Wait ....

2番目にINSERTを実行したセッションCは待機します。
この状態で、V$LOCKビューを確認します。

セッションA:
SQL>exec print_table('select * from v$lock where sid in (148,159) and 
type = TX');

ADDR                          : C000000074756E40
KADDR                         : C000000074756FC8
SID                           : 148
TYPE                          : TX
ID1                           : 4653100
ID2                           : 440
LMODE                         : 6    <-- TXロックを排他モードで獲得中
REQUEST                       : 0
CTIME                         : 118
BLOCK                         : 1
-----------------
ADDR                          : C00000007478E760
KADDR                         : C00000007478E8E8
SID                           : 159
TYPE                          : TX
ID1                           : 5046280
ID2                           : 444
LMODE                         : 6     <-- TXロックを排他モードで獲得中
REQUEST                       : 0
CTIME                         : 102
BLOCK                         : 0
-------------------
ADDR                          : C0000000750D2080
KADDR                         : C0000000750D20A0
SID                           : 159
TYPE                          : TX
ID1                           : 4653100
ID2                           : 440
LMODE                         : 0
REQUEST                       : 4    <-- TXロックを共有モードで獲得するため待機
CTIME                         : 102
BLOCK                         : 0
-----------------

　V$LOCKビューから、先にINSERTを実行する148番セッションは一つのTXロック(ID1=4653100、ID2=440)を排他モードで獲得していることが分かります。
　一方、後でINSERTを実行した159番セッションはすでに一つのTXロック(ID1=5046280、ID2=444)を排他モードで獲得したまま148番セッションが保持しているTXロックを共有モード(request=4)で獲得するために待機しています。
この結果は、一意キー競合による待機とまったく同一です。このような待機現象だけでは一意キー競合とビットマップ索引の競合の違いが区別できません。索引の正確な情報と同時に実行されたSQL文も考慮することだけが、正確な原因を見つけられる方法です。

待機パラメータと待機時間

待機パラメータ

　enq: TX – row lock contention 待機イベントのパラメータは次の通りです。

• P1 ： エンキュー情報
• P2 ： usn << 16 | slot
• P3 ： シーケンス

待機時間

　1回の待機で最大3秒まで待ちますが、TXロックを獲得するまで待機します。

チェックポイントとソリューション

トランザクションの管理

　同一行の更新によるTXロック競合は基本的にアプリケーション側の問題です。
　長時間実行されるUPDATEやDELETEコマンドはトランザクションが少ない時間帯に実行した方がいいです。
　または、UPDATEやDELETEコマンドそのものの性能を改善することも一つの方法です。
　特に大量データを更新することはTXロックの問題だけでなく、別の数多くの性能問題を引き起こす可能性があります。
　大量のUPDATEを避ける方法は次の通りです。

• 　old_table表をコピーし、new_table表を作成して更新内容を保存します。
  例えば、“CREATE TABLE new_table AS SELECT id, name, register_date,DECODE(class,1,’A’,2,’B’)… FROM old_table”のようなコマンドを利用します。
• 　新しく作成したnew_table表にold_table表と同じく索引などを作成します。
• 　既存のold_table表をDROPして、new_table表をold_tableにリネームします。　

上記の作業を実行する時は、NOLOGGINGとPARARELLオプションを合わせて使用すると、より早く実行することができます。

一意キーの管理

　一意キーの競合によるTXロックはあくまでもアプリケーション側の問題です。
　一意キーを作成するために計算式を使うか、既存の表でMAX値を抽出する方法などを使う場合、
　一意キーの競合によるTXロックはいつでも発生する可能性があります。最善の解決策はシーケンスを使って一意キーを作成することです。

ビットマップ索引の効率的な活用

ビットマップ索引は、読み取り作業は頻繁で、書き込み作業は少ない表に対してDSSのようなSELECTの性能を
最大化するために考案されたものです。DMLが頻繁な表に対してビットマップ索引をむやみに使うのはかなりリスクが高い方法です。
行が更新される度にビットマップ値を計算しなければならないため、DMLの性能が低下します。
しかも同時に複数のセッションがDMLを実行する場合には、過度なTXロックが発生するようになります。
もし、DMLが頻繁に行われる表でDSSのSELECTの性能を保障したい場合、
ビットマップ索引よりはマテリアライズド・ビューのような機能を推奨します。

豆知識

分析事例

同一行の更新による性能低下現象

同時ユーザー数が多いOLTP環境ではTXロックによる性能低下現象が発生する場合が多いです。
MaxGaugeを活用してTXロックによる性能低下の原因を解析します。

性能低下区間の確認

14時05分～14時14分間にアクティブ・セッション数が急増していることが分かります。

■「Active Session」の推移グラフ

待機イベントの検出および分析

　問題区間でアクティブ・セッション数が急増した原因を分析するために、[Stat/Event/Ratio]画面で問題が発生した時点(14時11分)の待機イベントを調査すると、下図のようにenqueue待機イベントが最も高い割合を占めていることが分かります。

　問題区間のアクティブ・セッションのリストを確認すると、下図のようにほとんどのセッションが排他モードのTXロックによってenqueue待機イベントを待機しています。

待機イベント発生原因の調査

　「Lock Tree」で、TXロックを待機しているセッションを確認します。
　1178番セッションがロックを保持したセッションですが、他のセッションは排他モードのTXロックを獲得するために待機しています。
　すなわち、ロックを保持したセッションが長時間もTXロックを解除しないため、enqueue待機イベントが多く発生しています。

セッションおよびSQL分析

ロックを保持している1178番セッションが実行したSQL履歴を検索すると、下図の通りです。

　ロックを保持したセッションはSQL文(<456130>)を実行した後、SQL文(<4618474>)を実行しました。

　最後のSQL文(<4618474>)を実行した後はそのまま、TXロックのenqueue待機イベントを待機します。つまり、ロックを保持したセッションはある行を更新した後、コミットを実行していない状態で、他の作業を実行している状況です。
　これによって同じ表の同じ行を更新しようとする他のセッションはTXロックでenqueue待機イベントを待機していました。

　表の行を更新した後、適切な時点でコミットをしなかったことが、enqueue待機イベントが発生した根本的な原因です。
　行を更新した後には必ずコミットを実行するようにアプリケーションのロジックを改善することが、この問題を解決する方法です。

結論

性能低下現象(enqueue待機イベントの発生によるアクティブ・セッション数増加)
　　　　　　　　　　　　　　　↓
行を更新した後、コミットを実行しなかったためTXロックが発生
　　　　　　　　　　　　　　　↓
行を更新した後は、必ずコミットを実行し、待機を解消する必要がある

一意キー競合による性能低下現象

　問題が発生したインスタンスのアクティブ・セッションの推移はエンキュー指標の推移と一致します。
　従って、アクティブ・セッションが90前後でセッション数を維持していたことは、エンキュー待機の発生と関連性があると考えられます。
　アクティブ・セッションのリストからでもセッションがTXエンキューを共有モードで待機していることが分かります。
　これらのセッションは全てINSERT文を実行しています。

　ロック詳細を確認すると、待機中のセッションは皆同じ表に対して作業中であり(Object Id：65561)、これらのセッションは共有モードで待機していることが分かります。

　セッションが実行しているSQL文はINSERT INTO B (B_id,…)で、B表のB_idは主キー、すなわちユニークなキーです。
　例えば、下記のEmp表のEmpnoカラムに一意索引が作成されています。

SID=21 INSERT INTO Emp (Empno) VALUES (:b1);
SID=24 INSERT INTO Emp (Empno) VALUES (:b1); 

　同じ値を入力した場合、24番セッションは21番セッションがコミットもしくはロールバックを実行するまでTXエンキューを共有モードで待機します。
　21番セッションがコミットをすると、24番セッションではORA-00001(unique constraint (%s.%s) violated)が発生しますが、ロールバックを実行すると、正常にINSERTされます。

　このように一意制約のカラムに同じ値をINSERTした場合、先に実行したセッションがトランザクションを終了するまでTXエンキューが発生します。
　この場合は、シーケンスを利用して一意制約のカラムにINSERTを実行するように変更することで解決できます。

解決策

　シーケンスを使用するようINSERT文を変更します。

①シーケンスを使用する下記の表があります。

CREATE TABLE MYTABLE (ID NUMBER, NAME VARCHAR2(20)); 

②CREATE SEQUENCEコマンドで、SEQ_IDというシーケンスを作成します。

  CREATE SEQUENCE SEQ_ID INCREMENT BY 1 START WITH 10000 cache 1000;
      -- INCREMENT BY 1 : 増加値は1
      -- START WITH 10000 :  10000から増加

③シーケンのNEXTVALで表にデータを入力します。

INSERT INTO MYTABLE VALUES( SEQ_ID.NEXTVAL, 'アスター太郎');
      -- NEXTVAL : 現在シーケンスの値は次の値を返します。