Oracle Databaseでデッドロック

# 概要
デッドロックはデータベースにおける基本的なものだと思います。ググるといろいろと情報が出てくると思うのですが、最近データベースを触っていないこともあり、リハビリとしてOracle Databaseを使用したデッドロックの再現をしてみようと思います。また、ロックの取得状態や関連統計についても書いておきます。

# 環境
* Oracle Database 19c Release 3
* Oracle Linux 7.7

# ここでやりたいこと
[マニュアル](https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/data-concurrency-and-consistency.html#GUID-C1971E9B-849A-4634-9575-4F8FAD697750) にも記載されている、おそらく最も一般的なシナリオだと思われる**「2つのトランザクションがたすき掛けで同じレコードを更新」**した場合のデッドロックについて確認していこうと思います。イメージとしては以下のような流れです。

|時刻| セッション1 | セッション2 | 状態 |
|:-:|:-:|:-:|:-:|
| t1  |レコード1を更新   | -  |セッション1がレコード1の行ロック獲得|
| t2  | -  | レコード2を更新  |セッション2がレコード2の行ロック獲得|
| t3  | レコード2を更新  | -  |セッション1はレコード2の行ロック開放を待機|
| t4  | - | レコード1を更新 |セッション2はレコード1の行ロック開放を待機（デッドロック発生）|

# やってみる
まずは適当な検証用テーブルとデータを準備します。

```sql
検証用テーブルの作成
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しています。

```sql
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で確認しておきます。

```sql
select sys_context('USERENV','SID') sid from dual;
```

今回は以下となります。

|-| セッション1 | セッション2 |
|:-:|:-:|:-:|
|SID| 42  |  39 |


###（時刻t1）セッション1でid=1のレコードを更新

```sql
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のレコードを更新

```sql
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のレコードを更新

```sql
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してみます。

```sql
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から確認できます。

```sql
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からも確認することができます。

```sql
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から確認してみます。

```sql
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から確認しておきます。

```sql
select
  statistic#,
  name,
  class,
  value
from v$sysstat
  where name = 'enqueue deadlocks';
STATISTIC# NAME                      CLASS      VALUE
---------- -------------------- ---------- ----------
        44 enqueue deadlocks             4          0
```

デッドロックはまだ発生していないため、この時点では0となっています。
更新をおこない、デッドロックを発生させます。

```sql
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
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"を確認しておきます。

```sql
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のロック・メカニズムの概要](https://docs.oracle.com/cd/F19136_01/cncpt/data-concurrency-and-consistency.html#GUID-AD0CEE83-2F33-4906-94E1-3D1022924C63)
* Oracle® Database データベース概要 19c - [ロックとデッドロック](https://docs.oracle.com/cd/F19136_01/cncpt/data-concurrency-and-consistency.html#GUID-C1971E9B-849A-4634-9575-4F8FAD697750)
* Oracle® Database データベース・リファレンス 19c - [8.40 V$LOCK](https://docs.oracle.com/cd/F19136_01/refrn/V-LOCK.html#GUID-87D76889-832C-4BFC-B8B0-154A22721781) 
* Oracle® Database データベース・リファレンス 19c - [6.103 DBA_WAITERS](https://docs.oracle.com/cd/F19136_01/refrn/DBA_WAITERS.html#GUID-2BFB9E43-BCE8-4E3B-AC94-A4B633ADB998)
* Oracle® Database データベース・リファレンス 19c - [E.2 統計情報の説明](https://docs.oracle.com/cd/F19136_01/refrn/statistics-descriptions-2.html#GUID-2FBC1B7E-9123-41DD-8178-96176260A639)