0

I'm encountering an issue when executing transactions on system-versioned tables (SQL Azure) in my ASPNET Core + Entity Framework app.

In my database, there are two system-versioned tables:

  • dbo.Entities
  • dbo.Tasks (has an external reference to Entities)

The affected method performs following actions:

  1. Retrieve main entity.
  2. Retrieve associated task by entity.
  3. Perform business logic outside transaction.
  4. Start transaction.
  5. Update main entity.
  6. Update task's steps.
  7. If task should be completed, append a message.
  8. Complete the task.
  9. Save changes and commit the transaction.

Here's the method I'm using:

public async Task<Result<GenericOutcomeResponse>> HandleGenericOutcomeAsync(int entityId, GenericOutcomeRequest request, CancellationToken cancellationToken)
{
    // 1. retrieve main entity
    var entity = await _dbContext.Entities.FindAsync([entityId], cancellationToken: cancellationToken);

    // 2. Retrieve associated task by entity
    var task = await _taskService.GetOpenedTaskByTypeIdAsync(entityId, TaskTypeEnum.TypeA, true, cancellationToken);

    // 3. Perform business logic outside transaction
    var upcomingPhase = await CalculateNextStepAsync(entityId, request, cancellationToken);
    var shouldCompleteTask = upcomingPhase is StepEnum.Done or StepEnum.RequiresVerification;

    // 4. Start transaction
    var transaction = await _dbContext.Database.BeginTransactionAsync(cancellationToken);

    try
    {
        // 5. Update main entity
        switch (request.PreviousPhase)
        {
            case StepEnum.StepA:
                entity.SomeProperty = request.StepAData!.SomeValue;
                break;

            case StepEnum.StepB:
                entity.Notes = request.StepBData!.Note;
                break;
        }

        // 6. Update task's steps.
        UpdateTaskSteps(task, (int)request.PreviousPhase, (int)upcomingPhase);

        // 7. If task should be completed, append a message.
        if (shouldCompleteTask)
        {
            task.Messages.Add(new Messages
            {
                Message = message
            });
        }

        // 8. Complete the task.
        await _taskService.CompleteAsync(task.Id, cancellationToken);

        // 9. Save changes and commit the transaction.
        await _dbContext.SaveChangesAsync(cancellationToken);
        await transaction.CommitAsync(cancellationToken);
        return new GenericOutcomeResponse(upcomingPhase);
    }
    catch
    {
        await transaction.RollbackAsync(cancellationToken);
        return GenericServiceErrors.GenericError;
    }
    finally
    {
        // Dispose the transaction if it was started locally
        if (!hasExternalTransaction)
            await transaction.DisposeAsync();
    }
}

private void UpdateTaskSteps(GenericTask task, int previousPhase, int upcomingPhase)
{
    // Get the entity tracking entry for the task
    var entry = _dbContext.Entry(task);

    // Check if the task is not currently tracked by the context
    var isDetached = entry.State == EntityState.Detached;

    // Attach the task to the context if it's detached
    if (isDetached)
        _dbContext.Attach(task);

    // Update the task's phase tracking properties
    task.SetPreviousPhase(previousPhase);
    task.SetUpcomingPhase(upcomingPhase);

    // Mark the phase property as modified if the entity was detached
    if (isDetached)
        entry.Property(x => x.Steps).IsModified = true;
}

The problem occurs when I run this method simultaneously (for different entities) and I get the following error:

An error occurred while saving the entity changes. See the inner exception for details. Data modification failed on system-versioned table 'server.dbo.Entities' because transaction time was earlier than period start time for affected records.

Has anyone encountered this issue before? Any suggestions on how to mitigate this?

