Saving, Transactions, and Concurrency

Reading data safely is half the job; writing it safely is the half that gets audited. EF gives you strong defaults here — if you understand what SaveChanges actually does.

Change tracking and SaveChanges

Every entity loaded through a tracked query is snapshotted by the change tracker. You mutate properties, add entities (_db.Permits.Add(p)), or mark deletions — and nothing touches the database until SaveChanges(). At that point EF diffs the tracked graph, generates the INSERT/UPDATE/DELETE statements, and runs them inside a single transaction. That built-in atomicity is the key fact: one SaveChanges call either fully succeeds or fully rolls back. The practical pattern follows directly — accumulate all the changes for one business operation, then call SaveChanges once, rather than saving after every tweak.

When you need an explicit transaction

Sometimes one operation spans multiple SaveChanges calls (you need a generated ID midway) or mixes EF with raw SQL. Wrap it explicitly:

using (var tx = _db.Database.BeginTransaction())
{
    _db.SaveChanges();          // first batch
    _db.SaveChanges();          // uses IDs from the first
    tx.Commit();
}

Keep transactions short — do your validation, lookups, and mapping before opening one; hold locks only while writing. A transaction that wraps a remote HTTP call is how systems deadlock.

Optimistic concurrency with rowversion

Two users load the same record; both edit; both save. Without protection the second save silently overwrites the first — a real problem when the record is someone's benefits case. The standard fix is optimistic concurrency: add a SQL Server rowversion column, map it as a concurrency token ([Timestamp] on a byte[] property), and carry the value through the edit screen in a hidden field. On save, EF adds WHERE RowVersion = @original to the UPDATE; if another writer got there first, zero rows match and EF throws DbUpdateConcurrencyException. Catch it, tell the user the record changed underneath them, and reload — never blind-retry, because that reintroduces exactly the lost update the token exists to prevent.

Retry thinking

Concurrency conflicts need a human decision, but transient faults — deadlock victim (SQL error 1205), timeouts, brief network blips — deserve an automatic retry. Turn on EnableRetryOnFailure() in your UseSqlServer options — an execution strategy that retries the whole operation on known-transient errors. Two rules keep retries safe: only retry operations that are idempotent or wrapped in a transaction that fully rolled back, and cap the attempts with backoff. Distinguish the two failure families in code review: conflict → surface to user; transient → bounded retry. Mixing them up produces either angry users or duplicate rows.