SQL Serverと.NET(C#)による楽観的ロックの実装方法

この記事は公開から3年以上経過しています。

Microsoft SQL Serverを使う開発で、大人の事情などでトランザクション(スナップショット)分離レベルではなく自前のレコードバージョン管理方式で楽観的ロックを実装しなければならないときに、SQL Server 2005で追加されたrowversionを利用して.NET(C#)で楽観的ロックを実現する方法を紹介します。
PostgreSQLで同様の楽観的ロックを行いたい場合はこちらをご参照ください。

DBテーブル定義と検証データ

DBはSQL Server 2019でSQL Server Management Studioの既定の構成で作成しています。

テーブル定義

CREATE TABLE USER_MASTER(
    ID BIGINT PRIMARY KEY,
    NAME NVARCHAR(50),
    VERSION ROWVERSION
);

テストデータ

INSERT INTO USER_MASTER
VALUES (100, 'Foo', NULL);

動作検証用サンプルソースコード(C#)

接続文字列は検証環境に合わせて適宜修正してください。

using System;
using System.Data;
using System.Data.SqlClient;

namespace SQLServerOptimisticLockTest210220
{
    internal class Program
    {
        // データ取得SQL
        private static readonly string SQL_GET_USER
= @"
SELECT ID,
    NAME,
    CAST(VERSION AS BIGINT) AS VERSION
FROM USER_MASTER
WHERE ID = @USER_ID
";

        // データ更新SQL
        private static readonly string SQL_UPDATE_USER
= @"
UPDATE USER_MASTER
SET NAME = @NAME OUTPUT CAST(INSERTED.VERSION AS BIGINT) AS UPDATED_VERSION
WHERE ID = @USER_ID
    AND VERSION = @VERSION
";

        private static void Main(string[] args)
        {
            using (var sqlCon = new SqlConnection("Server=localhost;Database=LOCK_TEST;Integrated Security=true;"))
            {
                sqlCon.Open();

                User columns;

                // ID=100のデータを取得
                using (var sqlCmd = new SqlCommand(SQL_GET_USER, sqlCon))
                {
                    sqlCmd.Parameters.Add(
                           new SqlParameter("@USER_ID", SqlDbType.BigInt) { Value = 100 });

                    using (var reader = sqlCmd.ExecuteReader())
                    {
                        reader.Read();

                        columns = new User
                        {
                            Id = reader.GetInt64(0),
                            Name = reader.GetString(1),
                            Version = reader.GetInt64(2)
                        };

                        Console.WriteLine($"Id:{columns.Id} Name:{columns.Name} Version:{columns.Version}");
                    }
                }

                // 競合なしで更新した場合の動作
                using (var sqlCmd = new SqlCommand(SQL_UPDATE_USER, sqlCon))
                {
                    sqlCmd.Parameters.AddRange(new[]
                    {
                        new SqlParameter("@USER_ID", SqlDbType.BigInt) { Value = columns.Id },
                        new SqlParameter("@NAME", SqlDbType.NVarChar) { Value = "Bar" },
                        new SqlParameter("@VERSION", SqlDbType.BigInt) { Value = columns.Version },
                    });
                    var updatedVersion = sqlCmd.ExecuteScalar();
                }

                // 競合ありでの更新した場合の動作(バージョン逆行)
                using (var sqlCmd = new SqlCommand(SQL_UPDATE_USER, sqlCon))
                {
                    sqlCmd.Parameters.AddRange(new[]
                    {
                        new SqlParameter("@USER_ID", SqlDbType.BigInt) { Value = columns.Id },
                        new SqlParameter("@NAME", SqlDbType.NVarChar) { Value = "Baz" },
                        new SqlParameter("@VERSION", SqlDbType.BigInt) { Value = columns.Version },
                    });
                    var updatedVersion = sqlCmd.ExecuteScalar();
                    if (updatedVersion == null)
                        Console.WriteLine("Collision occurred!");
                }

                Console.ReadKey();
            }
        }

        // データ
        private struct User
        {
            // ユーザーID
            public long Id;

            // ユーザー名
            public string Name;

            // バージョン
            public long Version;
        }
    }
}

上記プログラムを実行すると

1. データを取得
2. 手順1のデータ取得時のレコードバージョンを用いてデータを更新 → 成功
3. 再度手順1のデータ取得時のレコードバージョンを用いてデータを更新 → 失敗

の順に処理を行い、手順3で楽観的ロックが行われることを確認できます。

2021.5.19追記：
ちなみに、本例ではVERSIONカラムをプログラムで扱いやすいようにバイトデータであるBIGINTにマッピング(キャスト)していますが、この値はレコード更新判定以外の目的には利用できません。
何らかの理由で意味のある数値として扱いたい場合は、バイトデータのまま取得してエンディアンと符号を考慮してプログラム側で数値処理するなどの対応が必要です。

以上です。