Skip to content

Instantly share code, notes, and snippets.

@ichiroku11
Created November 2, 2017 03:17
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save ichiroku11/de7a85da400b8e5cfec5453f1bde9e48 to your computer and use it in GitHub Desktop.
Save ichiroku11/de7a85da400b8e5cfec5453f1bde9e48 to your computer and use it in GitHub Desktop.
EF Coreで楽観的同時実行制御
use Test;
begin tran;
-- テーブル作成
drop table if exists dbo.Monster;
create table dbo.Monster(
Id int,
Name nvarchar(20) not null,
Version rowversion not null,
constraint PK_Monster primary key(Id)
);
-- データを投入
insert into dbo.Monster(Id, Name)
output inserted.*
values(1, N'スライム');
commit;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.SqlClient;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Abstractions;
namespace ConsoleApp {
// モンスター
public class Monster {
public int Id { get; set; }
public string Name { get; set; }
// Data Annotationsの場合
[Timestamp]
public byte[] Version { get; set; }
}
// ロガープロバイダー
public class AppLoggerProvider : ILoggerProvider {
public ILogger CreateLogger(string categoryName) {
if (string.Equals(categoryName, DbLoggerCategory.Database.Command.Name)) {
return new ConsoleLogger();
}
return NullLogger.Instance;
}
public void Dispose() {
}
// ロガー
private class ConsoleLogger : ILogger {
public IDisposable BeginScope<TState>(TState state) => null;
// 情報レベル以上のログを有効にする
public bool IsEnabled(LogLevel logLevel) => logLevel >= LogLevel.Information;
public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception,
Func<TState, Exception, string> formatter) {
Console.WriteLine(formatter(state, exception));
Console.WriteLine("---");
}
}
}
// DBコンテキスト
public class AppDbContext : DbContext {
public AppDbContext(bool tracking = false) {
ChangeTracker.QueryTrackingBehavior = tracking
? QueryTrackingBehavior.TrackAll
: QueryTrackingBehavior.NoTracking;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
var connectionString = new SqlConnectionStringBuilder {
DataSource = ".",
InitialCatalog = "Test",
IntegratedSecurity = true,
}.ToString();
optionsBuilder.UseSqlServer(connectionString);
}
protected override void OnModelCreating(ModelBuilder modelBuilder) {
modelBuilder.Entity<Monster>().ToTable(nameof(Monster));
// Fluent APIの場合
// Timestamp属性の代わりにこちらでも
/*
modelBuilder.Entity<Monster>()
.Property(monster => monster.Version)
.ValueGeneratedOnAddOrUpdate()
.IsConcurrencyToken();
*/
}
}
class Program {
static void Main(string[] args) {
using (var dbContext = new AppDbContext()) {
// ロガープロバイダーを設定
var serviceProvider = dbContext.GetInfrastructure();
var loggerFactory = serviceProvider.GetService<ILoggerFactory>();
loggerFactory.AddProvider(new AppLoggerProvider());
}
// 同時に更新する1つ目のエンティティ
var monster1 = default(Monster);
using (var dbContext = new AppDbContext()) {
monster1 = dbContext.Set<Monster>().Find(1);
}
// 同時に更新する2つ目のエンティティ
// 楽観的同時実行制御による更新失敗を確認したいため、コピーを作っておく
var monster2 = new Monster {
Id = monster1.Id,
Name = monster1.Name,
Version = monster1.Version.Clone() as byte[],
};
// 1つ目のエンティティを更新 => 成功する
using (var dbContext = new AppDbContext(tracking: true)) {
monster1.Name = "スライムベス";
dbContext.Set<Monster>().Attach(monster1).State = EntityState.Modified;
dbContext.SaveChanges();
// 実行されるSQL
// WHERE句にVersionカラムが含まれている
/*
Executed DbCommand (0ms) [Parameters=[@p1='?', @p0='?' (Size = 4000), @p2='?' (Size = 8)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Monster] SET [Name] = @p0
WHERE [Id] = @p1 AND [Version] = @p2;
SELECT [Version]
FROM [Monster]
WHERE @@ROWCOUNT = 1 AND [Id] = @p1;
*/
}
// 2つ目のエンティティを更新 => 失敗する(上記SaveChangesでVersionカラムが更新されているため)
using (var dbContext = new AppDbContext(tracking: true)) {
monster2.Name = "バブルスライム";
dbContext.Set<Monster>().Attach(monster2).State = EntityState.Modified;
try {
// SaveChangesを呼び出すと例外が発生する
dbContext.SaveChanges();
} catch (DbUpdateConcurrencyException exception) {
Console.WriteLine(exception.Message);
/*
Database operation expected to affect 1 row(s) but actually affected 0 row(s).
Data may have been modified or deleted since entities were loaded.
See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
*/
}
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment