PostgreSQLのデッドロックについて

PostgreSQLの謎のデッドロックに苦しんでいます。

PHP Warning: pg_q> uery(): Query failed: ERROR: デッドロックを検出しました

DETAIL: プロセス 8100 は ShareLock をトランザクション 624517 で待機していましたが、プロセス 6032 でブロックされました
プロセス 6032 は ShareLock をトランザクション 624508 で待機していましたが、プロセス 8100 でブロックされました
HINT: クエリーの詳細はサーバログを参照してください in pg_load_test.php on line *

これがなぜ出てしまうのか、推測でも結構ですのでどなたか理由と解決策を教えてください。
エラーを再現できるスクリプトは以下です。

lang
1<?php
2$dbh = pg_connect("host=localhost port=5432 dbname=postgres user=postgres");
3while(1){
4    $res = pg_query($dbh, "UPDATE public.table SET created = NOW() WHERE field = '*****'");
5    if(!$res) exit();
6}
7

条件句のfieldはINDEXが貼ってあり、複数列がHITします。
サンプルは無限ループですが、実際のバッチも数百万レコードを嘗めてUPDATEに向かいます。
このバッチは中身は一緒で並列稼働します。
最低２本の並列で事象が出ますが、実運用は７並列で稼働しています。
並列数が増えれば増えるほど、当たり前かもしれませんが、デッドロックの検出タイミングは早まります。

クライアント
Windws7
PHP5.6
サーバ
PostgreSQL9.3.5
CentOS6.5 or Windows7 （どちらでも発生を確認）

よろしくお願いします。

行動規範の内容に同意します

回答1件

ベストアンサー

条件句のfieldはINDEXが貼ってあり、複数列がHITします。

update文で複数レコードが更新対象となることが原因と思われます。updateの処理を段階的に見ると、
0. whereで該当するレコード全てにロックを掛ける
0. ロックしたレコードを全て更新
0. ロック解放
の流れになります。並列する処理で「自分がロック獲得したいレコードを相手がロックしている」という関係が並列処理間で成立した場合、双方ロック獲得待ちとなりデッドロックとなります。レコードロックする順序が並列処理で同じなのであればデッドロックは発生しませんが、Indexが張られていたとしても列の値は同じなのでレコードロックの順序は処理で異なる可能性があります。そしてwhereに該当するレコード件数が多いほど（同じ値のレコードが多いほど）このコンディションが発生し易くなります。
対処としては、バッチの並列実行を辞めるか、ロックがぶつからないようにupdateのキーを別の物に変更する等になるかと思います。
対処について、追記します。バッチ処理を平行して実行しても対象のレコードが重複しないよう、バッチ処理別にwhere句に異なる抽出条件を追記する等です。

投稿2014/11/20 11:54

BlueMoon

総合スコア1339

BlueMoon

2014/11/20 13:29

デッドロック発生の説明について修正しました。

al_aya_yuka

2014/11/21 00:09

ご回答いただきまして、ありがとうございます。ロックの順序については私も同じ認識でした… そして発生する理由もおおよそ「自分がロック獲得したいレコードを相手がロックしている」ということなんだろうなぁ、って思っていました。でもそれでしたら、単に自分は相手のロックが解除されるまで待てばいいのではないでしょうか？この時点で相手にとって自分はまだロックを獲得できていないのですから、「デッド」ロックにはなりえないと思っております。また「自分がロック獲得したいレコードを相手がロックしている」とおっしゃっている時点で相手がすでにロックしているので「双方ロック獲得待ち」には該当しないと思います、少なくとも相手が「ロックしている＝ロックを獲得している」ことと思いますので。せっかく回答いただいたのに食いついてしまうようで申し訳ないのですが、それでも上記の状況でデッドロックは発生するのかしないのか再度ご意見をいただけますと幸いです。

TaMaMhyu

2014/11/21 00:23

説明を拝見する感じでは、複数レコードに対するレコード単位のロックが並列実行の関係で前後したレコードに対して発生して、お互いが前のレコードのロック解放を待っているということではないでしょうか？

BlueMoon

2014/11/21 00:59

例えばfieldの値が’A’のレコードが2件有ったとします、そしてそのレコードのidは１と２だったとします。並列処理のA,Bが同じupdateを同タイミングで掛けたとして、・処理Aはid=1,id=2の順でロックを掛けた・処理Bはid=2,id=1の順でロックを掛けたというシュチュエーションが有り得ます。両処理の1レコード目のロック獲得が同タイミングだった場合、２レコード目のロック獲得は双方待ち続けることにるのでデッドロックとなります。 2レコードで説明するとシビアなタイミングと感じますが、同時更新レコード件数が多かったりDB負荷が大きくて検索・更新に掛かるコストが大きい場合、コンディションが発生し易くなります。

BlueMoon

2014/11/21 01:20

コメントにダイレクトにお答えすると、処理A：「自分がロック獲得したいレコードを処理Bがロックしている。ロックしたいレコードが全部獲得できるまで持っているロックは解放しない」処理B：「自分がロック獲得したいレコードを処理Aがロックしている。ロックしたいレコードが全部獲得できるまで持っているロックは解放しない」というイメージでしょうか。

al_aya_yuka

2014/11/21 01:52

TaMahyuさん BlueMoonさんご回答ありがとうございます。それでしたら納得です！！ということは「該当するレコードすべてにロック」の段階では一気にロックを獲得することができず、１行ずつロックを獲得して最終的に対象のレコードが全ロックになるのですが、その過程でロックが競合してしまうことになるんですね。あとから気づいたことですが、pg_lockのlock_typeがrerationなのが少し気になりますが… ちなみに更新コストは高いです。純正のストリーミングレプリケーションを「完全同期型」で構築し、 pgpoolIIで分散構成になっておりますので。スレッドの趣旨としてはもう打ち切らないとですが、もう一つ疑問があります。この減少はPostgreSQL特有でしょうか？ MySQLでも起きうることでしょうか？技術的には起こっても仕方がない、というより防ぎようがない気もするのですが… PostgreSQLは社内で私しか推してなくて、だったらMySQLでいいじゃんって言われてしまうと切ないのです…

TaMaMhyu

2014/11/21 02:07

デッドロック自体はどのRDBMSでも発生すると思います。機能として、デッドロック検知でロールバックする物があるはずなので、ロールバックしたら一定時間後にリトライするような方針もあるのではないでしょうか？

行動規範の内容に同意します