PostgreSQLスキルアップノート（自己啓発のための個人サイト）
行レベルロック・SELECT FOR UPDATE/SHARE
【一覧に戻る】
マニュアルへのリンクは/9.2/としています。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ 行レベルロック・SELECT FOR UPDATE/SHARE
■■■■
■■■■
■■■■ 2013/03/07
■■■■ 使用環境：PostgreSQL9.1.7 (CentOS6.2)
(C) 2013 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【マニュアル】
SELECT(FOR UPDATE/FOR SHARE)→●[マニュアル]
【マニュアル参考】
第 13章同時実行制御・明示的ロック→●[マニュアル]
【参考記事】
−
■１■ 概要
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
FOR UPDATE・・自分が更新する目的で行をロックしておく。
FOR SHARE ・・他からの更新がかからないように行をロックをしておく。
SELECT・・・FOR UPDATE [ OF table_name [, ...] ] [ NOWAIT ]
SELECT・・・FOR SHARE [ OF table_name [, ...] ] [ NOWAIT ]
・ロックできない場合に即エラーを返したい場合はNOWAIT
・ロックできるまで待ちたい場合にはNOWAITを指定しない
・待機時間の指定はできない。(9.2現在)
・一部のみのテーブルを対象にする場合にはOFでテーブル名を羅列する。
・FOR UPDATEとFOR SHAREの違い
・FOR UPDATE・・該当行を排他ロック
・FOR SHARE・・該当行を共有ロック
他から更新されることはなくなる。
が、同じFOR SHAREを別の人も同時に獲得できる
ので自分も更新ができなくなることはある。
【注意】しかしFOR UPDATEが成功しても更新時に待たされてしまうことはある。
詳細は次を参照
■２■ 参考 FOR UDPATEが成功したのに更新時に待たされるケース
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
（以下は、前に学習したテーブルレベルロックのマトリックスと行レベルロックとの関連
で気になった点がありそれを確認した内容です。
前回のテーブルレベルロックのページについても参照下さい。）
FOR UPDATEした行は、他からFOR UPDATE/FOR SHARE/UPDATE/DELETE一切はできなくなる。
テーブルの排他ロックもできない。
しかし、テーブルのSHAREロックは獲得されてしまう。
FOR UPDATEをしておいても、後から誰かがテーブルのSHAREロックをすることができ、
この場合はFOR UPDATEした行のUPDATEはできなくなり待ちとなる。
【□】 テストデータ作成
drop table t1;
create table t1 as select a.a as c1,a.a||'LINE' as c2 from generate_series(1,5) a;
table t1;
c1 | c2
----+--------
1 | 1LINE
2 | 2LINE
3 | 3LINE
4 | 4LINE
5 | 5LINE
【term-1:□】 テーブルの一部の行をロック（UPDATEはしない）
BEGIN;SELECT * FROM t1 where c1=3 FOR UPDATE;
【term-2:□】 別セッションからテーブルをSHAREロック
BEGIN; LOCK TABLE t1 IN SHARE MODE;
【term-1:□】 もとのセッションから行の更新を試みる
UPDATE t1 SET c2='aaaaa' where c1=3;
・・・待ちになってしまう。
【term-0:□】 ロック確認
term-1,term-2とは別のターミナルより確認のこと
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;
pid | granted | datname | locktype | relation | relation | transactionid | mode
-------+---------+----------+---------------+----------+----------+---------------+------------------
term-1
11730 | t | | transactionid | | | 20325 | ExclusiveLock
11730 | t | | virtualxid | | | | ExclusiveLock
11730 | t | postgres | relation | 19887 | t1 | | RowShareLock・・FORUPDATEによるテーブルロック
11730 | f★ | postgres | relation | 19887 | t1 | | RowExclusiveLock・・UPDATEによるテーブルロック
-------+---------+----------+---------------+----------+----------+---------------+------------------
term-2
11734 | t | postgres | relation | 19887 | t1 | | ShareLock・・LOCK TABLEによるテーブルロック
11734 | t | | virtualxid | | | | ExclusiveLock
・SELECT FOR UPDATE では「ROW SHARE」という「テーブルロック」がかかる。
・LOCK TABLE IN SHARE MODEでは「SHARE」というテーブルロックがかかる。
・下のマトリックス ２と５ではＸになっていないのでお互いに競合しない。
だから、FOR UPDATEがかかっている状態でもテーブルのSHAREロックは成功する。
・しかしその後にFOR UPDATEをかけたトランザクションから、UPDATEを行うと
ROWEXCLUSIVE「テーブルロック」が要求され、 3と5は互いに競合するので
ここで初めて待ちが発生することになる。
つまり、
・自分がSELECT FOR UPDATE・・成功
・誰かがLOCK TABLE SHARE・・成功
・自分がUPDATE・・待ち
ということになる。
それでは、待たされている間に相手がその行へ更新をかけてしまったらどうなるだろうか？
【term-2:□】 相手が同じ行をUPDATE
UPDATE t1 SET c2='aaaaa' where c1=3;
相手はLOCK TABLE SHAREはできた。
さらに相手がUPDATEしようとすると、
UPDATEに先だって暗黙的に実行される「ROW EXCLUSIVE」テーブルロックも成功する。
（FOR UPDATEのROW SHAREテーブルロックとは競合しないため（下表））
しかし自分側はFOR UPDATEして同じ行に行排他ロックをかけているから更新はできない。
相手はその行ロックの解放を待つことになる。
が、自分側は既に相手の「SHARE テーブルロック」の解放を待っている。
自分が相手を待っているのに、相手も自分を待つことになるので
デッドロックとなり後から要求した相手側でエラーとなる。
ERROR: deadlock detected
同時に、SHAREロックは解放され、待っていた自分の更新が成功する。
もっともテーブルのSHAREロックで普通は更新まですることはないので
この検証はあくまでも、ロックの仕組みの習得のため・・
【参考】テーブルロックのマトリクスのうち関連部分のみ抜粋
現在のロックモード
要求ロックモード |1|2|3|4|5|6|7|8|
--------------------------------------------------------------+-+-+-+-+-+-+-+-+
2 ROW SHARE SELECT FOR UPDATE・SHARE | | | | | | |X|X|
3 ROW EXCLUSIVE UPDATE,DELETE,INSERT | | | | |X|X|X|X|
5 SHARE LOCK TABLE IN SHARE MODE | | |X|X| |X|X|X|
--------------------------------------------------------------+-+-+-+-+-+-+-+-+
以上
