運用中に起きたDBデッドロックの話

May 27, 2022

ソフトウェア開発部バックオフィス基盤グループの廖です。本番運用中にRDBのマイグレーションが行われた際に不意に起きたデッドロックの失敗事例について紹介し、ここで得られた教訓を活かせられたらと思います。

前提

今回紹介する事例について、RDBはPostgreSQL、マイグレーションツールはFlywayを使っています。マイグレーションが必要な時に、マイグレーション用SQLファイルを作成して本番に適用しています。 DBに適用する際、マイグレーションファイルごとにトランザクションとして実行されます。

また、PostgreSQLにおいては、DDLの実行前に暗黙のコミットをするOracleやMySQLなどと違って、DDLはトランザクションとして適用されて、途中で失敗したらロールバックされるのが特徴の一つです。

失敗事例1

事象

どこにも参照されてないテーブルの削除SQLのみが書かれているマイグレーションファイルに対して、本番でマイグレーションが実行される時に、デッドロックが起きました。

マイグレーションSQL：

DROP TABLE table_a;

ログ：

ERROR:  deadlock detected at character 750
DETAIL:  Process 756 waits for AccessShareLock on relation 28885 of database 16400; blocked by process 26402.
        Process 26402 waits for AccessExclusiveLock on relation 28865 of database 16400; blocked by process 756.
        Process 756:
            SELECT ...
            FROM table_b JOIN table_c ON ... JOIN table_d ON ...
            WHERE ...
        Process 26402:
        DROP TABLE table_a

調査

まずPostgreSQLのロック関連のドキュメントを見てみましょう。

ACCESS EXCLUSIVE
...
DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL、（CONCURRENTLYなしの）REFRESH MATERIALIZED VIEWコマンドによって獲得されます。 ALTER TABLEの多くの形式もこのレベルでロックを獲得します。 これはまた、明示的にモードを指定しないLOCK TABLE文のデフォルトのロックモードです。

ログから見たら、テーブル削除SQLでデッドロックを起こしたはずです。DROP TABLEでtable_aのAccessExclusiveLockを獲得しますが、削除対象のtable_a自体はどこからも参照されてない状態でした。なぜtable_aの削除は全く関係ないように見えるSELECT文の実行でデッドロックを起こしたかは最初はどうしても理解できませんでした。

次に、table_aを追加する際のマイグレーションファイルで書かれているSQLを見てみましょう。

ALTER TABLE table_a
    ADD FOREIGN KEY (table_c_id)
    REFERENCES table_c (id)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT
;

ALTER TABLE table_a
    ADD FOREIGN KEY (table_d_id)
    REFERENCES table_d (id)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT
;

外部キーのせいかとなんとなくピンと来ました。エラー事象とテーブルの定義からすると、テーブル削除の実行で、外部キー制約も外す必要があり、そこで、外部キーの参照テーブルに対してもAccessExclusiveLockを獲得したように見えました。 PostgreSQLの公式ドキュメントには外部キー制約削除時のロックに関する情報が見つかってませんが、Stackoverflowで関連のQ&Aがあります。https://stackoverflow.com/questions/32145189/avoid-exclusive-access-locks-on-referenced-tables-when-dropping-in-postgresql

Deletion of those triggers (when droping the foreign key, or the whole table) requires ACCESS EXCLUSIVE lock on those tables. This lock conflicts with every other lock!

検証

Stackoverflowの回答でも間違っている可能性があるので、外部キー制約の削除も含めてテーブルを削除する時にロックの獲得情報について検証します。仮に下記テーブルが存在するとします。

create table table_c
(
    id   bigserial not null
        constraint table_c_pk
            primary key,
    name varchar(100)
);

create table table_a
(
    id          bigserial not null
        constraint table_a_pk
            primary key,
    table_c_id bigint    not null
        constraint table_a_table_c_id_fk
            references table_c
            on update restrict on delete restrict,
    code        varchar(100)
);

下記のトランザクションを実行し、外部キー削除の時のロックモードを検証します。

BEGIN;
-- table_cのロック状態を確認。この時の検索結果が0行
SELECT locktype, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation = 'table_c'::regclass;

-- 外部キー制約を削除
ALTER TABLE table_a DROP CONSTRAINT table_a_table_c_id_fk;

-- 再度table_cのロック状態を確認。
-- 検索結果：
-- locktype:relation, mode:AccessExclusiveLock
SELECT locktype, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation = 'table_c'::regclass;

END;

これにより、外部キー制約の削除時に参照テーブルもAccessExclusiveLockを獲得していることが判明しました。

デッドロックの発生経緯

上記の調査と検証から、下記のAとBのようなトランザクションが同時に実行したら、デッドロックが発生します。

前提条件: table_aにはtable_cとtable_dを参照している外部キーを持っています。時系列の発生順： ※以下のトランザクションAはDROP TABLE table_aの処理、トランザクションBはSELECT * FROM table_b JOIN table_c ON ... JOIN table_d ON ...の処理とします。

