オプティミスティック同時実行制御を行うことによって複数アクセスによる更新で後勝ちによるデータ損失を防ぐ (SQL Server)

Siden oppdatert : lørdag 27. juli 2024

ページ作成日 : søndag 12. mai 2024

動作確認環境

Visual Studio

Visual Studio 2022

.NET

.NET 8

Entity Framework Core

Entity Framework Core 8.0

SQL Server

SQL Server 2022

※上記は検証環境ですがほかのバージョンでも動作する可能性はあります。

制御のない後勝ち更新について

Web アプリケーションやクライアント・サーバーアプリケーションにおいてひとつのデータベースに対し複数人がアクセスしてデータを更新する場合があります。特に何も対処していなければ後に更新した人のデータが最新としてデータベースに反映されます。

通常、後に更新した人のデータが最新として反映されること自体特に問題はないのですが、同時に同じデータに複数の人がアクセスして更新しようとしたときに問題が発生することがあります。

例えば以下のような本のデータがあったとします。

パラメータ名	値
本の名前	データベースの本
値段	1000

このデータを編集する画面を２人が同時に開くと上の値が表示されます。Ａさんがこの本の金額に対して 500円値段を上げようとしています。Ｂさんが後から指示を受けてこの本の金額に対してさらに300円値段を上げようとしています。

もし２人が同時ではなく別々に値段を上げていれば本の値段は1800円になりますが、同時にアクセスした場合それぞれ 1500円, 1300円と登録してしまうのでどちらが登録しても1800円にはなりません。

ここでの問題点は後に更新した人が前に更新した情報を知ることなく更新できてしまう点にあります。

オプティミスティック同時実行制御

今回「オプティミスティック同時実行制御」を行うことによって前述の問題を解消することができます。どういう制御を行うかというのを簡単に説明すると、「同時にデータを編集しようとした場合は先勝ちにする」という制御になります。後に更新しようとした人は更新のタイミングでエラーになり登録できなくなります。

これだと新しくデータを登録できないと思われるかもしれませんが、これはあくまでも「同時に変更しようとした時だけ」の話なので、２人がまったく別なタイミングで編集することは可能です。もちろんその場合は最後に更新した人のデータが最新となります。

具体的にどういう処理で制御を行うかというと「データにバージョンを持たせる」ことによって実現することができます。例えば前述の例でいうと以下のようなデータを持つことになります。

パラメータ名	値
本の名前	データベースの本
値段	1000
バージョン	1

バージョンはレコードを更新するごとに１ずつ増えます。例えばＡさんが値段を1500円にするとバージョンは２になります。その際更新できる条件として更新前のバージョンがデータベース上のバージョンの同じであることという条件を付けます。Ａさんが更新するときはデータベース上のバージョンが１であり、現在編集している元データのバージョンが１なので更新できるというわけです。

この仕様をもとにＡさんとＢさんが同じデータを編集する場面を想定します。Ａさんが先に値段を1500円にしてデータベースを更新しようとしたときバージョンが同じなので更新できます。その際データベース上のバージョンは２となります。Ｂさんが1000円のデータを編集して1300円としてデータベースに更新しようとしますが、手元のバージョンが１であるのに対しデータベース上のバージョンはすでに２となっているので更新に失敗します。これが「オプティミスティック同時実行制御」の動作です。

Entity Framework Core ではこの「オプティミスティック同時実行制御」が標準で含まれているので比較的実装が簡単になっています。

ちなみに「オプティミスティック同時実行制御」は別名「楽観ロック」「楽観的ロック」とも呼ばれ、この名称で調べたり語られたりすることもあります。データは更新できなくなるけどデータの読み込みは可能というロック方式です。「楽観ロック」とは別のロック方法として「悲観ロック」という制御もあります。こちらは最初の人がデータを読み込んだ時点でデータ読み込みをロックさせ編集操作すらさせないという手法です。データを変更したのに更新できない、という問題は解決できますが、誰かがデータ編集中の時はほかの人はデータの編集画面も開けませんしロック解除に失敗すると永遠とデータがロックされるという問題も発生します。どちらにもメリット・デメリットがありますのでどっちを採用するかは運用次第になります。

データベースの作成

今回は SQL Server を対象にデータベースを先に作成してからコードを自動生成する手法で説明します。コードファーストで実装したい場合は今回自動生成されたコードを参考に逆手順で実装してください。

データベースの作成

SQL でも作れますが、GUI で作ったほうが簡単なので今回は GUI で作ってます。データベース名以外はデフォルトで作成しています。

テーブルの作成

以下の SQL で作成します。

