デットロック回避策(autocommit off)について

質問

環境：LINUX　Postgres8.4.0
デットロックを回避する為に、自動コミットをやめ、手動コミットに変更したいです。

psqlで\set AUTOCOMMIT offとすると確かに手動コミットになってしまいますが、
毎回設定するのは、手間がかかるのとシステム全体に反映されません。

システム全体を手動コミットに変更するには、どうしたら宜しいのでしょうか？

回答No.3

yamada59 · ベストアンサー

デッドロックが何かを理解していますか？

デッドロックというのはロックの要求がすくみの状態になって互いにロックの開放を待ち続けてしまう状態です。

例えば、以下のように 2 つのテーブルを作成してデータを挿入し、

CREATE TABLE foo (id integer);
INSERT INTO foo VALUES (1);
CREATE TABLE bar (id integer);
INSERT INTO bar VALUES (1);

2 つの psql を起動します。ここでは 2 つの psql を見分けるために psql1、psql2 と呼ぶことにします。

1. psql1 で foo テーブルの id = 1 の行をロック

BEGIN;
SELECT * FROM foo WHERE id = 1 FOR UPDATE;

2. psql2 で bar テーブルの id = 1 の行をロック

BEGIN;
SELECT * FROM bar WHERE id = 1 FOR UPDATE;

3. psql1 で bar テーブルの id = 1 の行をロック

SELECT * FROM bar WHERE id = 1 FOR UPDATE;

bar テーブルの id = 1 の行は psql2 がロックしているので待たされる。

4. psql2 で foo テーブルの id = 1 の行をロック

SELECT * FROM foo WHERE id = 1 FOR UPDATE;

foo テーブルの id = 1 の行は psql1 がロックしているので待たされる。

5. psql1 は foo テーブルの id = 1 の行をロックしつつ bar テーブルの id = 1 の行に対するロックが開放されるのを待ち、psql2 は bar テーブルの id = 1 の行をロックしつつ foo テーブルの id = 1 の行に対するロックが開放されるのを待ち、互いにロックが開放されるのを待ち続ける状態になります。これをデッドロックと呼びます。

6. PostgreSQL にはデッドロックを検出する仕組みがあるので、後からロックしようとした psql2 のトランザクションがアボートされます。

ERROR: deadlock detected
DETAIL: Process 26267 waits for ShareLock on transaction 709; blocked by process 26261.
Process 26261 waits for ShareLock on transaction 710; blocked by process 26267.
HINT: See server log for query details.

従って、「デットロックを回避する為に、自動コミットをやめ、手動コミットに変更したいです。」という質問に対する回答としては、手動コミットに変更してもデッドロックは回避できません。デッドロックを回避するにはロックする順序を揃える必要があります。

また、回答番号: No.2 で athanasius さんが回答しているように、明示的にロックしなくても、同じテーブルの同じ行に対して更新しようとした場合には暗黙的に行ロックが取得されるので、先にロックを取得したほうが更新してから、次にロックを取得したほうが更新するだけでデータの不整合は発生しません。

「システム全体を手動コミットに変更するには、どうしたら宜しいのでしょうか？」という質問に対する回答としては、psql によるすべての接続で手動コミットを強制する方法はありません。

ただ、~/.psqlrc に「\set AUTOCOMMIT off」と書いておけば接続時に自動コミットが無効になります。

回答No.2

athanasius

気になるんですが、もしかして、勘違いしていませんか？
PostgreSQLは、レコードロックだから、他の人が更新しているかといってテーブルはロックしなくてもいいはずですよ。

万が一でも、後の方が待ちになるだけで問題になるようなことはない気がしますが。
デッドロックって、占有しているリソースの開放を待ち合ってしまう状況で発生するので、一つのテーブルの更新で発生するものではないはずです。

回答No.1

chukenkenkou

>デットロックを回避する為に、自動コミットをやめ、手動コミットに変更したい

本当にデッドロックが問題なのですか？
もしそうなら、発想が逆ですけど？
ちなみに、デッドロック(DEADLOCK）ですので念のため。

psqlに関しては、マニュアルで設定ファイルについて説明されています。

http://www.postgresql.jp/document/pg840doc/html/app-psql.html#APP-PSQL-VARIABLES

補足

初心者の私ですが、回答ありがとうございます。

ちょっと気になったのですが、デッドロックを回避する為には、どのような手法が宜しいのでしょうか・・・

自動コミットの場合
Aテーブルに
αさんが更新しました。
βさんも更新しました。
あるレコードの同期が取れませんよね？その時点でデータがおかしくなるような気もします。

手動コミットの場合
Aテーブルに
αさんが更新しました。（一度SELECT文でロックをかける）
βさんは更新しようとしたが、ロックがかかっている為、更新できない。

排他制御ができないでデッドロック障害は回避できるのでしょうか？

データの整合性が取れると思っている私は、無知なのでしょうか・・・（＾＾；