デットロックと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/explicit-locking.html#LOCKING-DEADLOCKS

こういった場合の回避方法なのですが、
プロセス１
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

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

WHERE acctnum=11111 AND acctnum = 22222 FOR UPDATE

↓

ANDでなくてOR

INにした方が、速い

お礼

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

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

回答No.1

bincook

デッドロックが発生するのは、下記の(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/sql-select.html

お礼

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

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