カテゴリー: SQL

PostgreSQLでDROP TABLEを行うときにデッドロックが発生するという事象がありました。私自身が遭遇したわけではないですが、Liquibaseで使用していないテーブルをDROP TABLEしようとしたときに、デッドロックが発生したようです。

そんなわけはないと思いましたが、どうやら発生するケースがあるようです。

以下の記事を参考に調査しました。この記事ではFlywayを使っていますが、Liquibaseでも同様の事象が発生しますし、こういったマイグレーションツールを使っていなくてもBEGIN〜END内でDROP TABLEするケースでは発生し得る事象になります。

外部キー制約を外すときに参照先のテーブルに対してAccessExclusiveLockを獲得している

問題の根元は外部キー制約を外すときに参照先のテーブルに対してAccessExclusiveLockを獲得している点あるようでした。これを確認するために、以下の2つのテーブルを作成します。

親テーブルであり、外部キー制約における参照先のテーブルにあたるcompaniesテーブルを作成します。

CREATE TABLE companies (
    id SERIAL NOT NULL,
    name VARCHAR,
    PRIMARY KEY (id)
);

次に子テーブルであり、外部キー制約を設定するusersテーブルを作成します。

CREATE TABLE users (
    id SERIAL NOT NULL,
    name VARCHAR,
    company_id INTEGER NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (company_id) REFERENCES companies (id)
);

このDDLによって作成される外部キーの名前を確認します。

SELECT
    table_name, constraint_name
FROM
    information_schema.table_constraints
WHERE
    table_schema = 'public'
    AND
    constraint_type = 'FOREIGN KEY';

このSQLを実行すると、以下のような結果が得られます。

外部キーの名前はあとで外部キー制約をDROP CONSTRAINTするときに使用します。

外部キー制約を外すときにAccessExclusiveLockを獲得していることを確認する

では、外部キー制約を外すときにAccessExclusiveLockを獲得していることを確認しましょう。

確認のために、トランザクション内で実行するようにします。これは、今回遭遇したケースではトランザクション内でDDLを実行していたため、同じようにするためにトランザクションを作成しています。

BEGIN;
SELECT locktype, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation = 'companies'::regclass;

トランザクションを作成した時点では、親テーブルのcompaniesテーブルに対してロックは獲得されていません。

次に外部キー制約をDROP CONSTRAINTしてからもう一度ロックが獲得されているかを確認してみます。

ALTER TABLE users DROP CONSTRAINT users_company_id_fkey;
SELECT locktype, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation = 'companies'::regclass;

今度はAccessExclusiveLockが獲得されていることを確認できました。

公式マニュアルによると、AccessExclusiveLockはFOR UPDATEがないSELECT文もブロックするため、オンライン稼働中に外部キーを削除するとデッドロックが発生する場合があります。

例えば、外部キーを2つ持つ以下のテーブルを考えてみます。

CREATE TABLE companies (
    id SERIAL NOT NULL,
    name VARCHAR,
    PRIMARY KEY (id)
);

CREATE TABLE organizations (
    id SERIAL NOT NULL,
    name VARCHAR,
    company_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY (company_id) REFERENCES companies (id)
);

CREATE TABLE users (
    id SERIAL NOT NULL,
    name VARCHAR,
    company_id INTEGER NOT NULL,
    organization_id INTEGER  NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (company_id) REFERENCES companies (id),
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
);

CREATE TABLE products (
    id SERIAL NOT NULL,
    name VARCHAR,
    company_id INTEGER NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (company_id) REFERENCES companies (id)
);

このとき、2つのトランザクションを考えます。

• トランザクション①
  productsテーブルをDROP TABLEするトランザクションです。一応、この例ではproductsテーブルはまだ使われていないテーブルということで、オンライン稼働中にDROP TABLEしても影響はないだろうという考えのもとでオンライン稼働中に実行しています。
• トランザクション②
  usersテーブルにcompaniesテーブルとorganizationsテーブルをJOINして検索するトランザクションです。これはオンラインで頻繁に実行されるSQLです。

次のような流れで実行されるとデッドロックが発生します。

1. トランザクション①でcompaniesテーブルへの外部キーが削除され、companiesテーブルへのAccessExclusiveLockが獲得されます。
2. トランザクション②でusersテーブルとorganizationsテーブルに対するAccessShareLockが獲得されます。
3. トランザクション①でorganizationsテーブルへの外部キーを削除しようとしますが、トランザクション②がすでにorganiztionsテーブルに対するAccessShareLockを獲得しているため、トランザクション①はAccessExclusiveLockが獲得できずに待ちになります。
4. トランザクション②はcompaniesテーブルに対するAccessShareLockを獲得使用としますが、トランザクション①がAccessExclusiveLockを獲得しているため、AccessShareLockが獲得できずに待ちになります。（ここでデッドロックが発生）

発生パターンは他にも考えられますが、使われていないテーブルであっても外部キー制約を持つテーブルの削除を行うだけでデッドロックが発生するという点が最も注意すべき点だと思います。

ではどうすればよいか

参考にした記事とも重複しますが、以下のような対策を行うのがよいでしょう。

1. 外部キー制約をもったテーブルをDROP TABLEする際には、複数のリソースを扱わないようにトランザクションを分ける（Liquibaseの場合は外部キー制約を解除するDDL（1外部キー制約1changelog）とDROP TABLEをするDDLのchangelogをわける）
2. DDLとDMLを1つのトランザクション内で実行しない（Liquibaseならchangelogを分ける）
3. 大量更新（登録・更新・削除）を行うテーブルを参照している場合、その処理が行われているタイミングを避けて実行する

外部キー制約を使っているプロジェクトの場合、使っていないテーブルだからといってテーブル定義を綺麗にするために安易にDROP TABLE→CREATE TABLEによる再作成を選択しないように慎重に検討すべきです。

どうしてもDROP TABLEをしたい場合は1DDL1トランザクションになるように分けて、先に外部キー制約を解除し、それからテーブルをDROP TABLEするようにした方がよいでしょう。

最後に

最後にデッドロックが発生した場合にどのように対応していくかについて記載しておきます。

以下のSQLを実行すると、ロックが発生しているトランザクション一覧が表示されます。

SELECT
    pg_stat_activity.pid,
    pg_stat_activity.usename,
    pg_stat_activity.query,
    pg_class.relname AS table_name,
    pg_locks.locktype,
    pg_locks.mode,
    pg_locks.granted,
    pg_stat_activity.state,
    pg_stat_activity.application_name
FROM
    pg_stat_activity
JOIN
    pg_locks
    ON pg_stat_activity.pid = pg_locks.pid
LEFT JOIN
    pg_class
    ON pg_class.oid = pg_locks.relation
WHERE
    pg_locks.locktype = 'relation'
    AND
    pg_class.relname NOT LIKE 'pg_%'
ORDER BY
    pg_stat_activity.pid;

application_nameなどをもとに、どのpidが問題になっているかを特定し、

SELECT pg_cancel_backend(<killしたいpid>);

で実行できます。例えば、pidが62なら

SELECT pg_cancel_backend(62);

のように書きます。

これでもkillできない場合は、

SELECT pg_terminate_backend(<killしたいpid>)

でkillします。