SQL Transactions & Concurrency Control

Transaction কী?

ACID — Atomicity, Consistency, Isolation, Durability। সব succeed অথবা সব rollback।

SQL Transaction

BEGIN TRANSACTION;
BEGIN TRY
    UPDATE Accounts SET Balance = Balance - 1000 WHERE Id = 1;
    UPDATE Accounts SET Balance = Balance + 1000 WHERE Id = 2;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;
END CATCH;

EF Core Transaction

using var transaction = await _ctx.Database.BeginTransactionAsync();
try {
    var from = await _ctx.Accounts.FindAsync(1);
    var to = await _ctx.Accounts.FindAsync(2);
    from.Balance -= 1000;
    to.Balance += 1000;
    await _ctx.SaveChangesAsync();
    await transaction.CommitAsync();
}
catch {
    await transaction.RollbackAsync();
    throw;
}

TransactionScope (Multiple DbContext)

using var scope = new TransactionScope(
    TransactionScopeOption.Required,
    new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted },
    TransactionScopeAsyncFlowOption.Enabled);

await ctx1.SaveChangesAsync();
await ctx2.SaveChangesAsync();
scope.Complete();   // commit
// Dispose হলে complete না করা থাকলে rollback

Isolation Levels ⭐

Level	Dirty Read	Non-repeatable	Phantom
READ UNCOMMITTED	✅	✅	✅
READ COMMITTED (default)	❌	✅	✅
REPEATABLE READ	❌	❌	✅
SERIALIZABLE	❌	❌	❌
SNAPSHOT	❌	❌	❌

Optimistic Concurrency (EF Core) ⭐

// Entity
public class Product {
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }

    [Timestamp]                    // RowVersion column
    public byte[] RowVersion { get; set; }
}

// Save
try {
    _ctx.Products.Update(product);
    await _ctx.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex) {
    foreach (var entry in ex.Entries) {
        var dbValues = await entry.GetDatabaseValuesAsync();
        // Conflict resolve: client wins / db wins / merge
        entry.OriginalValues.SetValues(dbValues);
    }
}

Pessimistic Lock (Raw SQL)

// SELECT ... WITH (UPDLOCK)
var product = await _ctx.Products
    .FromSqlRaw("SELECT * FROM Products WITH (UPDLOCK) WHERE Id = {0}", id)
    .FirstOrDefaultAsync();
// অন্য transaction lock release-এর জন্য wait করবে

Deadlock Handling

// Retry pattern
int retries = 3;
while (retries-- > 0) {
    try {
        // transaction
        break;
    }
    catch (SqlException ex) when (ex.Number == 1205) {  // deadlock
        if (retries == 0) throw;
        await Task.Delay(Random.Shared.Next(50, 200));
    }
}

EF Core Execution Strategy (Auto Retry)

builder.Services.AddDbContext<AppDbContext>(opt =>
    opt.UseSqlServer(conn, sql => sql.EnableRetryOnFailure(
        maxRetryCount: 3,
        maxRetryDelay: TimeSpan.FromSeconds(5),
        errorNumbersToAdd: null)));