Transactions and Concurrency
The setup is always a story: "Two case workers open the same candidate record. Both edit it. Both hit Save. What happens in your app today — and what should happen?" This scenario is a gift, because the honest first answer is unglamorous: last write wins, silently. Saying that plainly earns trust; pretending your CRUD app already handles it does not.
The two-users answer, structured
Clarify first: "Does the business actually care? For a notes field, last-write-wins may be
fine. For a certification decision on a transcript, it isn't." Then present the standard fix:
optimistic concurrency. Add a rowversion column, map it as a concurrency token
([Timestamp] or IsRowVersion() in Fluent API), and EF appends WHERE RowVersion = @original
to every UPDATE. If zero rows match, EF throws DbUpdateConcurrencyException — the record
changed under you.
The part most candidates skip, and interviewers notice: what the user sees next. Catching the exception is not a strategy. Say what you'd do with it — reload the current values, show "this record was changed by someone else," and let the user merge or retry. In an MVC app that means round-tripping the row version in a hidden field on the edit form. Contrast briefly with pessimistic locking (holding a lock or a "checked out by" flag) and explain why optimistic wins for web apps: no abandoned locks when someone closes a browser tab.
Transactions: when SaveChanges isn't enough
Know the baseline cold: one SaveChanges() call is already atomic — EF wraps it in a
transaction. You need an explicit transaction (Database.BeginTransactionAsync) when a unit
of work spans multiple SaveChanges calls or mixes EF with raw SQL/Dapper. The advanced
probe: "You've enabled connection retries — why did your transaction code start throwing?"
Answer: EnableRetryOnFailure requires retriable units, so you wrap the whole transaction in
CreateExecutionStrategy().ExecuteAsync(...) — the strategy can replay the entire block.
That leads into idempotent retries: if a retry replays your block, it must be safe to run twice. Check-then-insert patterns need unique constraints as the backstop; money-moving or status-advancing operations need an idempotency key or a state check inside the transaction.
Answers that fall flat
- "SQL Server handles concurrency for us" — confusing isolation levels with lost-update protection. Two reads followed by two writes lose an update at any default isolation level.
- Catching
DbUpdateConcurrencyExceptionand silently retrying the same overwrite — that's last-write-wins with extra steps. - Wrapping every single
SaveChangesin a manual transaction "to be safe."
Practice prompts
- Deliver the two-users answer end to end in three minutes: business question, rowversion mechanics, UX on conflict.
- Whiteboard a transfer between two tables that needs an explicit transaction plus the execution strategy wrapper.
- Explain how you'd make "approve transcript" idempotent when the user double-clicks Save.