PostgreSQLは、SELECTもロックを獲得する

SQL

PostgreSQLは、SELECT文を実行すると暗黙的にロックを獲得するということを、システムを止めて初めて知りました。
なので、こういうSQLはまずいです。

SELECT * FROM FooTable;
LOCK TABLE FooTable;

これを二つ同時に実行するとデッドロックが発生します。


これは、2つのコマンドプロンプトで

  画面A　BEGIN;                                // トランザクションの開始
  画面B                           BEGIN;       // トランザクションの開始
  画面A　SELECT * FROM FooTable;
  画面B                           SELECT * FROM FooTable;
  画面A  LOCK TABLE FooTable;
  画面B                           LOCK TABLE FooTable;

と実行すると再現できます。

なんで？

2行目のテーブルロックは、そのトランザクション以外にテーブルにアクセスするトランザクションがないことを保証します。
（そのため、ロック中は他のトランザクションの書き込みも読み込みも禁止します）
しかし、すでに実行中のトランザクションで読み込みが行われていたら、「他にアクセスするトランザクションがない」とは言えなくなります。
そのため、ロックをする側は、そのトランザクションが終了するまで待機します。
でも、上記の場合は互いに「別のトランザクションがアクセス中だから待機」となってデッドロックになります。


詳細は、こちらを参考に。
明示的ロック - PostgreSQL 8.4.2文書
SELECT実行時に獲得するロック(ACCESS SHAREロックモード)は、LOCK TABLE実行時に獲得するロック(ACCESS EXCLUSIVEロックモード)と競合する、とのこと。

PostgreSQLだけ？

MySQLでは、テーブルロックの前にそれまでの操作がコミットされます。
なので、上記の様なケースがそもそも発生しないようです。

LOCK TABLES はトランザクション セーフではないので、テーブルをロックしようとする前に、全てのアクティブなトランザクションを暗黙的にコミットします。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.3.5 LOCK TABLES および UNLOCK TABLES 構文


Oracleでは、どのようなロックでもSELECTは実行可能です。
ただ、SELECT...FOR UPDATEだと上記と同じ問題が発生する、とのこと。

しかし SELECT の度にロックを掛けているわけではなく、データや表定義が変更されると困る操作のときにだけ掛けられる。

表ロックの種類と相互関係 - オラクル・Oracleをマスターするための基本と仕組み

１テーブルに対して全く同じ条件で複数レコードを悲観的ロック(for update)しながら取得し、項目を更新するＳＱＬを発行します。
単発では問題なかったのですが、処理時間の関係でパラレルで実行された場合にデッドロックを検出することがあります。

同じＳＥＬＥＣＴ文同士でのデッドロックが発生 -ORACLE10Gを使用した- Oracle | 教えて!goo


意外とロック処理はデータベースによる違いが大きいんですね・・・。