Concurrency Tokens and Optimistic Locking

When building APIs for thousands of users, it is guaranteed that two users will attempt to edit the exact same database row at the exact same millisecond. If User A loads an invoice data into memory, and User B loads the same invoice... User A saves their edits, then User B saves their edits. User B blindly overwrites User A's changes without ever knowing it. This is the "Last-in-Wins" concurrency flaw. We fix this using Optimistic Locking.

1. Implementing the Concurrency Token (RowVersion)

We add a special tracking column to our SQL Server table. Every time ANY update happens to a row, SQL Server natively generates a brand new byte array signature for that row.

public class Invoice
{
    public int Id { get; set; }
    public decimal TotalAmount { get; set; }
    
    // The Concurrency Token. In SQL Server, this becomes a 'rowversion' column
    [Timestamp]
    public byte[] Version { get; set; }
}

2. How Optimistic Locking Works

When User A and User B both download the Invoice, they both download Version = 0xAA.

• User A saves their update. EF Core executes: UPDATE Invoices ... WHERE Id = 1 AND Version = 0xAA. SQL Server confirms 1 row updated. Because the row changed, SQL Server internally regenerates the token to 0xBB.
• Millisecond later, User B saves their update. EF Core executes: UPDATE Invoices ... WHERE Id = 1 AND Version = 0xAA.

3. Handling the Exception (The Retry Pattern)

When the collision happens, we catch the exception and typically ask the user what to do: overwrite the new data, or discard their changes.

try 
{
    await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex) 
{
    // A collision occurred! User B was rejected.
    var entry = ex.Entries.Single();
    var databaseValues = await entry.GetDatabaseValuesAsync(); // Get User A's current data

    if (databaseValues == null)
        return NotFound("The invoice was deleted by another user.");
        
    return Conflict("The invoice was updated by someone else while you were editing it. Please refresh and try again.");
}

4. Interview Mastery

Q: "We don't want to use SQL Server's `[Timestamp]` byte array because we are migrating to PostgreSQL, which doesn't support `rowversion`. Can we use a normal property, like `int UpdateCount`, as a Concurrency Token instead?"

Architect Answer: "Yes, EF Core supports Custom Concurrency Tokens natively across all database providers. In your Fluent API, you take your standard `UpdateCount` or `LastModifiedDate` property and configure it with `.IsConcurrencyToken()`. EF Core will automatically append that column to the `WHERE` clause of every `UPDATE` statement. The critical catch is that you, the application developer, are now 100% responsible for manually incrementing that `UpdateCount` inside C# before calling `SaveChanges()`, otherwise the token remains stagnant and collisions will still blindly overwrite data."