Transaction boundaries and fresh verification

Choose a commit boundary for a concrete multi-save requirement

A transaction defines which database work is committed together, subject to the provider’s actual capabilities and isolation behavior. EF normally wraps one SaveChanges in a transaction when supported, so an extra explicit transaction should group work that genuinely spans several operations. This lesson saves two separate additions before deliberately rolling back the group, then verifies a different committed group. The fixture keeps every context and transaction sequential on the held-open SQLite connection. It is a controlled relational atomicity exercise, not a distributed transaction or a claim that all concurrent readers in every provider see the same intermediate state.

Verify rollback and commit through fresh contexts

The first external context creates the transient schema, starts a transaction, saves A, saves B and rolls back. The context and transaction scopes then end. A new context counts zero rows. A second transaction saves C and commits; another fresh context reads the single C row. These observations avoid using the first context’s already tracked objects to decide whether rollback changed storage. The code’s Check assertions establish both outcomes on actual SQLite. Merely watching SaveChanges return successfully inside the uncommitted transaction would not establish that its work later survived the group’s rollback.

using System;
using System.Linq;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

await using var connection = new SqliteConnection("Data Source=:memory:;Foreign Keys=True");
await connection.OpenAsync();
var options = new DbContextOptionsBuilder<LabDb>().UseSqlite(connection).Options;
await using (var db = new LabDb(options))
{
    await db.Database.EnsureCreatedAsync(); await using var transaction = await db.Database.BeginTransactionAsync();
    db.Items.Add(new Item { Name = "A" }); await db.SaveChangesAsync(); db.Items.Add(new Item { Name = "B" }); await db.SaveChangesAsync();
    await transaction.RollbackAsync();
}
await using (var verify = new LabDb(options)) { Check(await verify.Items.CountAsync() == 0, "rollback stored count"); Console.WriteLine("after-rollback:0"); }
await using (var db = new LabDb(options)) { await using var transaction = await db.Database.BeginTransactionAsync(); db.Items.Add(new Item { Name = "C" }); await db.SaveChangesAsync(); await transaction.CommitAsync(); }
await using (var verify = new LabDb(options)) { Check((await verify.Items.SingleAsync()).Name == "C", "commit visible"); Console.WriteLine("after-commit:C"); }

static void Check(bool condition, string name) { if (!condition) throw new InvalidOperationException("CHECK: " + name); }

Staging is an application model, not an ACID implementation

The .NET9 companion begins with an empty committed list and a staged A,B list. Clearing the staged list leaves the committed count zero. A later stage containing C is explicitly copied to committed, producing C. Those operations illustrate a proposed boundary between tentative and accepted application data. They do not implement SQLite rollback, locking, durability, savepoints or isolation. The class-free list example remains useful precisely because its claim is small. Use it to reason about what an application wants to commit, then let genuine relational tests establish whether the actual database unit obeys that contract.

Rollback does not rewind arbitrary managed objects

After a database rollback, a context can still retain objects whose state was accepted during an earlier SaveChanges. Their values and tracking status are not an automatic reconstruction of the pre-transaction database. Ending that failed unit and reading through a fresh context avoids a common false verification. Likewise, a successful transaction commit is not the same as an application-level distributed operation completing: another service, an email or a file write may lie outside the database boundary. Do not silently include those effects in a rollback claim. Production retry strategies also need coordination with manually managed transactions.

Practice: make a later operation fail inside the group

Add a real unique index in a separate fixture and save one valid row before attempting a conflicting row in the same explicit transaction. Decide where the failure is translated, dispose or roll back the transaction, and verify the stored rows using a new context. Compare that grouped result with two deliberately independent transactions. Keep the expected outcome specified before writing the handling code. This experiment requires the actual provider constraint; throwing a homemade exception before any SQL executes would test control flow but would not establish database atomicity under a real save failure.

• Hint: use a fresh context for storage observations after a failed unit.
• Hint: verify which earlier saves belong to the same transaction.

Practice: define partial acceptance rather than obtaining it accidentally

Extend the portable staged-data example to validate every supplied entry before accepting the complete batch. Add one rejected entry after several valid entries and require that committed data remains unchanged. Then describe a different partial-import contract that deliberately accepts valid entries and reports rejected ones. Both can be legitimate application designs, but they need different results and tests. Do not infer partial success merely from a local list that was updated before an exception escaped. The model should express an independently chosen transaction requirement without borrowing database terms for guarantees it does not implement.

• Hint: all-or-nothing and partial import are different public contracts.
• Hint: a local variable changing before failure is not proof that storage committed it.

Review actual commit evidence and its limits

The external transcript establishes after-rollback zero and after-commit C through fresh SQLite queries. The portable transcript establishes only the stated staging-list behavior. Neither validates a production migration, another provider’s isolation level or a transaction spanning external side effects. A useful review identifies which saves share a transaction, who owns disposal, how failures end the unit and how committed state is independently observed. Keep those facts visible when the capstone combines stock updates with a unique reservation reference, where a plausible success message must never substitute for the whole relational unit actually committing.