PostgreSQLスキルアップノート（自己啓発のための個人サイト）
テーブルレベルロック・検証編
【一覧に戻る】
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ テーブルレベルロック・検証編
■■■■
■■■■
■■■■ 2013/03/03
■■■■ 使用環境：PostgreSQL9.1.7 (CentOS6.2)
(C) 2013 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
テーブルレベルのロックと影響を実際に確認する。
【マニュアル】
第 13章同時実行制御・明示的ロック→●[マニュアル]
LOCK文→●[マニュアル]
SELECT文(FOR UPDATE/FOR SHARE句)→●[マニュアル]
第45章システムカタログ・pg_locks→●[マニュアル]
【その他】
−
■１■ 復習
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
現在のロックモード
| | | | | | | | |
要求ロックモード LOCK文以外の代表例 |1|2|3|4|5|6|7|8|
| | | | | | | | |
--------------------------------------------------------------+-+-+-+-+-+-+-+-+
1 ACCESS SHARE SELECT | | | | | | | |X|
2 ROW SHARE SELECT FOR UPDATEおよびSHARE | | | | | | |X|X|
3 ROW EXCLUSIVE UPDATE,DELETE,INSERT | | | | |X|X|X|X|
4 SHARE UPDATE EXCLUSIVE VACUUM等 | | | |X|X|X|X|X|
5 SHARE CREATE INDEX | | |X|X| |X|X|X|
6 SHARE ROW EXCLUSIVE - | | |X|X|X|X|X|X|
7 EXCLUSIVE - | |X|X|X|X|X|X|X|
8 ACCESS EXCLUSIVE VACUUM FULL等 |X|X|X|X|X|X|X|X|
--------------------------------------------------------------+-+-+-+-+-+-+-+-+
■２■ 準備・実験用テーブル作成
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【□】 実験用テーブル作成
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,l.mode
FROM pg_locks l LEFT JOIN pg_database d ON l.database = d.oid
WHERE l.pid != pg_backend_pid()
AND l.locktype = 'relation'
ORDER BY l.pid;
---------------------------------------------------------------------
l.pid バックエンドプロセスのpid
l.granted ロックが待たされている場合 f
d.datname DB名
l.locktype ロック対象。今回はテーブルレベル'relation'のみ抽出
relation テーブル等oid
relation::regclass テーブル等名称
（pg_classへ結合の代わりにregclassキャストで名前を取得）
l.mode ロックモード
l.pid != pg_backend_pid() このSQL自身のロックは結果から除外
---------------------------------------------------------------------
（参考）locktype ロック対象オブジェクト
詳細はマニュアルからは確認できなかったため以下より確認
Let's Postgres→●[記事]
locktypeの主なもの
・relation テーブルレベルのロック
・transactionid や tuple 行レベルのロック
・extend INSERTなどによる特定テーブル拡張で競合
（参考）PREPARE（プリペアド文）
PREPAREは文解析の負荷軽減が目的の機能であるが一時的な関数用途にも使える。
「SQL関数」とは異なりセッション内限り有効。
PREPAREで定義し、EXECUTE name;で実行する。
■４■ 単純にSELECTした場合のロック状態
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
小さなテーブルではすぐにselectが終わりロックが解放されてしまうため、トランザク
ション内でSELECTすることにより確認を行う。
COMMIT/ROLLBACKしない限りSELECT時のロックがそのまま保持される。
【term-1 □】 BEGIN;SELECT * FROM t1;
【term-0 □】 EXECUTE show_pglock;
pid | granted | datname | locktype | relation | relation | mode
-------+---------+-----------+----------+----------+----------+-----------------
16350 | t | database1 | relation | 19613 | t1 | AccessShareLock
以上より
・t1に対してテーブルロックAccessShareLockが要求された。
・ロックは成功している状態である。（granted列が「t」）
■５■ 単純にUPDATEした場合のロック状態
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
UPDATE文などはpsqlのデフォルトでは自動でコミットがかかってしまうため、確認する
ためには必ずBEGINによりトランザクションの開始を宣言する必要がある。
【term-1 □】 BEGIN; UPDATE t1 set c2='aaaaa' WHERE c1=1;
【term-0 □】 EXECUTE show_pglock;
pid | granted | datname | locktype | relation | relation | mode
-------+---------+-----------+----------+----------+----------+------------------
16350 | t | database1 | relation | 19613 | t1 | RowExclusiveLock
以上より
・t1に対しテーブルロックてRowExclusiveLockが要求された。
・ロックは成功している状態である。（granted列が「t」）
■６■ 単純にSELECT FOR UPDATEした場合のロック状態
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【term-1 □】 BEGIN; SELECT * FROM t1 WHERE c1=1 FOR UPDATE;
【term-0 □】 EXECUTE show_pglock;
pid | granted | datname | locktype | relation | relation | mode
-------+---------+-----------+----------+----------+----------+--------------
16350 | t | database1 | relation | 19613 | t1 | RowShareLock
以上より
・t1に対してテーブルロックRowShareLockが要求された。
・ロックは成功している状態である。（granted列が「t」）
■７■ SELECT（ACCESS SHAREロック）中はACCESS EXCLUSIVEロックが阻まれる確認
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
冒頭のマトリクスで、現在のロックモード「1」に対して要求ロックモード「8」の確認。
ACCESS EXCLUSIVEモードが実運用で使われることは滅多にないが、SELECTがテーブルレベル
ロックを使って他の処理を阻害する動きを確認。
それぞれ別のターミナルから実行して確認する。
ACCESSSHAREロック
【term-1 □】 BEGIN; SELECT * FROM t1;
ACCESS EXCLUSIVEロック
【term-2 □】 BEGIN; LOCK t1 IN ACCESS EXCLUSIVE MODE; ・・待ちが発生
確認
【term-0 □】 EXECUTE show_pglock;
pid | granted | datname | locktype | relation | relation | mode
-------+---------+-----------+----------+----------+----------+---------------------
16643 | t | database1 | relation | 19613 | t1 | AccessShareLock
16655 | f ★待ち| database1 | relation | 19613 | t1 | AccessExclusiveLock
以上より
・t1に対してテーブルロックAccessShareLockが要求された。
・t1に対してテーブルロックAccessExclusiveLockが要求された。
・AccessExclusiveLockは認められず待ちになっている状態である。granted=f
■８■ 同じテーブルに双方がUPDATE（ROW EXCLUSIVEロックどうし）
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
互いに同じテーブルをUPDATEしても阻まれることはない。
冒頭のマトリクスで現在のロックモード「3」対要求ロックモード「3」の箇所を参照。
ただし、同じ行を更新した場合には、テーブルレベルのロックではなく行ロックが原因で
阻まれることになる。
１．別の行へ更新した場合
──────────────────────────────
【term-1 □】 BEGIN; UPDATE t1 set c2='aaaaa' WHERE c1=1;
【term-2 □】 BEGIN; UPDATE t1 set c2='bbbbb' WHERE c1=2;
【term-0 □】 EXECUTE show_pglock;
pid | granted | datname | locktype | relation | relation | mode
-------+---------+-----------+----------+----------+----------+------------------
16643 | t | database1 | relation | 19613 | t1 | RowExclusiveLock
16655 | t | database1 | relation | 19613 | t1 | RowExclusiveLock
以上よりどちらのRowExclusiveLockロックも成功している。
２．同一行へ更新した場合
──────────────────────────────
【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 | mode
-------+---------+-----------+----------+----------+----------+------------------
16643 | t | database1 | relation | 19613 | t1 | RowExclusiveLock
16655 | t | database1 | relation | 19613 | t1 | RowExclusiveLock
「term-2」は終了しない状態になっているにも関わらず、両方とも「granted=t」となっている。
これはテーブルロック自体は成功していることを示している。
待ちの原因はもちろん行レベルロックである。
ここではlocktypeがrelationのものだけに絞ってあり、「granted=f」は見えないが、絞らなけ
れば「granted=f」のものが現れてくる。
今回はテーブルレベルロックの確認と、マトリクスの理解が目的のため、ここでは触れず、行レ
ベルロックを含めた確認は章をあらためて行うこととする。
以上
