PostgreSQLでテーブルをdropしようとしてデッドロックした
福地春喜ひとつのテーブルを1文でdropしようとしたところデッドロックした。
begin;
drop table table_c;
commit;
テーブル構成は次のような具合。外部キー制約がある。
-
table_aとtable_bをtable_cが参照する
create table table_a (
id serial primary key
);
create table table_b (
id serial primary key
);
create table table_c (
id serial primary key,
a_id integer not null references table_a(id),
b_id integer not null references table_b(id)
);
次の系列でクエリを実行する。
- プロセス1:
table_cをdropする - プロセス2:
table_aとtable_bをselectする
| プロセス1 | プロセス2 |
|---|---|
begin; |
|
select * from table_a; |
|
begin; |
|
drop table table_c; |
|
select * from table_b; |
まずプロセス2でデッドロックの検知が起きた。
ERROR: Process 4246 waits for AccessShareLock on relation 1476979 of database 16385; blocked by process 4190.
Process 4190 waits for AccessExclusiveLock on relation 1476971 of database 16385; blocked by process 4246.deadlock detected
ERROR: deadlock detected
SQL state: 40P01
Detail: Process 4246 waits for AccessShareLock on relation 1476979 of database 16385; blocked by process 4190.
Process 4190 waits for AccessExclusiveLock on relation 1476971 of database 16385; blocked by process 4246.
Hint: See server log for query details.
Character: 15
プロセス1はプロセス2の結果を待ってテーブルをdropした。
DROP TABLE
Query returned successfully in 10 secs 523 msec.
selectする順番が逆だと違った結果になった。
- プロセス1:
table_cをdropする - プロセス2:
table_bとtable_aをselectする（先と順番が逆）
| プロセス1 | プロセス2 |
|---|---|
begin; |
|
select * from table_b; |
|
begin; |
|
drop table table_c; |
|
select * from table_a; |
|
commit; |
プロセス1はプロセス2のcommitを待ってテーブルをdropした。
DROP TABLE
Query returned successfully in 15 secs 169 msec.
手動で実行する分にはプロセス1でデッドロックを検知するには至らなかった。
しかし、プロセス2のクエリを少し修正して継続的に実行し続ける中でプロセス1のクエリを実行したところ、プロセス1でデッドロックの検知が起きた。
begin;
select * from table_a;
select pg_sleep(0.01); -- 負荷のエミュレーション
select * from table_b;
commit;
プロセス1で検知したデッドロック:
ERROR: Process 4751 waits for AccessExclusiveLock on relation 1477212 of database 16385; blocked by process 36202.
Process 36202 waits for AccessShareLock on relation 1477220 of database 16385; blocked by process 4751.deadlock detected
ERROR: deadlock detected
SQL state: 40P01
Detail: Process 4751 waits for AccessExclusiveLock on relation 1477212 of database 16385; blocked by process 36202.
Process 36202 waits for AccessShareLock on relation 1477220 of database 16385; blocked by process 4751.
Hint: See server log for query details.
どうやら、table_cをdropするときに外部キー制約の参照先であるtable_aとtable_bのAccessExclusiveLockも取得しようとするようだ。
ref https://stackoverflow.com/questions/32145189/avoid-exclusive-access-locks-on-referenced-tables-when-dropping-in-postgresql
ref https://lab.mo-t.com/blog/rdb-deadlock
福地春喜同じ状況でも外部キー制約をdropしてからだとテーブルをdropできた。
begin;
alter table table_c drop constraint table_c_a_id_fkey;
alter table table_c drop constraint table_c_b_id_fkey;
drop table table_c;
commit;
結果:
COMMIT
Query returned successfully in 64 msec.
外部キー制約をdropするのに、table_aやtable_bに続いてtable_cをselectするクエリのトランザクションが継続的に実行されているとき、先にtable_aやtable_bを明示的ロックする必要がありそう。次のようにしてテーブルをdropできた。
begin;
lock table
table_a,
table_b
in access exclusive mode;
alter table table_c drop constraint table_c_a_id_fkey;
alter table table_c drop constraint table_c_b_id_fkey;
drop table table_c;
commit;
結果:
COMMIT
Query returned successfully in 117 msec.
継続的に実行されるクエリ:
begin;
select * from table_a;
select pg_sleep(0.01); -- 負荷のエミュレーション
select * from table_c;
commit;
table_cをdropしようとするときロックは次のように取得されようとする？
- 排他ロックは同時に取得するのではない？
- 排他ロックは対象テーブル、外部キー制約の参照先テーブルの順に取得する？
table_cのdrop |
table_aとtable_cのselect |
|
|---|---|---|
| 🙆♀️ |
table_aのAccessShareLock |
|
table_cのAccessExclusiveLock |
🙆♀️ | |
| 🙅♀️ |
table_cのAccessShareLock |
|
table_aのAccessExclusiveLock |
🙅♀️ |
福地春喜table_cに続いてtable_aやtable_bをselectするクエリのトランザクションが継続的に実行されているときでは明示的ロックなしで外部キー制約とテーブルをdropできた。
やはり対象テーブル、外部キー制約の参照先テーブルの順に排他ロックを取得していそう。
継続的に実行されるクエリ:
begin;
select * from table_c;
select pg_sleep(0.01); -- 負荷のエミュレーション
select * from table_a;
commit;
先のテーブル定義では次の順で排他ロックが取得されていそうだった。
- 対象テーブル
table_c - 外部キー制約の参照先テーブル
table_b - 外部キー制約の参照先テーブル
table_a
継続的に実行されるクエリが次だと単純にテーブルをdropできた。
begin;
select * from table_c; select pg_sleep(0.01);
select * from table_b; select pg_sleep(0.01);
select * from table_a;
commit;
テーブルをdropしたクエリ:
begin;
drop table table_c;
commit;
次のことが分かっていれば、なんのことはない「ロックを取得する順序によるデッドロックの基本的な構造」と矛盾しない。
- テーブルをdropするとき、外部キー制約の参照先テーブルのロックも取得する
- drop対象のテーブル、外部キー制約の参照先テーブルの順にロックを取得する
なお、外部キー制約のあるカラムをdropするとき、あるいは外部キー制約をdropするときでも、テーブルをdropするときと同様に、カラムdrop対象のテーブル、外部キー制約の参照先テーブルの順にロックを取得しようとする。
福地春喜table_a, table_bの順にselectするのクエリのトランザクションと同時実行する可能性があるとき、テーブルtable_cは次のように明示的ロックしてからdropする。
begin;
lock table table_a in access exclusive mode; -- table_aをロック
alter table table_c drop column a_id; -- table_c, table_bの順にロック
commit;
table_a, table_cの順にselectするのクエリのトランザクションと同実行するときも同様。
福地春喜なお、明示的ロックに複数テーブルを指定しても同時にロックを取得するわけではない。指定した順にロックを取得しようとする。
lock table
table_a,
table_b,
table_c
in access exclusive mode; -- table_a, table_b, table_cの順にロック