USE [TestDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Book](
	[ID] [int] NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[Price] [money] NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
 CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ほとんどのパラメータについてはただのデータ更新用なので気にしなくていいですが、今回注目すべきパラメータは RowVersion と記載している列です。これがレコードのバージョン管理となります。型に timestamp を指定することによりレコードが更新されるたびに自動でバージョンが増えていきます。またこのバージョンはテーブル単位で管理されるので手動で設定しない限りは同じバージョンのレコードが存在することは基本的にありません。

レコードの追加

以下の SQL で追加できます。

begin transaction;
insert into Book ([ID],[Name],[Price]) values (1,'C#の本','1000.00');
insert into Book ([ID],[Name],[Price]) values (2,'VB.NETの本','1500.00');
insert into Book ([ID],[Name],[Price]) values (3,'SQL Serverの本','2000.00');
commit;

RowVersion 列は自動で設定されるので設定する必要はありません。

プロジェクトの作成とコードの自動生成

今回コンソールアプリで動作を確認します。プロジェクトの作成とコードの自動生成の手順は以下の Tips で説明しているのでここでは説明を省きます。

Entity Framework Core を使用して SQL Server にアクセスする準備を行う (データベースファースト)

生成されたコードは以下になります。

TestDatabaseDbContext.cs

using Microsoft.EntityFrameworkCore;

namespace ConcurrencySqlServer.Models.Database;

public partial class TestDatabaseDbContext : DbContext
{
  public TestDatabaseDbContext() { }

  public TestDatabaseDbContext(DbContextOptions<TestDatabaseDbContext> options) : base(options) { }

  public virtual DbSet<Book> Book { get; set; }

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see https://go.microsoft.com/fwlink/?LinkId=723263.
    => optionsBuilder.UseSqlServer("Data Source=<サーバー名>;Database=<データベース名>;user id=<ユーザー名>;password=<パスワード>;TrustServerCertificate=true");

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    modelBuilder.Entity<Book>(entity =>
    {
      entity.Property(e => e.ID).ValueGeneratedNever();
      entity.Property(e => e.RowVersion)
        .IsRowVersion()
        .IsConcurrencyToken();
    });

    OnModelCreatingPartial(modelBuilder);
  }

  partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

Book.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace ConcurrencySqlServer.Models.Database;

public partial class Book
{
  [Key]
  public int ID { get; set; }

  [StringLength(100)]
  public string Name { get; set; } = null!;

  [Column(TypeName = "money")]
  public decimal Price { get; set; }

  public byte[] RowVersion { get; set; } = null!;
}

オプティミスティック同時実行制御の動作確認

今回ひとつのアプリケーションで動かしているので厳密には同時にアクセスしているというわけではありませんがそれに近い形で実装してみたいと思います。

冒頭の例のように２つデータを取得しておき、それぞれ最初のデータをもとに更新したときにきちんと後者の更新者がエラーになるか確認します。

Program.cs

using ConcurrencySqlServer.Models.Database;
using Microsoft.EntityFrameworkCore;
using System.Text.Json;

Console.WriteLine("Hello, World!");

// 2人が別々にデータベースにアクセスする想定で２つのデータベースコンテキストを作成
Console.WriteLine("データベースコンテキストを作成します。");
using var dbContextA = new TestDatabaseDbContext();
using var dbContextB = new TestDatabaseDbContext();
Console.WriteLine("データベースコンテキストを作成しました。");
Console.WriteLine("");

// それぞれがデータを編集しようと読み込む
Console.WriteLine("ID:1 の Book を読み込みます。");
var bookA = dbContextA.Book.First(x => x.ID == 1);
var bookB = dbContextB.Book.First(x => x.ID == 1);
Console.WriteLine("ID:1 の Book を読み込みました。");
Console.WriteLine($"A  Book : {JsonSerializer.Serialize(bookA)}");
Console.WriteLine($"B  Book : {JsonSerializer.Serialize(bookB)}");
Console.WriteLine($"DB Book : {JsonSerializer.Serialize(dbContextC.Book.AsNoTracking().First(x => x.ID == 1))}");
Console.WriteLine("");


// A の人が最初にデータベースに更新する
bookA.Price += 500;
UpdateToDatabase(dbContextA, "A");

Console.WriteLine($"A  Book : {JsonSerializer.Serialize(bookA)}");
Console.WriteLine($"B  Book : {JsonSerializer.Serialize(bookB)}");
Console.WriteLine($"DB Book : {JsonSerializer.Serialize(dbContextC.Book.AsNoTracking().First(x => x.ID == 1))}");
Console.WriteLine("");

// そのあと B の人が最初にデータベースに更新する
bookB.Price += 300;
UpdateToDatabase(dbContextB, "B");

Console.WriteLine($"A  Book : {JsonSerializer.Serialize(bookA)}");
Console.WriteLine($"B  Book : {JsonSerializer.Serialize(bookB)}");
Console.WriteLine($"DB Book : {JsonSerializer.Serialize(dbContextC.Book.AsNoTracking().First(x => x.ID == 1))}");
Console.WriteLine("");

Console.WriteLine("処理を終了します。");


// データベースに更新するメソッド
void UpdateToDatabase(TestDatabaseDbContext dbContext, string updateUser)
{
  try
  {
    Console.WriteLine($"{updateUser} のデータベースコンテキストを変更保存します。");
    dbContext.SaveChanges();
    Console.WriteLine($"{updateUser} のデータベースコンテキストを変更保存しました。");
  }
  catch (DbUpdateConcurrencyException ex)
  {
    Console.WriteLine($"{updateUser} の更新でエラーが発生しました。");
    Console.WriteLine(ex.Message);
  }
}

長々と書いていますが大部分はコンソールへの書き出しになってます。

実行結果は以下のようになります。

Hello, World!
データベースコンテキストを作成します。
データベースコンテキストを作成しました。

ID:1 の Book を読み込みます。
ID:1 の Book を読み込みました。
A  Book : {"ID":1,"Name":"C#\u306E\u672C","Price":1000.0000,"RowVersion":"AAAAAAAAH2k="}
B  Book : {"ID":1,"Name":"C#\u306E\u672C","Price":1000.0000,"RowVersion":"AAAAAAAAH2k="}
DB Book : {"ID":1,"Name":"C#\u306E\u672C","Price":1000.0000,"RowVersion":"AAAAAAAAH2k="}

A のデータベースコンテキストを変更保存します。
A のデータベースコンテキストを変更保存しました。
A  Book : {"ID":1,"Name":"C#\u306E\u672C","Price":1500.0000,"RowVersion":"AAAAAAAAH2o="}
B  Book : {"ID":1,"Name":"C#\u306E\u672C","Price":1000.0000,"RowVersion":"AAAAAAAAH2k="}
DB Book : {"ID":1,"Name":"C#\u306E\u672C","Price":1500.0000,"RowVersion":"AAAAAAAAH2o="}

B のデータベースコンテキストを変更保存します。
B の更新でエラーが発生しました。
The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded. See https://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
A  Book : {"ID":1,"Name":"C#\u306E\u672C","Price":1500.0000,"RowVersion":"AAAAAAAAH2o="}
B  Book : {"ID":1,"Name":"C#\u306E\u672C","Price":1300.0000,"RowVersion":"AAAAAAAAH2k="}
DB Book : {"ID":1,"Name":"C#\u306E\u672C","Price":1500.0000,"RowVersion":"AAAAAAAAH2o="}

処理を終了します。

区分けして説明します。

Console.WriteLine("Hello, World!");

// 2人が別々にデータベースにアクセスする想定で２つのデータベースコンテキストを作成
Console.WriteLine("データベースコンテキストを作成します。");
using var dbContextA = new TestDatabaseDbContext();
using var dbContextB = new TestDatabaseDbContext();
using var dbContextC = new TestDatabaseDbContext();
Console.WriteLine("データベースコンテキストを作成しました。");

データベースコンテキストを２つ作成していますが、ひとつのデータベースコンテキストを共用するとデータを読み込んだ時にキャッシュされ同じインスタンスになってしまうのでそれぞれ別々にアクセスしていると想定してデータベースコンテキストを２つ作成しています。 dbContextC はデータベースの値確認用です。A や B でも代用できるので本来は必要ないのですが。

// それぞれがデータを編集しようと読み込む
Console.WriteLine("ID:1 の Book を読み込みます。");
var bookA = dbContextA.Book.First(x => x.ID == 1);
var bookB = dbContextB.Book.First(x => x.ID == 1);
Console.WriteLine("ID:1 の Book を読み込みました。");
Console.WriteLine($"A  Book : {JsonSerializer.Serialize(bookA)}");
Console.WriteLine($"B  Book : {JsonSerializer.Serialize(bookB)}");
Console.WriteLine($"DB Book : {JsonSerializer.Serialize(dbContextC.Book.AsNoTracking().First(x => x.ID == 1))}");

別々にアクセスしていることを想定して別々のデータベースコンテキストから Book を１つ読み込んでいます。この時点では何も変更していないのですべて同じ値です。

ID:1 の Book を読み込みます。
ID:1 の Book を読み込みました。
A  Book : {"ID":1,"Name":"C#\u306E\u672C","Price":1000.0000,"RowVersion":"AAAAAAAAH2k="}
B  Book : {"ID":1,"Name":"C#\u306E\u672C","Price":1000.0000,"RowVersion":"AAAAAAAAH2k="}
DB Book : {"ID":1,"Name":"C#\u306E\u672C","Price":1000.0000,"RowVersion":"AAAAAAAAH2k="}

最初に A さんの Book を変更して更新しています。更新処理は UpdateToDatabase メソッドにまとめており例外が発生すればメッセージを表示するようにしています。

// A の人が最初にデータベースに更新する
bookA.Price += 500;
UpdateToDatabase(dbContextA, "A");

Console.WriteLine($"A  Book : {JsonSerializer.Serialize(bookA)}");
Console.WriteLine($"B  Book : {JsonSerializer.Serialize(bookB)}");
Console.WriteLine($"DB Book : {JsonSerializer.Serialize(dbContextC.Book.AsNoTracking().First(x => x.ID == 1))}");

A のデータベースコンテキストを変更保存します。
A のデータベースコンテキストを変更保存しました。
A  Book : {"ID":1,"Name":"C#\u306E\u672C","Price":1500.0000,"RowVersion":"AAAAAAAAH2o="}
B  Book : {"ID":1,"Name":"C#\u306E\u672C","Price":1000.0000,"RowVersion":"AAAAAAAAH2k="}
DB Book : {"ID":1,"Name":"C#\u306E\u672C","Price":1500.0000,"RowVersion":"AAAAAAAAH2o="}

結果としては正常に更新され A の Book の Price と RowVersion は更新されています。また DB の値を直接読み込んでも更新された値になっています。 A で取得した RowVersion と DB にある RowVersion が両方とも AAAAAAAAH2k= だったので更新できました。更新したためバージョンが変わり AAAAAAAAH2o= となっています。

A を更新した後、同様に B を更新します。

// そのあと B の人が最初にデータベースに更新する
bookB.Price += 300;
UpdateToDatabase(dbContextB, "B");

Console.WriteLine($"A  Book : {JsonSerializer.Serialize(bookA)}");
Console.WriteLine($"B  Book : {JsonSerializer.Serialize(bookB)}");
Console.WriteLine($"DB Book : {JsonSerializer.Serialize(dbContextC.Book.AsNoTracking().First(x => x.ID == 1))}");

B のデータベースコンテキストを変更保存します。
B の更新でエラーが発生しました。
The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded. See https://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
A  Book : {"ID":1,"Name":"C#\u306E\u672C","Price":1500.0000,"RowVersion":"AAAAAAAAH2o="}
B  Book : {"ID":1,"Name":"C#\u306E\u672C","Price":1300.0000,"RowVersion":"AAAAAAAAH2k="}
DB Book : {"ID":1,"Name":"C#\u306E\u672C","Price":1500.0000,"RowVersion":"AAAAAAAAH2o="}

結果としては例外 DbUpdateConcurrencyException が発生し更新に失敗しています。 bookB.RowVersion が AAAAAAAAH2k= であるのに対しデータベース側の RowVersion がすでに AAAAAAAAH2o= となっているので不一致と判断され更新エラーとなっています。ローカル変数 bookB の Price が変更されていますが RowVersion が変わっていないことを確認できますし、データベース側の値も何も変わっていないことを確認できます。

まとめ

このように SQL Server と Entity Framework Core で自動生成されたコードを使用するとデフォルトで「オプティミスティック同時実行制御」が実装されるのでいくつかあるロックの種類の中では実装が非常に簡単です。ただあくまでも「更新対象のデータ破損」を防ぐというだけですので他にもデータが絡んできたりユーザー操作がある場合は適切に例外処理を行う必要があります。

また、今回は簡単なコンソールアプリケーションで実装したため RowVersion を一切管理していませんでしたが、 Web アプリケーションやクライアントアプリケーションでデータを読み込んだ後編集画面をはさむ場合、 RowVersion を何らかの方法で保持し更新時に適切に判定されるようにセットする必要がある点には注意してください。

ただし Entity Framework Core では変更追跡機能がついているため、DB から読み込んだ値に古い RowVersion を更新時にセットする場合、

bookB.RowVersion = <古い RowVersion>;

のようにセットしても「オプティミスティック同時実行制御」としては正しく判定されません。普通に RowVersion に値をセットしても変更した値としか認識されないため、以下、

dbContextB.Entry(bookB).Property("RowVersion").OriginalValue = <古い RowVersion>;

のように変更前の値を書き換える必要があります。

Entity Framework Core を使用して SQL Server にアクセスする準備を行う (データベースファースト)