PostgreSQLでロック情報を確認する方法は？テーブル、プロセス、アプリ、SQLを確認！

PostgreSQLでロック情報を確認する方法は？ Database

PostgreSQLデータベースでロック情報を確認する様々なSQLを紹介します。pg_locksというロック情報を確認することが出来るテーブルを参照すれば確認可能ですが、どのアプリケーションがどのくらいの時間ロックを取得しているか、またどのアプリケーションがどのSQLでロックを取得したか、またはどのSQLでロック待ちになっているか等の詳細情報を確認することが出来ます。

この記事で紹介するSQLを使ってロックが発生してしまった場合に、すぐにロック情報を確認出来るようにして頂ければと思います。

現在のロック情報を確認するSQL

PostgreSQLデータベースで現在のロック情報を確認するには次のSQLを実行します。

SELECT
    d.datname AS dbname,
    t.schemaname,
    t.relname,
    l.locktype,
    l.page,
    l.virtualtransaction,
    l.pid,
    l.mode,
    l.granted
FROM pg_locks l
JOIN pg_stat_all_tables t ON (l.relation = t.relid)
JOIN pg_database d ON (l.database = d.oid)
ORDER BY
    l.database,
    t.schemaname,
    t.relname;

すると以下のような結果が得られます。SQLを実行した時のロックの情報が得られます。ここで表示された情報によって、どのプロセス(pid)が、どのデータベース(dbname)のスキーマ(schemaname)のどのテーブル(relname)に対して、どのようなロックが掛かっているのか(mode)を知ることが出来ます。

=> SELECT
    d.datname AS dbname,
    t.schemaname,
    t.relname,
    l.locktype,
    l.page,
    l.virtualtransaction,
    l.pid,
    l.mode,
    l.granted
FROM pg_locks l
JOIN pg_stat_all_tables t ON (l.relation = t.relid)
JOIN pg_database d ON (l.database = d.oid)
ORDER BY
    l.database,
    t.schemaname,
    t.relname;

+--------+------------+--------------+----------+------+--------------------+---------+---------------------+---------+
| dbname | schemaname |   relname    | locktype | page | virtualtransaction |   pid   |        mode         | granted |
+--------+------------+--------------+----------+------+--------------------+---------+---------------------+---------+
| test   | pg_catalog | pg_class     | relation |      | 5/238387961        | 3604189 | AccessShareLock     | t       |
| test   | pg_catalog | pg_index     | relation |      | 5/238387961        | 3604189 | AccessShareLock     | t       |
| test   | pg_catalog | pg_namespace | relation |      | 5/238387961        | 3604189 | AccessShareLock     | t       |
| test   | test       | test         | tuple    |    0 | 11/341301          | 3605067 | AccessExclusiveLock | t       |
| test   | test       | test         | relation |      | 11/341301          | 3605067 | RowShareLock        | t       |
| test   | test       | test         | relation |      | 10/2383881         | 3604265 | RowShareLock        | t       |
+--------+------------+--------------+----------+------+--------------------+---------+---------------------+---------+
(6 rows)

ロックの経過時間も含めてロック情報を表示する方法

下記SQLを実行することにより、ロック取得からの経過時間も表示することが出来ます。こちらのSQLではロックが発生する原因となったSQLも表示します。こちらはPostgreSQL-9.2以上で動作します。

--
-- PostgreSQL-9.2以上
--

SELECT
    a.datname,
    l.relation::regclass,
    l.transactionid,
    l.mode,
    l.GRANTED,
    a.usename,
    a.query,
    a.query_start,
    age(now(), a.query_start) AS "age",
    a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON (l.pid = a.pid)
ORDER BY
    a.query_start;

ロック取得から現在までの経過時間を”age”という列に表示します。

PostgreSQL-9.1以下の場合には次のSQLで同じ情報を取得することが出来ます。

--
-- PostgreSQL-9.1以下
--

SELECT
    a.datname,
    c.relname,
    l.transactionid,
    l.mode,
    l.granted,
    a.usename,
    a.current_query,
    a.query_start,
    age(now(), a.query_start) as "age",
    a.procpid
FROM pg_stat_activity a
JOIN pg_locks l ON (l.pid = a.procpid)
JOIN pg_class c ON (c.oid = l.relation)
ORDER BY
    a.query_start;

ロックが発生しているSQLを確認する方法

また、もし同じデータを同時に更新しようとしている処理があった場合、次のSQLでpg_locksテーブルを確認することで競合するロック情報を表示することが出来ます。

SELECT
    relation::regclass AS relation_regclass, *
FROM pg_locks
WHERE NOT granted;

実際に競合する更新処理が発生している状態でこのSQLを実行すると、次のように競合するロック情報が表示されます。

=> SELECT
    relation::regclass AS relation_regclass, *
FROM pg_locks
WHERE NOT granted;

+-[ RECORD 1 ]-------+---------------+
| relation_regclass  |               |
| locktype           | transactionid |
| database           |               |
| relation           |               |
| page               |               |
| tuple              |               |
| virtualxid         |               |
| transactionid      | 2423509990    |
| classid            |               |
| objid              |               |
| objsubid           |               |
| virtualtransaction | 11/341301     |
| pid                | 3605067       |
| mode               | ShareLock     |
| granted            | f             |
| fastpath           | f             |
+--------------------+---------------+

