Azure SQL DatabaseのMERGE文でデッドロック

Question

Azure WorkerRollでSQL Databaseに対して大量にMERGE文※を発行すると
Instanceが１つの場合は、Deadlockが発生しないのですが複数Instanceにした場合
SQL DatabaseでDeadlockが発生してしまいます。

※MERGE文は１文につきConnectionを接続/切断をしています。

SqlException
   Number            =[1205]
   ErrorCode         =[-2146232060]
   LineNumber        =[1]
   State             =[51]
   Message           =[Transaction (Process ID 1306) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.]

データ的にはKeyが重複するものは存在せず、
全てInsertになるデータなのですが、
なぜDeadlockが発生するのか原因が特定できず、
ご教示の程、お願いいたします。

Answer 1

お悩みはどちらでしょうか？

1. デッドロックが発生すること自体が問題
2. デッドロックが多すぎて処理がなかなか進まないのが問題

1.でしたら、考え方を切り換えてください。RDBはどんなに注意深く設計してもデッドロックが発生するものです。
エラーメッセージにも「Rerun the transaction」とありますよね。デッドロックで強制ロールバックがかかったら、数ミリ秒待ってから再実行すればいいだけです。

2.が問題なのでしたら、デッドロック頻度を軽減するためにもう少し検討してみましょう。ロックの対象はテーブル、行の他にインデックス、外部キーもあります。削除できる不要なインデックスはないでしょうか、などなど。

Comments

[R7038XX]

Keyが重複しない単純なInsertなのに、なぜ、Deadlockが発生するのか、
Azure Databaseで、Deadlock数が右肩上がりで計上されており、
先方に説明できないので、
1. デッドロックが発生すること自体が問題
で悩んでいます。

[yuba]

先方が納得してくれないのは難しい状況ですね。

先方が中途半端な知識を持ってしまっているのは仕方が無いとして我々エンジニアは正確な知識を持ちましょう。RDBでデッドロックを防ぐのは不可能です。「Keyが重複しない単純なInsert」ももちろんデッドロックを発生させます。

どんなメカニズムでINSERTのみの単純なMERGEがデッドロックを発生させるか？ それを机上で再現することも可能ですが、王道はプロファイラを使って実際にデッドロック状態を分析することです。

SQL Server Profilerをつないでデッドロックログを採取・分析してください。デッドロックグラフというわかりやすい模式図にしてくれます。

おそらく、主キーインデックスの共有ロックを持ったトランザクション同士がそれを排他ロックに昇格しようとして待ち待ちになっている図が確認できるのではと思われます。