PostgreSQLスキルアップノート（自己啓発のための個人サイト）
行レベルロック・概要＆検証
【一覧に戻る】
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ 行レベルロック・概要＆検証
■■■■
■■■■
■■■■ 2013/03/03
■■■■ 使用環境：PostgreSQL9.1.7 (CentOS6.2)
(C) 2013 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
行レベルのロックと影響を実際に確認する。
【マニュアル】
第 13章同時実行制御・明示的ロック・行レベルのロック→●[マニュアル]
SELECT文(FOR UPDATE/FOR SHARE句)→●[マニュアル]
第45章システムカタログ・pg_locks→●[マニュアル]
【その他】
−
■１■ 概要
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
行レベルロックはテーブルレベルロックと比べて単純でわかりやすい。
・行レベルロックは参照に影響を与えることは一切ない。同じ行の更新削除だけを阻害する。
・種類は排他ロックと共有ロックのみ。
・ロックの保持期間はコミットまたはロールバックされるまで。
行の排他ロック
・更新・削除を行うと自動的に行が排他ロックされる。
・SELECT FOR UPDATEにより行レベルロック（排他）だけを獲得することができる。
・一旦ロックを獲得すれば自らでトランザクションを終えるまでは他から更新削除される心配がない。
（※１補足参照）
行の共有ロック
・SELECT FOR SHAREにより行う。
・獲得すると他の誰からも更新、削除、排他ロックの獲得はできない。
・ただ、複数の人が同時に同じ行に共有ロックをかけられる。
【補足】※１
マニュアルに更新削除される心配がないと記載されているのは、自分の更新が必ず
成功できるという意味ではない。
たとえばデッドロックによりエラーとなる場合もあれば、FOR UPDATEした後で
テーブルにSHAREロックをかけられてしまうことも起こる。(この検証別ページ）
■２■ 検証準備・実験用テーブル作成
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【□】 実験用テーブル作成
drop table t1;
create table t1 (c1 int,c2 text);
insert into t1 values(1,'aa'),(2,'bb'),(3,'cc');
select * from t1;
c1 | c2
----+----
1 | aa
2 | bb
3 | cc
■３■ 検証準備・ロック状態確認用スクリプト
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ロック状態はpg_locksを参照して確認を行う。 (SELECT * FROM pg_locks;)
ここでは簡単に呼び出せるようにPREPAREを定義。
なお、PREPAREはそのセッション限り有効なのでそのターミナルを確認専用として切らずに使うこと。
このスクリプトは前章のテーブルレベルロックの確認用スクリプトから変更しています。
テーブルレベルと行レベル双方のロックを確認することができます。
【term-0 □】 確認用SQLを準備
DEALLOCATE show_pglock;
PREPARE show_pglock AS
SELECT l.pid,l.granted,d.datname,l.locktype,relation,relation::regclass,transactionid,l.mode
FROM pg_locks l LEFT JOIN pg_database d ON l.database = d.oid
WHERE l.pid != pg_backend_pid()
ORDER BY l.pid;
---------------------------------------------------------------------
l.pid バックエンドプロセスのpid
l.granted ロックが待たされている場合 f
d.datname DB名
l.locktype ロック対象。今回はテーブルレベル'relation'のみ抽出
relation テーブル等oid
relation::regclass テーブル等名称
（pg_classへ結合の代わりにregclassキャストで名前を取得）
transactionid トランザクションID
l.mode ロックモード
l.pid != pg_backend_pid() このSQL自身のロックは結果から除外
---------------------------------------------------------------------
■４■ 検証１・同じテーブルの同一行に２多重でUPDATE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
同じ行を更新(UPDATE)した場合には、テーブルレベルロックは成功するが行ロックが原因で
阻まれることになる。
【term-1 □】 BEGIN; UPDATE t1 set c2='aaaaa' WHERE c1=1;
【term-2 □】 BEGIN; UPDATE t1 set c2='aaaaa' WHERE c1=1; ・・待ちが発生
【term-0 □】 EXECUTE show_pglock;
pid | granted | datname | locktype | relation | relation | transactionid | mode
-------+---------+-----------+---------------+----------+----------+---------------+------------------
16643 | t | | transactionid | | | ★ 19567 | ExclusiveLock
16643 | t | | virtualxid | | | | ExclusiveLock
16643 | t | database1 | relation | 19613 | t1 | | RowExclusiveLock
-------+---------+-----------+---------------+----------+----------+---------------+------------------
16655 | t | database1 | tuple | 19613 | t1 | | ExclusiveLock
16655 | t | | transactionid | | | 19568 | ExclusiveLock
16655 | t | database1 | relation | 19613 | t1 | | RowExclusiveLock
16655 | t | | virtualxid | | | | ExclusiveLock
16655 | f★ | | transactionid | | | ★ 19567 | ShareLock
（分かりやすいようにpidごとに横線を加えてあります）
以上よりわかることは、次項でまとめて記載。
■５■ 検証２・同じテーブルの同一行に４多重でUPDATE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
4トランザクションの同一行更新なので３トランザクションで待ちが発生する。
【term-1 □】 BEGIN; UPDATE t1 set c2='aaaaa' WHERE c1=1;
【term-2 □】 BEGIN; UPDATE t1 set c2='aaaaa' WHERE c1=1; ・・待ちが発生
【term-3 □】 BEGIN; UPDATE t1 set c2='aaaaa' WHERE c1=1; ・・待ちが発生
【term-4 □】 BEGIN; UPDATE t1 set c2='aaaaa' WHERE c1=1; ・・待ちが発生
【term-0 □】 EXECUTE show_pglock;
pid | granted | datname | locktype | relation | relation | transactionid | mode
-------+---------+-----------+---------------+----------+----------+---------------+------------------
term-1
16643 | t | | virtualxid | | | | ExclusiveLock
16643 | t | | transactionid | | | ● 19567 | ExclusiveLock
16643 | t | database1 | relation | 19613 | t1 | | RowExclusiveLock
-------+---------+-----------+---------------+----------+----------+---------------+------------------
term-2
16655 | f ★ | | transactionid | | | ● 19567 | ShareLock
16655 | t | database1 | relation | 19613 | t1 | | RowExclusiveLock
16655 | t | | transactionid | | | 19568 | ExclusiveLock
16655 | t | | virtualxid | | | | ExclusiveLock
16655 | t | database1 | tuple | 19613 | t1 | | ExclusiveLock
-------+---------+-----------+---------------+----------+----------+---------------+------------------
term-3
17007 | t | | transactionid | | | 19574 | ExclusiveLock
17007 | f ★ | database1 | tuple | 19613 | t1 | | ExclusiveLock
17007 | t | database1 | relation | 19613 | t1 | | RowExclusiveLock
17007 | t | | virtualxid | | | | ExclusiveLock
-------+---------+-----------+---------------+----------+----------+---------------+------------------
term-4
17045 | f ★ | database1 | tuple | 19613 | t1 | | ExclusiveLock
17045 | t | | virtualxid | | | | ExclusiveLock
17045 | t | | transactionid | | | 19575 | ExclusiveLock
17045 | t | database1 | relation | 19613 | t1 | | RowExclusiveLock
（分かりやすいようにpidごとに横線を加えてあります）
以上よりわかることは、次項でまとめて記載。
■６■ 前項の結果の見方
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
マニュアルに説明がない部分については、一部「Let's Postgres」の記事を参考にした。
Let's Postgres→●[記事]
上記記事の中に以下のような解説がある。（内容を要約）
----------------------------------------------------------------------------------
locktypeの主なもの
・relation テーブルレベルのロック
・transactionid や tuple 行レベルのロック
・extend INSERTなどによる特定テーブル拡張で競合
locktype = transactionid or tupleの場合
・ある行への最初のロックはpg_locksには表れない。
・次にロックを取りにきたトランザクションはlocktype=「transactionid」がロック待ち。
・その次にロックを取りに来たトランザクションは locktype=「tuple」がロック待ち。
----------------------------------------------------------------------------------
以上を踏まえて前項（■５■）の結果を確認すると以下のようになる。
・４つのトランザクションがあるので、pidは４種類現れている。
・「granted=f」のもの（左側の３つの★印）がロック待ちである。
・最初のトランザクションpid:16643 （term-1から実行した分）
・ロック待ちをしていない。
・ロック対象オブジェクト（locktype）にtoupleが出てこない。
これは行ロックがかかっていないということではなく、上の記事をみる限りでは
「ある行への最初のロックはpg_locksには表れない」という表示上の都合になる。
・２番目のトランザクションpid:16555 （term-2から実行した分）
・ロック待ちがある。 （★印）
・ただtoupleへのロックは成功している（ことになっている）。
待ちが発生しているのは最初のトランザクションと同じtransactionid:19567
に対してShareLockを獲得しようとしている部分である。
・３番目のトランザクションpid:17007（term-3から実行した分）
・ロック待ちがある。 （★印）
・ロック待ちはtoupleへのExclusiveLockを試みて発生している。
・４番目のトランザクションpid:17045 （term-4から実行した分）
・ロック待ちがある。 （★印）
・ロック待ちはtoupleへのExclusiveLockを試みて発生している。
以上、Let'sPostgresの記事の中の 「locktype = transactionid or tupleの場合」
の記述の通りとなった。
３番目と４番目の結果は分かりやすいが、最初と２番目のトランザクションの
結果はあらかじめ知識がないと、普通のロック待ちなのに何か別の原因であるかのような
印象をもってしまう。
【補足】
・tuple | ExclusiveLock
・・・・これは誰がみても行への排他ロックである。
繰り返しになるが、３番目と４番目はここでgranted=fになっており、
UPDATEによる行の排他ロックを獲得しようとして待ちになっているという
ことでわかりやすい。
・transactionid | ShareLock
・・・・２番目のトランザクションではこれで待ちになっている。
２番目はそうなりますよと記事にはかかれているが、やはりUPDATEなら行の排他ロックが待た
されるはずで、何かしらよくわからないと思うのはこのあたりが大きい。
・relation | RowExclusiveLock
・・・・これは「テーブルレベルロック」である。
Locktypeがrelationになっていることからもわかる。
「行排他ロック」そのものではなく「行排他ロックの際のテーブルレベルロック」
行ロックが多くてテーブルロックにエスカレーションするという意味でもない。
行排他ロックの際には必ずこのテーブルレベルロックが発生する。
マニュアルでも「Rowとあるからといって誤解しないように」と記載がある。
行ロックの検証なのにRowExclusiveLockのところは今回全く関係がない。
■７■ 参考・その他の確認手段と表示例
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
１．pg_stat_activity
──────────────────────────────
pg_stat_activityでwaiting=tとなっている状況が確認できる。
【□】
SELECT
procpid,datname,usename,application_name as appli,
to_char(backend_start,'YYYY/MM/DD HH24:MI'),
to_char(xact_start,'YYYY/MM/DD HH24:MI'),
to_char(query_start,'YYYY/MM/DD HH24:MI'),
waiting,
current_query
FROM pg_stat_activity
WHERE procpid != pg_backend_pid();
procpid | datname | usename | appli | to_char | to_char | to_char | waiting | current_query
---------+-----------+----------+--------+------------------+------------------+------------------+---------+--------------------------------------
16643 | database1 | dba01 | psql | 2013/03/03 22:15 | 2013/03/03 22:43 | 2013/03/03 22:43 | f | in transaction
17007 | database1 | dba01 | psql | 2013/03/03 22:56 | 2013/03/03 22:56 | 2013/03/03 22:56 | t ★ | UPDATE t1 set c2='aaaaa' WHERE c1=1;
16655 | database1 | dba01 | psql | 2013/03/03 22:15 | 2013/03/03 22:43 | 2013/03/03 22:43 | t ★ | UPDATE t1 set c2='aaaaa' WHERE c1=1;
17045 | database1 | dba01 | psql | 2013/03/03 22:58 | 2013/03/03 22:58 | 2013/03/03 22:58 | t ★ | UPDATE t1 set c2='aaaaa' WHERE c1=1;
２．psコマンド
──────────────────────────────
psコマンドでもpostgreSQLバックエンドで何が行われどんな状態かがおおよそ把握できる。
以下の例ではUPDATE文が waitingになっていることが確認できる。
手軽なpsコマンドを一次切り分けに活用するのがよいと思う。
【□】 ps axwfo user,pid,ppid,tty,command|egrep "^postgres|^USER"
USER PID PPID TT COMMAND
postgres 8141 8137 ? | \_ sshd: postgres@pts/0
postgres 8142 8141 pts/0 | \_ -bash
postgres 13793 8142 pts/0 | \_ psql database1 dba01
postgres 10930 10926 ? | \_ sshd: postgres@pts/3
postgres 10931 10930 pts/3 | \_ -bash
postgres 16642 10931 pts/3 | \_ psql database1 dba01
postgres 16616 16613 ? | \_ sshd: postgres@pts/1
postgres 16618 16616 pts/1 | \_ -bash
postgres 16654 16618 pts/1 | \_ psql database1 dba01
postgres 16981 16977 ? | \_ sshd: postgres@pts/4
postgres 16982 16981 pts/4 | \_ -bash
postgres 17006 16982 pts/4 | \_ psql database1 dba01
postgres 17019 17016 ? | \_ sshd: postgres@pts/5
postgres 17020 17019 pts/5 | \_ -bash
postgres 17044 17020 pts/5 | \_ psql database1 dba01
postgres 17127 17123 ? \_ sshd: postgres@pts/6
postgres 17128 17127 pts/6 \_ -bash
postgres 17170 17128 pts/6 \_ ps axwfo user,pid,ppid,tty,command
postgres 17171 17128 pts/6 \_ -bash
postgres 16818 1 ? /usr/pgsql-9.1/bin/postgres
postgres 16820 16818 ? \_ postgres: logger process
postgres 16822 16818 ? \_ postgres: writer process
postgres 16823 16818 ? \_ postgres: wal writer process
postgres 16824 16818 ? \_ postgres: autovacuum launcher process
postgres 16825 16818 ? \_ postgres: stats collector process
postgres 13794 16818 ? \_ postgres: dba01 database1 [local] idle
postgres 16643 16818 ? \_ postgres: dba01 database1 [local] idle in transaction
postgres 16655★ 16818 ? \_ postgres: dba01 database1 [local] UPDATE waiting★
postgres 17007★ 16818 ? \_ postgres: dba01 database1 [local] UPDATE waiting★
postgres 17045★ 16818 ? \_ postgres: dba01 database1 [local] UPDATE waiting★
postgres 17066 16818 ? \_ postgres: postgres postgres [local] idle
■■ その他参考
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
・強制的にセッションを終了させることで対応する方法は以下の通り。
SELECT pg_cancel_backend(プロセスID);
システム管理関数-サーバシグナル送信関数→●[マニュアル]
以上
