【SQL Server】UPDLOCKを使ってデッドロックエラーを防ぐ

SQL Serverの更新ロック（UPDLOCK）についての基礎的な説明です。更新ロックの目的や互換性、デッドロックの防止方法について具体例を交えて解説しています。

SQL Serverの更新ロック（UPDLOCK）について調査したので、基礎的な内容になりますが紹介いたします。

更新ロック（UPDLOCK）とは

SQL Serverのロックモードの一種です。

ロックモード	説明
共有 (S)	SELECT ステートメントなど、データの変更や更新を伴わない読み取り操作で使用します。
更新 (U)	更新可能なリソースに使用します。複数のセッションがリソースを読み取り、ロックして、後で更新する可能性がある場合に発生する一般的な形式のデッドロックを防ぎます。
排他 (X)	INSERT、UPDATE、DELETE などのデータ変更操作に使用します。複数の更新操作により 1 つのリソースを同時に更新しないようにするためのロックモードです。
インテント	ロック階層を設定するのに使用します。インテントロックには、インテント共有 (IS)、インテント排他 (IX)、およびインテント排他付き共有 (SIX) があります。
スキーマ	テーブルのスキーマに依存する操作を行うときに使用します。スキーマロックには、スキーマ修正 (Sch-M) およびスキーマ安定度 (Sch-S) があります。
一括更新 (BU)	データを一括でテーブルにコピーするときに TABLOCK ヒントを指定して使用します。
キー範囲	トランザクション分離レベルが SERIALIZABLE のとき、クエリにより読み取られる行の範囲を保護します。シリアル化可能トランザクションのクエリを再度実行した場合に対象となるような行を、他のトランザクションが挿入できなくなります。

更新ロックの目的

更新ロックは、データを読み取ってから更新するといったトランザクションで更新の予約を行うために使用されるロックです。 SELECT文にWITH (UPDLOCK)を付けることでデータ読み取り時に更新ロックを取得し、データ更新時に排他ロックへ昇格します。これにより、変換デッドロックを防ぐことができます。

更新ロックの互換性

自分が取得しようとしているロック	すでにかけられているロック
自分が取得しようとしているロック	共有（S）	更新（U）	排他（X）
共有（S）	可	可	不可
更新（U）	可	不可	不可
排他（X）	不可	不可	不可

更新ロック（U）は共有ロック（S）のみ互換性があり、更新ロック（U）および排他ロック（X）とは互換性がありません。

どのようにデッドロックを防ぐか

次のような「Test1テーブルを読み取り→Test1テーブルに挿入」を行う2つのトランザクションがあります。

トランザクションA

-- トランザクションA
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;
              SELECT * FROM Test1;
              -- ここで一時停止して、トランザクションBを実行する
              WAITFOR DELAY '00:00:10'; -- 10秒待機
              INSERT INTO Test1 VALUES (1);
COMMIT;

トランザクションB

-- トランザクションB
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;
              SELECT * FROM Test1;
              -- ここで一時停止して、トランザクションAのINSERTを待機する
              WAITFOR DELAY '00:00:10'; -- 10秒待機
              INSERT INTO Test1 VALUES (1);
COMMIT;

これを次のように実行すると、変換デッドロックが発生します。

1 ：トランザクションAを開始し、SELECT文を実行して共有ロック（S）を取得
2 ：トランザクションBを開始し、SELECT文を実行して共有ロック（S）を取得
3 ：トランザクションAがINSERT文を実行し、排他ロック（X）に変換しようとしますが、トランザクションBが共有ロックを保持しているため待機
4 ：トランザクションBがINSERT文を実行し、排他ロック（X）に変換しようとしますが、トランザクションAが共有ロックを保持しているため待機 ←デッドロック!!

次に、上記のトランザクションのSELECT文にWITH (UPDLOCK)を付け、更新ロックに変更します。

トランザクションA

-- トランザクションA
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;
              SELECT * FROM Test1 WITH (UPDLOCK);
              -- ここで一時停止して、トランザクションBを実行する
              WAITFOR DELAY '00:00:10'; -- 10秒待機
              INSERT INTO Test1 VALUES (1);
COMMIT;

トランザクションB

-- トランザクションB
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;
              SELECT * FROM Test1 WITH (UPDLOCK);
              -- ここで一時停止して、トランザクションAのINSERTを待機する
              WAITFOR DELAY '00:00:10'; -- 10秒待機
              INSERT INTO Test1 VALUES (2);
COMMIT;

これを同じ手順で実行すると、デッドロックは発生せず、値を挿入することができました。

1 ：トランザクションAを開始し、SELECT文を実行して更新ロック（U）を取得
2 ：トランザクションBを開始し、SELECT文を実行して更新ロック（U）を取得しようとしますが、トランザクションAが更新ロックを保持しているため待機
3 ：トランザクションAがINSERT文を実行し、排他ロック（X）に変換。値を挿入し、コミット（トランザクションA終了）
4 ：トランザクションBが待機状態から解放され、更新ロック（U）を取得
5 ：トランザクションBがINSERT文を実行し、排他ロック（X）に変換。値を挿入し、コミット（トランザクションB終了）