しかしこれだけでは情報が少なすぎますので、次のSQLを実行することで、どのSQLとどのSQLが競合しているのかを詳しく確認することが出来ます。

SELECT
    blocked_locks.pid         AS blocked_pid,
    blocked_activity.usename  AS blocked_user,
    blocking_locks.pid        AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query    AS blocked_statement,
    blocking_activity.query   AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks AS blocked_locks
JOIN pg_catalog.pg_stat_activity AS blocked_activity ON (blocked_activity.pid = blocked_locks.pid)
JOIN pg_catalog.pg_locks AS blocking_locks ON (
    blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database       IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation       IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page           IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple          IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid     IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid  IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid        IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid          IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid       IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid)
JOIN pg_catalog.pg_stat_activity blocking_activity ON (blocking_activity.pid = blocking_locks.pid)
WHERE NOT blocked_locks.granted;

このSQLを実行すると、次のようにロックしているSQLと、ロック解除待ちになっているSQLの両方を確認することが出来て非常に便利です。この例では「select * from test where id = 1 for update;」という２つのSQLが同じレコードのロックを取得しようとして、一方のSQLがロック解除待ちとなっています。

=> SELECT
    blocked_locks.pid         AS blocked_pid,
    blocked_activity.usename  AS blocked_user,
    blocking_locks.pid        AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query    AS blocked_statement,
    blocking_activity.query   AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks AS blocked_locks
JOIN pg_catalog.pg_stat_activity AS blocked_activity ON (blocked_activity.pid = blocked_locks.pid)
JOIN pg_catalog.pg_locks AS blocking_locks ON (
    blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database       IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation       IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page           IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple          IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid     IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid  IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid        IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid          IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid       IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid)
JOIN pg_catalog.pg_stat_activity blocking_activity ON (blocking_activity.pid = blocking_locks.pid)
WHERE NOT blocked_locks.granted;


+-[ RECORD 1 ]--------------------------+---------------------------------------------+
| blocked_pid                           | 3605067                                     |
| blocked_user                          | test_user                                   |
| blocking_pid                          | 3604265                                     |
| blocking_user                         | test_user                                   |
| blocked_statement                     | select * from test where id = 1 for update; |
| current_statement_in_blocking_process | select * from test where id = 1 for update; |
+---------------------------------------+---------------------------------------------+

さらに、下記SQLではどのアプリケーションがロックを掛けているのかも確認することが出来るようになっています。

SELECT
    blocked_locks.pid                   AS blocked_pid,
    blocked_activity.usename            AS blocked_user,
    blocking_locks.pid                  AS blocking_pid,
    blocking_activity.usename           AS blocking_user,
    blocked_activity.query              AS blocked_statement,
    blocking_activity.query             AS current_statement_in_blocking_process,
    blocked_activity.application_name   AS blocked_application,
    blocking_activity.application_name  AS blocking_application
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON (blocked_activity.pid = blocked_locks.pid)
JOIN pg_catalog.pg_locks blocking_locks ON (
    blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE       IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation       IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page           IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple          IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid     IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid  IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid        IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid          IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid       IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid)
JOIN pg_catalog.pg_stat_activity blocking_activity ON (blocking_activity.pid = blocking_locks.pid)
WHERE NOT blocked_locks.granted;

これによって、ロックを掛けているアプリケーション名、ロック待ちになっているアプリケーション名の両方を確認することが出来ます。

=> SELECT
    blocked_locks.pid                   AS blocked_pid,
    blocked_activity.usename            AS blocked_user,
    blocking_locks.pid                  AS blocking_pid,
    blocking_activity.usename           AS blocking_user,
    blocked_activity.query              AS blocked_statement,
    blocking_activity.query             AS current_statement_in_blocking_process,
    blocked_activity.application_name   AS blocked_application,
    blocking_activity.application_name  AS blocking_application
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON (blocked_activity.pid = blocked_locks.pid)
JOIN pg_catalog.pg_locks blocking_locks ON (
    blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE       IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation       IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page           IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple          IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid     IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid  IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid        IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid          IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid       IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid)
JOIN pg_catalog.pg_stat_activity blocking_activity ON (blocking_activity.pid = blocking_locks.pid)
WHERE NOT blocked_locks.granted;

+-[ RECORD 1 ]--------------------------+---------------------------------------------+
| blocked_pid                           | 3605067                                     |
| blocked_user                          | test_user                                   |
| blocking_pid                          | 3604265                                     |
| blocking_user                         | test_user                                   |
| blocked_statement                     | select * from test where id = 1 for update; |
| current_statement_in_blocking_process | select * from test where id = 1 for update; |
| blocked_application                   | psql                                        |
| blocking_application                  | psql                                        |
+---------------------------------------+---------------------------------------------+

ロックの解除方法

もしロックが掛かってしまっている場合には、pg_cancel_backend関数に、終了させたいプロセスのプロセスID(pid)を渡すことによってプロセスを終了させることが出来ます。

SELECT pg_cancel_backend(プロセスID);

まとめ

PostgreSQLデータベースのロック情報を詳しく確認するSQLをいくつか紹介しました。どのアプリケーションがどのようなSQLでロックを取得またはロック待ちになっているか、そしてその経過時間はどのくらいなのかを確認することが出来ます。トラブル時に非常に使えるSQLですので是非活用して頂ければと思います。

タイトルとURLをコピーしました