5
  • This error often happens with concurrent updates on system-versioned tables. A retry on failure usually helps. Commented May 26 at 11:41
  • Hi Balaj, thank you. Increasing the IsolationLevel should I solve the problem? I would like to solve the problem at the root, in any case I will also evaluate a retry strategy. Thank you Commented May 26 at 14:45
  • I suspect there might also be a bit of an issue with handling detached entities. I'm not sure about this period start time, but with concurrency tokens, allowing for detached entities will easily run into issues where the entity being "attached" is actually stale and the only way to know this prior to saving is by querying the fresh data anyways when modifying state, defeating the "scratching the itch" to avoid a read. However it means when save fails due to concurrency, the detached entity has to be discarded/refreshed. Commented May 26 at 21:01
  • This code approach with Attach() can also run foul of other tracking exceptions like "An entity with this Id is already tracked". I do generally recommend avoiding explicit detach/attach designs /w EF. It has to be done quite carefully and still can run foul of several issues like this. Commented May 26 at 21:03
  • Hi Steve Py, Thank you for your contribution. I agree with you — I'm not very fond of this management approach either, and I think I’ll remove it. Actually, I’ve raised the transaction level to Serializable, and after running some stress tests, it seems that the issue no longer occurs. Today, I’ll run some additional tests to confirm. Commented May 27 at 7:55

1 Answer 1

0

Written with StackEdit.>How can I fix/avoid exception saving entity in system-versioned table?

The error is because of SQL Server uses the system clock to assign SysStartTime for system-versioned temporal tables. If a transaction's timestamp is earlier than the SysStartTime of the row it's trying to modify, SQL Server throws an error.

Try with the below code which shows how to save an entity in system-versioned table. In the code, the .IsTemporal() maps the Employees table as a temporal table, and the SysStartTime and SysEndTime columns are defined as shadow properties with ValueGeneratedOnAddOrUpdate() and SetAfterSaveBehavior(PropertySaveBehavior.Ignore). By this the temporal columns are automatically managed by SQL Server, not EF Core. When db.SaveChanges() is called, EF Core inserts the record without attempting to modify these system-managed columns, allowing SQL Server to assign correct temporal metadata.

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Position { get; set; }
}

public class CompanyContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=tcp:<serverName>.database.windows.net,1433;Database=<dbName>;User ID=<userId>;Password=<password>;Encrypt=True;");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>(b =>
        {
            b.ToTable("Employees", b => b
                .IsTemporal(t =>
                {
                    t.HasPeriodStart("SysStartTime");
                    t.HasPeriodEnd("SysEndTime");
                    t.UseHistoryTable("EmployeesHistory");
                }));

            b.Property<DateTime>("SysStartTime")
                .HasColumnName("SysStartTime")
                .ValueGeneratedOnAddOrUpdate()
                .Metadata.SetAfterSaveBehavior(PropertySaveBehavior.Ignore);

            b.Property<DateTime>("SysEndTime")
                .HasColumnName("SysEndTime")
                .ValueGeneratedOnAddOrUpdate()
                .Metadata.SetAfterSaveBehavior(PropertySaveBehavior.Ignore);
        });
    }
}

class Program
{
    static void Main()
    {
        using var db = new CompanyContext();

        var newEmployee = new Employee
        {
            Id = 1001,
            Name = "Alice",
            Position = "Engineer"
        };

        db.Employees.Add(newEmployee);
        db.SaveChanges();
        Console.WriteLine("Inserted new employee.");

        var employees = db.Employees
            .Select(e => new
            {
                e.Id,
                e.Name,
                e.Position,
                SysStartTime = EF.Property<DateTime>(e, "SysStartTime"),
                SysEndTime = EF.Property<DateTime>(e, "SysEndTime")
            })
            .ToList();

        foreach (var emp in employees)
        {
            Console.WriteLine($"{emp.Id} | {emp.Name} | {emp.Position} | {emp.SysStartTime} - {emp.SysEndTime}");
        }
    }
}

Output:

Inserted new employee.
1001 | Alice | Engineer | 5/28/2025 10:09:35 AM - 12/31/9999 11:59:59 PM
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.