トランザクションA: table_cへの外部キーが削除され、table_cへのAccessExclusiveLockが獲得されたトランザクションB: table_bとtable_dへのAccessShareLockが獲得された
トランザクションA: table_dへの外部キーの削除を行おうとしたが、table_dへのAccessExclusiveLockを獲得できず待ち状態になったトランザクションB: table_cへのAccessShareLockが獲得できず待ち状態になった

結果、上記AとBはお互いのロック(AccessExclusiveLockとAccessShareLockは競合関係)を待っている状態に陥り、デッドロックが発生しました。

参照されてないテーブルでも安易にDROP TABLEするのは危ないことだとわかりました。

失敗事例2

事象

マイグレーションSQL：

...
CREATE INDEX xxx ON table_e(xxx);

UPDATE table_e
SET ...
WHERE ...;

今度も同じくマイグレーションの時にデッドロックが起きました。

調査

上記のSQLから、本番でインデックスを作成する時にうっかりCONCURRENTLY(ロックを獲得しないモード)がつけられてないのが原因ではないかとの推測がありました。

DBログ:

ERROR:  deadlock detected
DETAIL:  Process 7025 waits for ShareLock on transaction 58465859; blocked by process 26629.
	Process 26629 waits for ShareLock on transaction 58465858; blocked by process 5548.
	Process 5548 waits for ShareLock on transaction 58465862; blocked by process 7015.
	Process 7015 waits for ShareLock on transaction 58465861; blocked by process 7025.
    Process 7025:
	INSERT INTO table_e ...
	Process 26629:
	INSERT INTO table_e ...
...

該当インデックスが作成された時にちょうど定期ジョブで該当テーブルへ大量なレコード作成処理が実行されたことも上記のログに反映されています。

また、PostgreSQL公式ドキュメントにも運用状態のDBにてインデックス作成の影響が書かれています。

他のトランザクションはテーブルを読み取ることはできますが、対象テーブル内の行を挿入、更新、削除しようとすると、インデックス作成が完了するまでブロックされます。 実行中の運用状態のデータベースシステムの場合、これは重大な影響を与える可能性があります。 非常に大規模なテーブルに対するインデックス作成は何時間もかかることがあり得ます。

https://www.postgresql.jp/document/11/html/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

デッドロックの発生経緯

時系列の発生順：

トランザクションA: CREATE INDEX xxx ON table_e(xxx)の実行でtable_eへのShareLockが獲得された
トランザクションB: table_eへのinsert処理を行おうとして、table_eへのShareLockの解放を待つ
トランザクションA: table_eへのupdate処理を行おうとして、Bのinsert対象の行へのロックの解放を待たなくてはいけない

CREATE INDEXだけの問題ではなく、同じトランザクション内でDDLもDMLも混在していて、かつ同じテーブルへの大量レコードの更新があることがさらに問題を起こしやすくなります。

教訓

PostgreSQL+Flywayの組み合わせでDBマイグレーションを行う時に、下記の注意点と対策例をまとめてみました。

注意点

DROP TABLEする前に、対象テーブルが参照されてないか、対象テーブル関連の各種制約、インデックス、トリガーなどが存在しているかをよく確認する
外部キー制約によく気を付ける
同じマイグレーショントランザクション内でなるべく下記のようなことを避ける
- DMLとDDLの混在
- 複数テーブルへのDDL修正、データ更新
- 同じテーブルへの大量レコード更新大規模レコード更新が必要でしたら、本番の処理へ影響を少なくするために、なるべく分割して実行する
マイグレーションファイルでDML（データの更新、登録、削除など）をマイグレーションファイルで定義する必要性があるかを考える（本番DBでDMLを手動管理・投入することと比較）
テーブルへの変更が発生する際に、変更の目的/用途を考えてマイグレーションファイルを分割した方がいい

上記は一部の例ですが、本番DBが並行運用中の状態で、マイグレーション/更新などを行う際は、本番で発生しそうなDB処理への影響範囲の考慮が大事です。

デッドロックを起こさない対策例

DROP TABLEする際に、対象テーブルに何かの制約、トリガーなどが存在する場合、マイグレーションファイルを分けて先に制約、トリガーを削除してから、テーブル削除を行う
DDLは1ファイルごとに書く
DMLとDDLを同じファイルに混在させない
マスター系対象以外のDML操作や大量データ更新のDMLに関しては、なるべくFlyway以外の方法で適用する。どうしてもFlywayで適用する場合、対象テーブルごとに、用途ごとにファイルを分ける
大量レコード更新を行う場合、Flyway以外の方法で適用しても、デッドロックを起こすリスクがあるので、なるべく更新範囲を分割して逐次に適用する

We're Hiring!

📢

Mobility Technologies ではともに働くエンジニアを募集しています。

興味のある方は採用ページも見ていただけると嬉しいです。

Twitter @mot_techtalk のフォローもよろしくお願いします！