Created
November 2, 2017 03:17
-
-
Save ichiroku11/de7a85da400b8e5cfec5453f1bde9e48 to your computer and use it in GitHub Desktop.
EF Coreで楽観的同時実行制御
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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