Simulate Temporal Table error in SQL Server to demonstrate fix

96 Views Asked by At

I raised this question which has a potential good solution attached, but raising this as a new question

I have a table Invoice in the database which I am setting UpdatedDate to GetDate

If I deliberately run a method that updates this in parallel I can get the error I want to see

Data modification failed on system-versioned table 'table name' because transaction time was earlier at than period start time for affected records.

static async Task UpdateInvoiceAsync()
{
    var connectionString = @"";
    await using var cnn = new SqlConnection(connectionString);
    var sql = "UPDATE Invoice SET UpdatedDate = GETDATE() WHERE ID = 'CF10E62E-07EB-4F7B-ACB3-95C0BDEC9884'"; 

    SqlCommand command = new SqlCommand(sql, cnn);
    cnn.Open();
    await command.ExecuteScalarAsync();
}

static async Task MainAsync(string[] args)
{
    var tasks = new List<Task>();
    for (var taskNo = 1; taskNo < 1000; taskNo++)
    {
        tasks.Add(UpdateInvoiceAsync());
    }
    await Task.WhenAll(tasks);
}

My question is how to reproduce this within EF Core so that I can prove to my tester that the proposed fix below actually works?

options.UseSqlServer(connectionString,
sqlOptions =>
{
    sqlOptions.MigrationsAssembly(MigrationAssemblyName);
    sqlOptions.EnableRetryOnFailure(maxRetryCount: 10,
        TimeSpan.FromSeconds(30),
        new List<int>()
        {
            80131904
        });
        sqlOptions.CommandTimeout(240);
});

Its not possible to run queries in parallel via EF core so not sure how to simulate this?

Paul

0

There are 0 best solutions below