"serializable" transactions vs Guarded atomic UPDATE, concurrency racing conditions

Summary

The problem at hand is ensuring exactly-once processing for a specific row in a database table, without encountering race conditions. This is crucial in scenarios where multiple users or devices attempt to redeem a coupon or update a record simultaneously. The author has been using serializable transactions with Entity Framework Core to achieve this, but has been suggested to use Guarded atomic UPDATE instead.

Root Cause

The root cause of the issue is the concurrency racing condition, where multiple transactions attempt to update the same row at the same time. This can lead to data inconsistency and incorrect results. The two approaches mentioned, serializable transactions and Guarded atomic UPDATE, aim to prevent this issue.

Why This Happens in Real Systems

This issue occurs in real systems due to the following reasons:

Multiple concurrent requests: When multiple users or devices send requests to update the same row simultaneously.
Lack of synchronization: When the database or application does not properly synchronize access to the row, allowing multiple transactions to proceed concurrently.
Inadequate locking mechanisms: When the locking mechanisms in place are not sufficient to prevent concurrent updates.

Real-World Impact

The impact of this issue can be significant, including:

Data inconsistency: Multiple users redeeming the same coupon or updating the same record, leading to incorrect results.
System crashes: Deadlocks and other concurrency-related issues can cause the system to crash or become unresponsive.
User frustration: Users may experience errors or inconsistencies, leading to frustration and a negative experience.

Example or Code

public async Task CommitIsolatedTransactionForEntityFuncAsync(Func methodToExecute)
{
    var strategy = _context.Database.CreateExecutionStrategy();
    await strategy.ExecuteAsync(async () =>
    {
        await using var dbContextTransaction = await _context.Database.BeginTransactionAsync(System.Data.IsolationLevel.Serializable);
        try
        {
            await methodToExecute();
            await _context.SaveChangesAsync();
            await dbContextTransaction.CommitAsync();
        }
        catch
        {
            await dbContextTransaction.RollbackAsync();
            throw;
        }
    });
}

public async Task TryRedeemSaleAsync(Guid saleId, Guid machineId)
{
    var rows = await _context.Database.ExecuteSqlInterpolatedAsync($@"
        UPDATE Sales
        SET Redeemed = {DateTime.UtcNow}, SaleStatus = {(int)SaleStatus.Redeemed}, MachineId = {machineId}
        WHERE SaleId = {saleId} AND SaleStatus  {(int)SaleStatus.Redeemed}
    ");
    return rows == 1;
}

How Senior Engineers Fix It

Senior engineers fix this issue by:

Using Guarded atomic UPDATE: This approach ensures that only one transaction can update the row at a time, preventing concurrency racing conditions.
Implementing row versioning: By including a row version column in the table, the application can detect and prevent concurrent updates.
Handling exceptions properly: By catching and handling exceptions, the application can ensure that the system remains consistent and reliable.

Why Juniors Miss It

Juniors may miss this issue due to:

Lack of experience: Inexperienced developers may not be aware of the potential for concurrency racing conditions.
Insufficient knowledge: Juniors may not fully understand the implications of concurrent updates and the need for synchronization.
Overreliance on frameworks: Relying too heavily on frameworks and libraries can lead to a lack of understanding of the underlying database and concurrency issues.

“serializable” transactions vs Guarded atomic UPDATE, concurrency racing conditions