テーブルロックモードの関係を把握しやすいように図で表してみました。
() 内はそのロックを自動的に獲得するクエリです。
■ロックとトランザクション
SQL における排他制御の方法として、トランザクションとロックが挙げられます。
複数のクエリをトランザクションでひとつにまとめることで、(デフォルトのトランザクションレベル READ COMMITTED の場合)コミット済みの結果のみ参照されるようになり、トランザクション中の内容を外部からは見えなくなるため一見アトミック性が守られるように見えます。
しかし、複数の接続で同時に既存の同じレコードに対して参照と更新を分けて実行する場合などはトランザクションだけでは限界があるのも事実です。
たとえば以下のテーブルを考えてみましょう。
CREATE TABLE foo (
id int NOT NULL, — 商品の識別番号
price int NOT NULL — 値段
);
INSERT INTO foo (id, price) VALUES(1, 100); — id:1 は 100 円
INSERT INTO foo (id, price) VALUES(2, 500); — id:2 は 500 円
このテーブルに対して、以下の処理を行うものとします。
BEGIN;
SELECT id FROM foo WHERE price < 500; -- 500円未満の id:1 を得る
UPDATE foo SET price = price + 500 WHERE id = 1; — 500円未満だった id:1 を 500円増し
COMMIT;
READ COMMITED レベルで同時に同じのクエリが実行されると、id:1 が 1100 円になる可能性があります。
トランザクションのみでこれを防ぐには衝突する可能性のある全てのクエリのトランザクションレベルを最も厳密な SERIALIZABLE にする必要があります。
— 衝突回避版
BEGIN;
SET TRANSACTION SERIALIZABLE; — この接続のトランザクションレベルを SERIALIZABLE に。
SELECT id FROM foo WHERE price < 500; -- 500円未満の id:1 を得る
UPDATE foo SET price = price + 500 WHERE id = 1; — 500円未満だった id:1 を 500円増し
COMMIT;
ただし、 SERIALIZABLE 隔離レベルのトランザクションの実行中に他の接続で COMMIT された場合、直列性を守るためにエラーになり処理が続行できなくなります。このため、衝突した場合は ROLLBACK の後再処理を行う必要があるなど, 欠点もあります。トランザクションのみでは、参照を完全にブロックすることができないのが原因のひとつです。
そこで、読み込みも制御できるロックの出番というわけです。
■ロックの獲得方法
多くのクエリは暗黙にクエリ単位で行レベルロックを獲得しています。
ロックの種類と影響する範囲については図を参照してください。
複数のクエリでロックを獲得するには LOCK 構文を用います。
LOCK 構文で取得できるロックはテーブルに対するロックのみです。
BEGIN;
LOCK foo_table IN EXCLUSIVE MODE; — EXCLUSIVE ロック獲得。
SELECT * FROM foo_table;
UPDATE foo_table SET …
— その他何か色々なクエリ…
COMMIT;
たとえば EXCLUSIVE モードでロックを獲得すると、他の接続では SELECT, ANALYZE 文以外の全てのクエリ、および ACCESS_SHARE 以外の全てのロックを明示的に獲得しようとするとロック待ちになります。
※具体的には UPDATE, DELETE, INSERT 文および SELECT … FROM table FOR …, CREATE INDEX, ALTER TABLE, DROP TABLE, REINDEX, CLUSTER, VACUUM, VACUUM FULL 文の実行を防ぎます。
また、ACCESS EXCLUSIVE, EXCLUSIVE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, SHARE の明示ロック獲得を防ぎます。
■ロックの解放方法
ロックはトランザクションの終了時(COMMIT, ROLLBACK実行時)に自動で解放されます。
トランザクションの途中でロックだけを解放する方法はありません。
参考:
–トランザクションの隔離(PostgreSQL 8.3.1 マニュアル)
–LOCK 構文(同上)
–トランザクションと隔離レベルとロック(ファイヤープロジェクト)