Transactions — Explicit Transactions and SaveChanges Behaviour

A transaction is a unit of work that either completes entirely or is rolled back entirely — atomicity. EF Core’s SaveChangesAsync() wraps all pending changes in an implicit transaction automatically. Explicit transactions are needed when you need to coordinate multiple SaveChangesAsync() calls, mix EF Core operations with raw SQL, or implement patterns like the outbox (write data and events atomically). SQL Server’s isolation levels control concurrency — understanding them prevents deadlocks and dirty reads.

Explicit Transactions #

// ── Implicit transaction (default) — all changes in one transaction ────────
// SaveChangesAsync() wraps all tracked changes in a single transaction
await db.SaveChangesAsync();   // INSERT post + INSERT tags = one transaction

// ── Explicit transaction — coordinate multiple SaveChangesAsync calls ──────
await using var transaction = await db.Database.BeginTransactionAsync(ct);
try
{
    // Operation 1: Create the post
    var post = Post.Create(request.Title, request.Slug, request.Body, authorId);
    db.Posts.Add(post);
    await db.SaveChangesAsync(ct);   // saves post, gets Id

    // Operation 2: Award points to author (uses same DbContext/transaction)
    await pointsService.AwardPostCreationPointsAsync(authorId, post.Id, ct);
    await db.SaveChangesAsync(ct);

    // Both operations committed atomically
    await transaction.CommitAsync(ct);
    return post;
}
catch
{
    await transaction.RollbackAsync(ct);
    throw;
}

// ── Outbox pattern — write event alongside data in same transaction ────────
await using var tx = await db.Database.BeginTransactionAsync(ct);

var post = Post.Create(request.Title, request.Slug, request.Body, authorId);
db.Posts.Add(post);

// Write the outbox event atomically with the post
var outboxEvent = new OutboxEvent
{
    EventType   = "PostCreated",
    Payload     = JsonSerializer.Serialize(new { post.Id, post.Title }),
    CreatedAt   = DateTime.UtcNow,
    ProcessedAt = null,   // background worker picks this up and publishes
};
db.OutboxEvents.Add(outboxEvent);

await db.SaveChangesAsync(ct);   // post and event saved together
await tx.CommitAsync(ct);

Isolation Levels #

// ── SQL Server isolation levels ────────────────────────────────────────────
// ReadCommitted (default) — sees only committed data; no dirty reads
// ReadUncommitted — fastest, sees uncommitted data (dirty reads possible)
// RepeatableRead — same read returns same data within transaction (no phantom rows)
// Serializable — highest isolation; prevents phantom reads; most locking
// Snapshot — reads consistent snapshot without locking other writers (MVCC)

// Configure isolation level for a specific transaction
await using var tx = await db.Database.BeginTransactionAsync(
    System.Data.IsolationLevel.Snapshot, ct);

// For reporting queries that need a consistent snapshot without blocking writers:
await db.Database.ExecuteSqlRawAsync(
    "SET TRANSACTION ISOLATION LEVEL READ COMMITTED SNAPSHOT", ct);

// Enable RCSI at database level (recommended for web applications):
// ALTER DATABASE BlogApp SET READ_COMMITTED_SNAPSHOT ON;

Common Mistakes #

Mistake 1 — Opening explicit transactions for simple single-entity CRUD (unnecessary overhead) #

❌ Wrong — beginTransaction + SaveChangesAsync + commitTransaction for a single entity insert.

✅ Correct — SaveChangesAsync() already wraps all changes in a transaction; explicit transaction adds no value for single SaveChangesAsync calls.

Mistake 2 — Long-running transaction holding locks during HTTP calls or file I/O #

❌ Wrong — transaction opened, HTTP call made inside, SaveChangesAsync called; lock held for seconds.

✅ Correct — complete all non-database work before opening the transaction; keep transactions covering only database operations.