デットロックとFOR UPDATE

こんにちは。
PostgreSQLのマニュアルを見ますと。次のような文の組み合わせはデットロックになる可能性があるとされてます。

プロセス１
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;
プロセス２
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;

#PostgreSQL8.1.5のマニュアルの12.3.3を参照しています。
#http://www.postgresql.jp/document/pg815doc/html/ …

こういった場合の回避方法なのですが、
プロセス１
SELECT * FROM accounts WHERE acctnum=11111 AND acctnum = 22222 FOR UPDATE;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;
プロセス２
SELECT * FROM accounts WHERE acctnum=22222 AND acctnum = 11111 FOR UPDATE;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
とするのは大丈夫なのでしょうか？
本来なら、マニュアルにもある通り、UPDATEの順番を揃えるのがいいのでしょうが、実際にはWHEREの部分が変数で変化してしまうため、少々手間なのです。

実際のところ、このような場合、SELECT　～　FOR UPDATEで大丈夫なのでしょうか？それともテーブルレベルロックを用いるべきでしょうか？あるいはソートを用いるなどしてでも順番をそろえるべきなのでしょうか？

実際の処理はpl/pgsqlの関数の中で、EXECUTE文によって行っています。
なので、なるべく複雑な処理は避けたいところなのです。

できれば情報ソースなども示してご説明いただけるとありがたいです。よろしくお願いいます。

No.2 ベストアンサー 回答者： chukenkenkou 回答日時： 2009/02/14 14:16

質問に対する直接的な話ではないのですが。

。。

WHERE acctnum=11111 AND acctnum = 22222 FOR UPDATE

↓

ANDでなくてOR

INにした方が、速い

この回答へのお礼

＞ANDでなくてOR
はい、ORの間違いでした。

＞INにした方が、速い
なるほど、この構文ですね。ありがとうございます。
WHERE acctnum IN (11111,22222) FOR UPDATE

お礼日時：2009/02/16 11:04

No.1 回答者： bincook 回答日時： 2009/02/14 09:04

デッドロックが発生するのは、下記の(1)または(2)まで実行された段階で(3)の前に(4)が実行された場合です。

FOR UPDATEも行ロックをするだけですので、大丈夫ではありません。

プロセス１
(1) SELECT * FROM accounts WHERE acctnum=11111 AND acctnum = 22222 FOR UPDATE;
(2) UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
(3) UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;
プロセス２
(4) SELECT * FROM accounts WHERE acctnum=22222 AND acctnum = 11111 FOR UPDATE;
(5) UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;
(6) UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;

もし、ロックされていればエラーにして返しても良いのであれば、「FOR UPDATE NOWAIT」を指定すればよいと思います。

エラーにはできないというのであれば、順番をそろえるしかないと思います。

参考URL：http://www.postgresql.jp/document/pg815doc/html/ …

この回答へのお礼

ありがとうございます。ちょっと分からない点があるので、もう少し質問させてください。
＞デッドロックが発生するのは、下記の(1)または(2)まで実行された段階で(3)の前に(4)が実行された場合です。
＞FOR UPDATEも行ロックをするだけですので、大丈夫ではありません。
ということですが、(4)が実行されようとする時点で、（１）が実行されているので、(4)は待機状態になりますよね。
この状態で(3)が実行されて、プロセス１のトランザクションが終了すると、(4)から抜けて(5)(6)と実行されて、無事に終了するのではないかと思うのですが、これでもデットロックしてしまうのでしょうか？

あと、(1)、(4)のANDはORの間違いでした。申し訳ありません。

お礼日時：2009/02/15 13:41