ブログ

PostgreSQLのテーブルロックを解除するには
2019.10.04

バッチ処理的に大量のデータ更新を行うためにテーブルロック（EXCLUSIVE MODE）をおこなったのですが、思いがけず問題が発生してロックしたままになってしまいました。
このような時に、解除する方法です。

目次

1. ロックしているプロセスID(pid)の確認
2. ロックの解除

1.ロックしているプロセスID(pid)の確認

psqlにてデータベースに接続した後、次のSQLで確認します。

select * from pg_stat_activity;

「waiting」で「t」となっているものがロックしている問合せとなります。
下記の場合、2行目と3行目の「waiting」が「t」です。

ad126z6tmm=> select * from pg_stat_activity;
 datid | datname  |  pid  | usesysid |  usename   | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | waiting | state  |                         query
-------+----------+-------+----------+------------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+--------+-------------------------------------------------------
 16881 | sampledb | 15484 |    17414 | sampleuser |                  | 127.0.0.1   |                 |       43766 | 2019-10-01 08:59:27.529876+09 | 2019-10-01 08:59:27.532452+09 | 2019-10-01 09:03:01.699899+09 | 2019-10-01 09:03:01.699899+09 | f       | active | SELECT code,price FROM goods WHERE name = $1
 16881 | sampledb | 16250 |    17414 | sampleuser | psql             | 127.0.0.1   |                 |       44329 | 2019-10-01 09:00:42.085982+09 | 2019-10-01 09:00:49.732322+09 | 2019-10-01 09:00:49.732322+09 | 2019-10-01 09:00:49.732324+09 | t       | active | SELECT count(*) FROM goods WHERE status = 2;
 16881 | sampledb | 16629 |    17414 | sampleuser |                  | 127.0.0.1   |                 |       44610 | 2019-10-01 09:01:08.860556+09 | 2019-10-01 09:01:08.951878+09 | 2019-10-01 09:01:08.951878+09 | 2019-10-01 09:01:08.95188+09  | t       | active | SELECT * FROM goods WHERE type = 10 ORDER BY name ASC
 16881 | sampledb | 17217 |    17414 | sampleuser | psql             | 127.0.0.1   |                 |       45036 | 2019-10-01 09:02:38.623184+09 | 2019-10-01 09:03:01.735736+09 | 2019-10-01 09:03:01.735736+09 | 2019-10-01 09:03:01.735738+09 | f       | active | SELECT * FROM pg_stat_activity;
(4 rows)

さらに、「backend_start」の時刻を見ると、2行目が先に実行を開始してテーブルをロックしており、3行目が待っている状態でした。
しかしながら、2行目の実行に時間がかかったり、問題があり、ロックしたままになってしまいました。
（なお、「query」のSQLはサンプル的なものに差し替えて掲載しています）

今回は、2行目のロックを解除できれば、待たされている3行目が実行できるようになるはずです。

原因となっている2行目の「pid」を確認すると、「16250」であることが分かります。

2.ロックの解除

ロックの解除は、次のSQLにて実行できます。

SELECT pg_cancel_backend(プロセスID);

今回の場合は、次のようになります。

SELECT pg_cancel_backend(16250);

以上で、無事にロックを解除でき、待たされていた3行目のクエリーも実行されました。
同じような問題が発生した場合のお役に立てれば幸いです。

参考サイト
https://qiita.com/adebadayo/items/2c57084e3b6de01cfc6b