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

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

過去のエントリ「SQL Serverと.NET(C#)による楽観的ロックの実装方法」に続いて、今回はPostgreSQLを使う開発で、トランザクション識別情報列xminを用いて楽観的ロックを実現する方法を紹介します。

DB定義とテストデータ

DBはPostgreSQL 13、pgAdmin4で作成しています。

CREATE TABLE USER_MASTER(
  ID BIGINT PRIMARY KEY,
  NAME TEXT);

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

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

PostgreSQLへの接続にはADO.NETプロバイダNpgsqlを利用しています。
接続文字列は検証環境に合わせて適宜修正してください。

using Npgsql;
using NpgsqlTypes;
using System;
using System.Data;

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

        // データ更新SQL
        private static readonly string SQL_UPDATE_USER
= @"
UPDATE USER_MASTER
SET NAME = @NAME
WHERE ID = @USER_ID
    AND XMIN = @VERSION RETURNING XMIN AS UPDATED_VERSION
";

        private static void Main(string[] args)
        {
            using (var sqlCon = new NpgsqlConnection("Host=localhost;Database=test_db;User Id=user;Password=password;"))
            {
                sqlCon.Open();

                User columns;

                // ID=100のデータを取得
                using (var sqlCmd = new NpgsqlCommand(SQL_GET_USER, sqlCon))
                {
                    sqlCmd.Parameters.Add(
                           new NpgsqlParameter("@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.GetFieldValue<uint>(2)
                        };

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

                // 競合なしで更新した場合の動作
                using (var sqlCmd = new NpgsqlCommand(SQL_UPDATE_USER, sqlCon))
                {
                    sqlCmd.Parameters.AddRange(new[]
                    {
                        new NpgsqlParameter("@USER_ID", NpgsqlDbType.Bigint) { Value = columns.Id },
                        new NpgsqlParameter("@NAME", NpgsqlDbType.Text) { Value = "Bar" },
                        new NpgsqlParameter("@VERSION", NpgsqlDbType.Xid) { Value = columns.Version },
                    });
                    var updatedVersion = sqlCmd.ExecuteScalar();
                }

                // 競合ありで更新した場合の動作(バージョン逆行)
                using (var sqlCmd = new NpgsqlCommand(SQL_UPDATE_USER, sqlCon))
                {
                    sqlCmd.Parameters.AddRange(new[]
                    {
                        new NpgsqlParameter("@USER_ID", NpgsqlDbType.Bigint) { Value = columns.Id },
                        new NpgsqlParameter("@NAME", NpgsqlDbType.Text) { Value = "Baz" },
                        new NpgsqlParameter("@VERSION", NpgsqlDbType.Xid) { 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 uint Version;
        }
    }
}

プログラムを実行すると

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

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

参考ウェブサイトなど

• PostgreSQL Docs
  5.5. System Columns

• npgsql.org
  Npgsql – .NET Access to PostgreSQL

以上です。