概要
デッドロックはデータベースにおける基本的なものだと思います。ググるといろいろと情報が出てくると思うのですが、最近データベースを触っていないこともあり、リハビリとしてOracle Databaseを使用したデッドロックの再現をしてみようと思います。また、ロックの取得状態や関連統計についても書いておきます。
環境
- Oracle Database 19c Release 3
- Oracle Linux 7.7
ここでやりたいこと
マニュアル にも記載されている、おそらく最も一般的なシナリオだと思われる**「2つのトランザクションがたすき掛けで同じレコードを更新」**した場合のデッドロックについて確認していこうと思います。イメージとしては以下のような流れです。
| 時刻 | セッション1 | セッション2 | 状態 |
|---|---|---|---|
| t1 | レコード1を更新 | - | セッション1がレコード1の行ロック獲得 |
| t2 | - | レコード2を更新 | セッション2がレコード2の行ロック獲得 |
| t3 | レコード2を更新 | - | セッション1はレコード2の行ロック開放を待機 |
| t4 | - | レコード1を更新 | セッション2はレコード1の行ロック開放を待機（デッドロック発生） |
やってみる
まずは適当な検証用テーブルとデータを準備します。
検証用テーブルの作成
create table test_tab (id number, value varchar2(25));
検証用データの登録
insert all
into test_tab values(1, 'HOGE_1')
into test_tab values(2, 'HOGE_2')
select * from dual;
commit;
テーブルの状態は以下のような感じです。確認で使用するため、オブジェクト番号なども併せてSELECTしています。
select
id,
value,
dbms_rowid.rowid_object(rowid) data_object_id,
dbms_rowid.rowid_block_number(rowid) block_number,
dbms_rowid.rowid_row_number(rowid) row_number
from test_tab
order by id;
ID VALUE DATA_OBJECT_ID BLOCK_NUMBER ROW_NUMBER
---------- ------------------------- -------------- ------------ ----------
1 HOGE_1 23374 132 0
2 HOGE_2 23374 132 1
これで準備は完了です。ここからたすき掛けでの更新を行っていきますが、その前に検証で使用する2つのセッションのSIDを以下のSQLで確認しておきます。
select sys_context('USERENV','SID') sid from dual;
今回は以下となります。
| - | セッション1 | セッション2 |
|---|---|---|
| SID | 42 | 39 |
（時刻t1）セッション1でid=1のレコードを更新
update test_tab set value='updated by session 1' where id=1;
select * from test_tab;
ID VALUE
---------- -------------------------
1 updated by session 1
2 HOGE_2
（時刻t2）セッション2でid=2のレコードを更新
update test_tab set value='updated by session 2' where id=2;
select * from test_tab;
ID VALUE
---------- -------------------------
1 HOGE_1
2 updated by session 2
（時刻t3）セッション1からid=2のレコードを更新
update test_tab set value='updated by session 1' where id=2;
★ロック開放待ちで待機
既にid=2のレコードは時刻t2にセッション2（SID=39）で更新されているため、セッション1（SID=42）からは行ロックの獲得が行えず、待機状態になります。
このとき、どのような種類のロックが取得／要求されているかの一覧はv$lockから確認することができます。今回のセッションID、表ロック（TM）と行ロック（TX）のみに絞ってSELECTしてみます。
select
sid,
type,
id1,
id2,
lmode,
request,
block
from v$lock
where sid in (42,39) and type in ('TX', 'TM')
order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
39 TM 23374 0 3 0 0
39 TX 327706 478 6 0 1 <-★1
42 TM 23374 0 3 0 0
42 TX 196633 677 6 0 0
42 TX 327706 478 0 6 0 <-★2
ということで、今回実行している両セッションにおいて、表ロック（TM）と行ロック（TX）が獲得されています。(★1)の箇所はBLOCK列が1となっているため、他セッションをブロックしていることがわかります。また、(★2)の箇所はREQUEST列が6となっているため。排他モードでのロック獲得を要求していることがわかります。そのため、(★1)が時刻t2、(★2)が時刻t3における操作に対応したロックとなります。
なお、ID1, ID2の意味はロックモード(LMODE)で異なりますが、これらはv$lock_typeから確認できます。
select
type,
name,
id1_tag,
id2_tag
from v$lock_type
where type in ('TX', 'TM')
order by 1;
TYPE NAME ID1_TAG ID2_TAG
----- --------------- ------------------------------ ------------------------------
TM DML object # table/partition
TX Transaction usn<<16 | slot sequence
したがって、表ロック（TM）のID1からオブジェクト番号がわかるため、test_tab表に対して取得されたロックであることがわかります。行ロック（TX）のID1, ID2については更新を行っているトランザクション情報を示しているようなので、v$transactionからアクティブトランザクションと紐づけられそうです。
また、要求されているロックや競合に関する同様の情報はDBA_WAITERSからも確認することができます。
select
waiting_session,
holding_session,
lock_type,
mode_held,
mode_requested,
lock_id1,
lock_id2
from dba_waiters
where waiting_session=42;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2
--------------- --------------- --------------- ---------- ---------- ---------- ----------
42 39 Transaction Exclusive Exclusive 327706 478
最後に、セッション1（SID=42）の情報をv$sessionから確認してみます。
select
sid,
event,
state,
row_wait_obj#,
row_wait_block#,
row_wait_row#,
round(wait_time_micro/1000000,2) as "WAIT TIME[S]",
blocking_session
from v$session
where sid=42;
SID EVENT STATE ROW_WAIT_OBJ# ROW_WAIT_BLOCK# ROW_WAIT_ROW# WAIT TIME[S] BLOCKING_SESSION
---------- ----------------------------------- ---------- ------------- --------------- ------------- ------------ ----------------
42 enq: TX - row lock contention WAITING 23374 132 1 444.57 39
待機イベント"enq: TX - row lock contention"により待機しています。ROW_WAIT_XXX列から、ロック獲得待ちの対象となっているオブジェクト番号／ブロック／行の情報がわかるため、冒頭のDBMS_ROWIDから確認した結果と照らし合わせることで、test_tab表のid=2の行における更新でロック競合が発生していることがわかります。
（時刻t4）セッション2からid=1のレコードを更新
ここでの更新によりデッドロックが発生します。デッドロックの発生は統計値"enqueue deadlocks"に反映されるため、デッドロック発生前後での値の変化もv$sysstatから確認しておきます。
select
statistic#,
name,
class,
value
from v$sysstat
where name = 'enqueue deadlocks';
STATISTIC# NAME CLASS VALUE
---------- -------------------- ---------- ----------
44 enqueue deadlocks 4 0
デッドロックはまだ発生していないため、この時点では0となっています。
更新をおこない、デッドロックを発生させます。
update test_tab set value='updated by session 2' where id=1;
★ロック開放待ちで待機
こちらも同様に、既にid=1のレコードは時刻t1にセッション1（SID=42）で更新されているため、セッション2（SID=39）からは行ロックの獲得が行えず、待機状態になります。この操作によって、2つのセッションからたすき掛けでの更新が行われたたためデッドロックとなりますが、Oracle Databaseではデッドロックが検知されるとエラーとともに自動的に解消（ロールバック）されます。セッション1では時刻t3における処理がロールバックされ、ORA-60エラーによりデッドロック状態が解消されたことが通知されます。
SQL> update test_tab set value='updated by session 1' where id=2;
update test_tab set value='updated by session 1' where id=2
*
行1でエラーが発生しました。:
ORA-00060: リソース待機の間にデッドロックが検出されました。
ということで、デッドロックを無事に再現できました。この状態でセッション1側をcommit/rollbackすることで、セッション2での処理を進めることができます。
最後に、統計値"enqueue deadlocks"を確認しておきます。
select
statistic#,
name,
class,
value
from v$sysstat
where name = 'enqueue deadlocks';
STATISTIC# NAME CLASS VALUE
---------- -------------------- ---------- ----------
44 enqueue deadlocks 4 1
デッドロックの発生により統計値がインクリメントされており、デッドロックの発生を確認することが可能です。
参考
- Oracle® Database データベース概要 19c - Oracle Databaseのロック・メカニズムの概要
- Oracle® Database データベース概要 19c - ロックとデッドロック
- Oracle® Database データベース・リファレンス 19c - 8.40 V$LOCK
- Oracle® Database データベース・リファレンス 19c - 6.103 DBA_WAITERS
- Oracle® Database データベース・リファレンス 19c - E.2 統計情報の説明