SQLServerで変換デッドロックの回避

デッドロックといえば、２つのテーブル（ここではA、B）に対して、トランザクション１はA→Bの順で更新、トランザクション２はB→Aの順で更新しようとした場合に発生する、というのは良く知られていると思います。MicrosoftのTechNetによると、このデッドロックはサイクルデッドロックと言います。
こちらの「3.3 デッドロックの種類 2.サイクルデッドロック」を参照してください。

一方で１つのテーブルに対して、複数のトランザクションから参照・更新を行う場合もデッドロックが発生します。TechNetによると、こちらは変換デッドロックと言います。
こちらの「3.3 デッドロックの種類 1.変換デッドロック」を参照してください。

今回遭遇したのは変換デッドロックの方で、１つのテーブルに対して、２つのトランザクションから同一のキーで参照・更新を行おうとしてデッドロックが発生しました。今までこのタイプのデッドロックに遭遇したことはありませんでしたが、今回の対応で一応の回避策が分かったのでそれをまとめておきます。

JDBC接続文字列

リファレンス等を読むと、SQLServerを利用する場合のJDBC接続文字列は以下のようにします。

jdbc:sqlserver://192.168.1.1:1433;databaseName=dbnamae;

ただし、このままだとデータの読み出しにカーソルが使えませんので、以下のようにパラメータを追加する必要があります。

jdbc:sqlserver://192.168.1.1:1433;databaseName=dbnamae;selectMethod=cursor

selectMethod～が追加した部分となります。javaでSQLServerを利用する場合には、必ず追加しなければならない重要なパラメータです。

SELECT ～ FOR UPDATE

SQLServerではPostgreSQLなどで使うSELECT ～ FOR UPDATEが利用出来ません。その代わりにロックヒントなるものを追加して、更新ロックを掛けるとSELECT ～ FOR UPDATEと同等の処理を行うことができます。

SELECT
    hoge.key
    , hoge.value
FROM
    hoge WITH(ROWLOCK, UPDLOCK)
WHERE
    hoge.key = 1;

FROM句のテーブル名後ろにあるWITH～がロックヒントです。上記では行ロックと更新ロックを掛ける場合のSQL文となっています。ロックヒントで指定できるロックの種類は、こちらを参照してください。

たったこれだけのことなのに、えらく時間が掛かってしまいました。使い慣れていないDBはやっぱり怖いですね。気になるのはOracleやPostgreSQLではこの変換デッドロックと同じ現象が起こるのでしょうか。今までの経験と少し調べてみたところ見つかりませんでしたが。