DBのロック順序

Question

テラテイルデッドロックの質問←ここを見ていて、疑問に思ったのですが、トランザクションを使えるDBでの行ロックで、複数行がロック対象の場合（例えば、id=1,flg=0とid=2,flg=0の２行)、id=1の行からロックするかid=2の行からロックするかは、予め指定してロックすることはできますか？

実際のロックでは何百行ロックとかなる場合、昇順にロックしていくとか降順にロックしていくといった指定は可能でしょうか？

Answer 1

RDBはそういう場合のロック取得順序を保証していません。実験で知見が得られる場合もありますがバージョンが上がればどうなるかわからないどころか、同じバージョン、同じテーブルでも使っているうちに実行プランが変化してロック取得順序が変わることはありえます。

ではどうやってデッドロックを対策するかなのですが、ここは頭を切り替える必要があり、RDBではデッドロックは防げないものと認識しなければなりません。そして、発生したらトランザクションをリトライというアプリケーションプログラミングが最大の対策となります。

デッドロックの頻度を下げるための対策は一筋縄ではいきませんが、ロック順序整理という手続き型プログラミングで行われる対策はあまり有意義ではなく、むしろ各トランザクションがなるべく多くのオブジェクトを触らずにシンプルに終えられるようなデータ設計がキモになってきます。

Comments

[tixure55]

運用方法次第で各々のトランザクションのデッドロックを回避することはできそうですね。というかほとんどの案件で同じ行に同じタイミングでアクセスしないというような工夫離されていると思います。

Answer 2

Oracleに関してはSELECT ... FOR UPDATEは ON句を含むWHERE句で指定した行についてロックは保証されますが、WHERE句内での順番の保証はないと思います。サポート契約が有効でしたらオラクル社カスタマ・サポート・センターに確認ください。
5年位前に調べた情報では、ダーティリードの回避をきちんと保証しているのは Oracle だけでした。

Answer 3

MySQLで、idがオートインクリメントで入っている数千万レコードで速度が遅いテーブルに対し、
A)select * from table_a order by a desc for update;
B)select * from table_b order by a for update;
を実行した直後に別トランザクションで
id = 1をselect for updateで検索を試みました。

結果、Aはid=1で検索した方が先にロックをかけ
Bはselect * from table_b order by a for update;が優先されました。

順序だけであればorder by である程度はロックの順序が優先される結果になっていますが、
これに関し、正しい答えは得にくいので、サポート契約している人に
直接確認してもらうのが最も早い手段と思います。

Comments

[anonymouskawa]

あと、根本的な考え方としては「ロックの順序は考慮しない」なので
順序の如何にかかわらずロック対象となっているレコードはどの時点でロックされても問題がない作りにすべきかなと。
今はOrder by で順序が効いているなとは思いますけど、今後「一回すべてのレコードを一気にロックしてからソートをする」という動作に変わらないとも限らないので。