Optimistic Concurrency Control Prevents Data Loss Due to Last-Win Updates with Multiple Accesses (SQL Server)

Page update date : Saturday, July 27, 2024

Page creation date : Sunday, May 12, 2024

Operating environment

Visual Studio

Visual Studio 2022

.NET

.NET 8

Entity Framework Core

Entity Framework Core 8.0

SQL Server

SQL Server 2022

* The above is a verification environment, but it may work with other versions.

About Winning Updates After No Control

In a web application or a client-server application, multiple people may access and update data from a single database. If nothing is done in particular, the data of the person who updated it later will be reflected in the database as the latest.

Normally, there is no particular problem that the data of the person who updated later is reflected as the latest. Problems can arise when multiple people try to access and update the same data at the same time.

For example, suppose you have the following book data.

Parameter Name	Value
Name of the book	Database Books
price	1000

If two people open the screen to edit this data at the same time, the above value will be displayed. Mr./Ms. A is trying to raise the price of this book by 500 yen. Mr./Ms. B is later instructed to raise the price of this book by another 300 yen.

If the two of them raise the price separately instead of at the same time, the price of the book will be 1800 yen. If you access it at the same time, it will be registered as 1500 yen and 1300 yen, respectively, so it will not be 1800 yen no matter which one registers.

The problem here is that the person who updated later can update it without knowing the information that was previously updated.

Optimistic concurrency

The aforementioned problem can be solved by performing "optimistic concurrency control" this time. To explain simply, what kind of control is to "win first if you try to edit the data at the same time". Those who try to renew later will receive an error at the timing of the update and will not be able to register.

You may think that you can't register new data with this, but this is only "when you try to change it at the same time". It is possible for two people to edit at completely different times. Of course, in that case, the data of the last updated person will be the latest.

Specifically, what kind of processing control can be achieved by "having a version of the data". For example, in the above example, you will have the following data.

Parameter Name	Value
Name of the book	Database Books
price	1000
version	1

The version is incremented by 1 for each record update. For example, if Mr./Ms. A sets the price to 1500 yen, the version will be 2. At that time, the condition that the update can be made is that the version before the update is the same as the version on the database. When Mr./Ms. updates it, the version on the database is 1, and the version of the original data currently being edited is 1, so it can be updated.

Based on this specification, assume a situation where Mr./Ms. A and Mr./Ms. B edit the same data. When Mr./Ms. first set the price to 1500 yen and tried to update the database, the version was the same, so it could be updated. In that case, the version on the database will be 2. Mr./Ms. B tries to edit the data of 1000 yen and update it to the database as 1300 yen. The update fails because the version at hand is 1, but the version on the database is already 2. This is how optimistic concurrency works.

Entity Framework Core includes this "optimistic concurrency" out of the box, making it relatively easy to implement.

By the way, "optimistic concurrency" is also known as "optimistic lock" or "optimistic lock", and it is sometimes examined and talked about by this name. It is a locking method that the data cannot be updated, but the data can be read. There is also a control called "pessimistic lock" as another locking method other than "optimistic lock". This is a method that locks the data loading when the first person reads the data and does not even allow editing operations. It can solve the problem that the data cannot be updated even though it has been changed, but when someone is editing the data, other people cannot open the data editing screen, and if the unlock fails, the data will be locked forever. Both have advantages and disadvantages, so it depends on the operation which one to adopt.

Creating a database

In this article, I will explain how to create a database for SQL Server first and then automatically generate code. If you want to implement it in a code-first manner, please refer to the automatically generated code this time and implement it in the reverse procedure.

Creating a database

You can also make it in SQL, but it's easier to make it with a GUI, so I'm making it with a GUI this time. Except for the database name, it is created by default.

Create a table

Create it with the following 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

You don't have to worry about most of the parameters because they're just for data updates. The parameter of interest this time is RowVersion the column that describes . This is record versioning. By specifying as the timestamp type, the version is automatically incremented each time the record is updated. Also, since this version is managed on a per-table basis, there is basically no record of the same version unless you set it manually.

Add a record

You can add it with the following 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 You don't need to set the columns because they're set automatically.

Create a project and automatically generate code

This time, we will check the operation with the console application. The steps to create a project and automatically generate code are described in the following tips, so I won't go into them here.

Prepare to access SQL Server using Entity Framework Core (database-first)

The generated code is as follows:

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!;
}

Checking the operation of optimistic concurrency control

Since we are running it in one application this time, we are not strictly accessing it at the same time, but we would like to implement it in a form close to it.

As in the example at the beginning, two pieces of data are acquired, and when each is updated based on the first data, check whether the latter updater will get an error.

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);
  }
}

It's a long story, but most of it is written to the console.

The result of the execution is as follows.

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="}

処理を終了します。

I'll break it down into sections.

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

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

I'm creating two database contexts, If you share one database context, it will be cached when the data is read and it will be the same instance. Assuming that each is accessed separately, two database contexts are created. dbContextC is for checking the values in the database. I don't really need it because A or B can be substituted.

// それぞれがデータを編集しようと読み込む
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))}");

Assuming that they are accessed separately, they are reading one from different database contexts Book . At this point, we haven't changed anything, so they're all the same.

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="}

First, I changed and updated Mr./Ms.'s book. The update process is UpdateToDatabase summarized in a method, and a message is displayed when an exception occurs.

// 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="}

As a result, it has been successfully updated, and Price RowVersion and of Book A have been updated. Also, if you read the DB value directly, the updated value will be the same as the updated value. I was able to update it because both got in RowVersion A and RowVersion in DB were AAAAAAAAH2k= . The version has changed AAAAAAAAH2o= due to the update.

After updating A, update B in the same way.

// そのあと 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="}

The result is an exception DbUpdateConcurrencyException and the update has failed. bookB.RowVersion AAAAAAAAH2k= is , but RowVersion since is already AAAAAAAAH2o= , it is judged to be a mismatch and an update error occurs. You can see that the Price local variable bookB has changed, but RowVersion the You can see that the values on the database side have not changed at all.

Summary

This is the easiest of the several lock types to implement, because when you use the auto-generated code in SQL Server and Entity Framework Core, optimistic concurrency is implemented by default. However, since it is only to prevent "data corruption to be updated", it is necessary to properly handle exceptions when other data is involved or user operations are involved.

Also, this time I didn't manage anything because I implemented RowVersion it in a simple console application. If you want to insert the edit screen after loading the data in a web application or client application, RowVersion in some way so that it can be properly determined when it is updated.

However, Entity Framework Core has a change tracking function, so if you want to set the old RowVersion to the value read from the DB at the time of update,

bookB.RowVersion = <古い RowVersion>;

Even if it is set as follows, it will not be correctly judged as "optimistic concurrency control". RowVersion Even if you set the value to normally, it will only be recognized as the changed value, so the following is

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

It is necessary to rewrite the value before the change.

Prepare to access SQL Server using Entity Framework Core (database-first)