Transactions, Isolation Levels and Concurrency Control Interview Questions

Q1 Explain ACID — and why it matters more as a design discussion than as a definition.#

Short answer: Atomicity, Consistency, Isolation and Durability describe the guarantees a transactional database gives you so that concurrent, failure-prone operations still leave the data in a state your application logic can reason about — but reciting the acronym is table stakes; the interesting discussion is which of these your architecture actually depends on, and where you've deliberately traded one away.

Atomicity means a transaction's operations succeed or fail as a unit — no partial writes. Consistency is the one most candidates get vague on: it doesn't mean "the data is correct," it means the database moves from one state that satisfies your declared invariants (constraints, foreign keys, triggers) to another state that also satisfies them; application-level invariants that aren't encoded as database constraints aren't protected by this property, which is a common source of bugs when people assume "consistency" covers business rules it never enforced. Isolation determines how much of one transaction's in-flight work another concurrent transaction can observe, and is the one property with a genuine dial — the isolation level — rather than being on or off. Durability means a committed transaction survives a crash, typically via write-ahead logging.

The design conversation worth having: distributed and NoSQL systems routinely relax one or more of these — eventual consistency relaxes Isolation and sometimes Consistency across nodes in exchange for availability and partition tolerance — and a senior engineer should be able to say, for a given system, exactly which property was traded and why that trade was acceptable for that specific workload, rather than treating ACID as an all-or-nothing badge.

What interviewers look for: going beyond the acronym to explain Consistency correctly (a frequent stumble) and connecting ACID trade-offs to real distributed-systems decisions, not just relational database trivia.

• Follow-up questions: Where does a message queue with at-least-once delivery fit into this model? How would you retrofit atomicity across two systems that don't share a transaction?

Q2 Walk through the standard isolation levels and the anomalies each one prevents.#

Short answer: The four ANSI SQL isolation levels — Read Uncommitted, Read Committed, Repeatable Read and Serializable — form a strictly increasing ladder of consistency guarantees, each one closing off one more class of anomaly at the cost of more blocking or more aborted transactions.

A dirty read is seeing another transaction's uncommitted change, which might be rolled back a moment later. A non-repeatable read is re-reading the same row twice in one transaction and getting different values because another transaction committed a change in between. A phantom read is re-running the same range query twice and getting a different set of rows, because another transaction inserted or deleted a row that matches your predicate in between — this is subtly different from a non-repeatable read because no individual row you already read actually changed.

In practice, almost nobody runs Read Uncommitted deliberately anymore except for specific reporting queries that can tolerate dirty data in exchange for zero blocking, and Serializable is reserved for the rare cases where correctness genuinely requires it, because it's implemented with the most restrictive locking (or the most aggressive conflict detection) of the four. SQL Server's default is Read Committed, but — critically for the next question — what "Read Committed" actually does depends on a database-level setting most developers never touch.

What interviewers look for: the exact anomaly-to-level mapping without hesitation, and the ability to explain the difference between a non-repeatable read and a phantom read precisely, which is where most candidates get fuzzy.

• Common mistakes: confusing non-repeatable reads with phantom reads, or claiming Serializable prevents anomalies "by locking everything" without mentioning that some engines use optimistic conflict detection instead.

Q3 What's the real difference between Read Committed Snapshot Isolation (RCSI) and the Snapshot isolation level?#

Short answer: Both use row versioning instead of locks for readers, but RCSI is a database-level setting that changes what the existing Read Committed isolation level means — statement-level consistency, no code changes required — while Snapshot is a distinct isolation level a transaction opts into explicitly for transaction-level consistency, and the two fail very differently under a write conflict.

With READ_COMMITTED_SNAPSHOT set ON (the default for Azure SQL Database, off by default for on-premises SQL Server), a READ COMMITTED reader sees a transactionally consistent snapshot as it existed at the start of the statement, takes only schema-stability locks, and never blocks on or is blocked by writers. Because it's re-evaluated per statement rather than per transaction, two statements in the same transaction can each see a different, more-recent snapshot — that's the "statement-level" part. Snapshot isolation, in contrast, requires ALLOW_SNAPSHOT_ISOLATION at the database and SET TRANSACTION ISOLATION LEVEL SNAPSHOT per session; it gives every statement in the transaction the same snapshot from the transaction's start, and if the transaction then tries to update a row modified and committed by someone else since that snapshot was taken, SQL Server raises error 3960 and aborts it — a real conflict the application must catch and retry, not just a blocking wait.

There's a further nuance worth having ready at the architect level: SQL Server's newer optimized locking feature changes what happens on that conflict path specifically for RCSI under plain Read Committed — conflicts there are detected and retried automatically by the engine with no impact to the application, whereas under true Snapshot isolation, update conflicts must still be handled and retried by the application; the engine never silently resolves them for you.

ALTER DATABASE Sales SET READ_COMMITTED_SNAPSHOT ON; -- no application code changes needed

ALTER DATABASE Sales SET ALLOW_SNAPSHOT_ISOLATION ON;
-- and in the session:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

What interviewers look for: knowing these are two different mechanisms with two different failure behaviors, not "SQL Server's version of MVCC" as a single undifferentiated concept — and specifically that Snapshot isolation can abort a transaction outright while RCSI generally doesn't.

• Common mistakes: assuming RCSI eliminates blocking entirely (writers still block writers under RCSI; only reader/writer blocking goes away); confusing error 3960 (snapshot update conflict) with a deadlock.

Q4 How does EF Core implement optimistic concurrency with a rowversion column, and what happens when a conflict occurs?#

Short answer: EF Core configures a designated property — typically a SQL Server rowversion column mapped via [Timestamp] or .IsRowVersion() — as a concurrency token that's included in the WHERE clause of every generated UPDATE/DELETE; if zero rows are affected because the token no longer matches, EF Core throws DbUpdateConcurrencyException rather than silently succeeding or silently overwriting someone else's change.

public class Invoice
{
    public int Id { get; set; }
    public decimal Balance { get; set; }

    [Timestamp]
    public byte[] Version { get; set; } = default!;
}

Because rowversion changes automatically on every write to the row, EF Core sends UPDATE Invoices SET Balance = @p0 WHERE Id = @p1 AND Version = @p2 — if another transaction already changed that row, Version no longer matches, zero rows are affected, and the exception fires. Resolving the conflict means catching DbUpdateConcurrencyException, pulling DbUpdateConcurrencyException.Entries to see the affected entities, and choosing between the three sets of values EF Core exposes: current values (what your code was trying to write), original values (what was loaded before the edit) and database values (what's actually there now) — then either overwriting, merging, or discarding your change and refreshing the original values before retrying SaveChanges.

On databases without a native auto-updating type (SQLite, for instance), [ConcurrencyCheck]/.IsConcurrencyToken() lets you manage an application-assigned token — a GUID you regenerate yourself — trading the "automatic" part for portability. Either way, this is optimistic concurrency: no locks are taken up front, and the cost of a conflict is paid only when one actually happens, which is why it scales far better than pessimistic locking for low-contention data.

What interviewers look for: the exact mechanism (token in the WHERE clause, zero-rows-affected detection), not just "EF Core throws an exception on conflict" — and awareness of the three value sets used to resolve a conflict.

• Follow-up questions: How would you build a "last write wins with a warning" UX on top of this versus a hard merge conflict? Why does adding a rowversion column never fully replace testing under Repeatable Read for a genuinely serialized business process?

Q5 When would you reach for pessimistic locking instead, and how do you actually do it since EF Core has no built-in API for it?#

Short answer: Pessimistic locking makes sense when contention on specific rows is high enough that optimistic retries would themselves become the bottleneck — a hot inventory counter, a seat-booking row during a flash sale — and since EF Core has no first-class pessimistic-locking API, you implement it with raw SQL locking hints (UPDLOCK, HOLDLOCK, ROWLOCK on SQL Server) executed through FromSqlRaw/ExecuteSqlRaw inside an explicit transaction.

await using var tx = await context.Database.BeginTransactionAsync();

var seat = await context.Seats
    .FromSqlRaw("SELECT * FROM Seats WITH (UPDLOCK, ROWLOCK) WHERE Id = {0}", seatId)
    .SingleAsync();

if (seat.Status == SeatStatus.Available)
{
    seat.Status = SeatStatus.Reserved;
    await context.SaveChangesAsync();
}

await tx.CommitAsync();

UPDLOCK takes an update lock at read time so a second concurrent transaction reading the same row for update blocks immediately instead of both transactions racing to an optimistic conflict later; HOLDLOCK holds that lock for the duration of the transaction (roughly equivalent to Serializable for that specific range); ROWLOCK requests row-level rather than page-level granularity to minimize how much you block unrelated rows. The trade-off versus optimistic concurrency is direct: you pay a guaranteed lock-acquisition cost on every access instead of an occasional conflict-and-retry cost, which is the right trade only when conflicts under the optimistic approach would be frequent, not rare.

It's worth knowing that SQL Server's newer optimized locking mechanism narrows this gap somewhat: row and page locks acquired for an update are released as soon as that specific row is updated rather than held until the whole transaction commits, which reduces blocking even for update-heavy transactions without changing your isolation-level choice.

What interviewers look for: recognizing that "EF Core doesn't have this out of the box" is itself the correct starting answer, followed by fluency with the actual SQL Server locking hints and when each one is appropriate.

• Common mistakes: reaching for pessimistic locking as a default instead of a targeted fix for measured contention; holding a pessimistic lock across a network call or user-facing wait, which turns brief contention into a systemic bottleneck.

Q6 What's the difference between TransactionScope and DbContext.Database.BeginTransaction, and when do you actually need System.Transactions?#

Short answer: BeginTransactionAsync on DbContext.Database gives you an explicit, EF-Core-scoped transaction object you pass around or commit directly; TransactionScope from System.Transactions is an ambient transaction that any transaction-aware code inside its using block automatically enlists in, without being passed a reference — which is exactly what you need when you're coordinating work across more than one DbContext, or across EF Core and raw ADO.NET, without wiring the participants together manually.

using var scope = new TransactionScope(
    TransactionScopeAsyncFlowOption.Enabled); // required for async work to flow the ambient transaction

await using (var ordersContext = new OrdersContext())
await using (var billingContext = new BillingContext())
{
    ordersContext.Orders.Add(order);
    await ordersContext.SaveChangesAsync();

    billingContext.Invoices.Add(invoice);
    await billingContext.SaveChangesAsync();
}

scope.Complete();

The explicit BeginTransaction path is simpler and sufficient for the common case: multiple SaveChanges calls and queries against a single DbContext that need to commit or roll back together — and it's what EF Core already does implicitly around every individual SaveChanges call by default. Reach for TransactionScope specifically when the unit of work spans multiple contexts or multiple data-access technologies, and always pass TransactionScopeAsyncFlowOption.Enabled, since without it the ambient transaction doesn't reliably flow across await boundaries. A real limitation to know cold: TransactionScope doesn't support asynchronous commit or rollback, so disposing it synchronously blocks the calling thread until the operation finishes, even in an otherwise fully async code path.

What interviewers look for: the ambient-versus-explicit distinction stated precisely, plus knowing TransactionScopeAsyncFlowOption.Enabled by name — a very common trip-up for engineers who haven't hit this personally.

• Follow-up questions: What happens if two DbContext instances inside the same TransactionScope target different database servers? How would you test code that depends on an ambient transaction?

Q7 What's the current state of distributed transactions in modern .NET, and what should an architect do instead?#

Short answer: True distributed (two-phase-commit) transaction support in System.Transactions — the kind that escalates a TransactionScope to the OS-level distributed transaction coordinator when it spans more than one physical connection — was only reintroduced in .NET 7.0, and only on Windows; attempting to escalate to a distributed transaction on an earlier .NET version, or on Linux or macOS regardless of version, fails outright. That single fact should end most conversations about using MSDTC-style two-phase commit in a cloud-native, cross-platform .NET system.

The practical implication: if your architecture is containerized, runs on Linux, or needs to coordinate a database write with a message publish or a call to another service, you cannot lean on ambient distributed transactions the way an on-premises Windows monolith once did. The two patterns architects reach for instead both trade strict atomicity for eventual consistency with explicit compensation: the outbox pattern, where you write your business change and an "event to publish" row in the same local database transaction, then a separate process reliably publishes that event and marks it sent — guaranteeing the event is never lost even if the publish step crashes — and the saga pattern, where a multi-step, cross-service process is modeled as a sequence of local transactions, each with a defined compensating action if a later step fails.

For a single database server, none of this matters — a plain local transaction is atomic already. This question really tests whether you reach for distributed transactions reflexively (a red flag at the architect level) or recognize that .NET's platform constraints and cloud-native deployment realities push toward saga/outbox designs by default.

What interviewers look for: citing the .NET 7.0/Windows-only constraint specifically, and pivoting confidently to outbox/saga as the modern answer rather than trying to work around MSDTC.

• Follow-up questions: How would you implement the outbox pattern with EF Core so the business write and the outbox row are guaranteed atomic? See Saga Pattern and Distributed Transactions Interview Questions for the deeper distributed-systems version of this question.

Q8 How do deadlocks happen in SQL Server, and how should application code respond to error 1205?#

Short answer: A deadlock happens when two transactions each hold a lock the other needs and neither can proceed — classically, transaction A updates row 1 then waits for row 2, while transaction B has already locked row 2 and is waiting for row 1. SQL Server's lock monitor detects the cycle (checking roughly every five seconds by default, but polling as frequently as every 100 milliseconds once frequent deadlocking is detected), kills one transaction as the "victim" — raising error 1205 on that connection — and lets the other proceed.

Victim selection isn't arbitrary: SQL Server first honors DEADLOCK_PRIORITY if you've set it explicitly on either session, and otherwise picks whichever transaction is cheapest to roll back. Application code has to treat 1205 as an expected, retryable condition, not a fatal error — catch it, wait a short randomized backoff, and retry the whole logical operation from the top, because the victim's work was fully rolled back. The two structural fixes that actually reduce deadlock frequency, as opposed to just handling them gracefully, are enforcing a consistent order of access to rows/tables across every code path that touches them, and keeping transactions as short as possible so the window for a conflicting lock pattern to occur is smaller. Moving read-heavy paths onto RCSI also helps indirectly, since it removes reader/writer blocking from the picture entirely, leaving only writer/writer contention as a deadlock source.

For diagnosis rather than just recovery, SQL Server's system_health extended events session captures an xml_deadlock_report for every deadlock, showing exactly which statements, resources and lock modes were involved — that report, not guesswork, is what you bring to a code review after a production deadlock spike.

What interviewers look for: knowing 1205 is retryable by design, understanding victim selection isn't random, and naming a structural fix (consistent access ordering) rather than only "catch and retry."

• Common mistakes: treating every 1205 as a bug to eliminate entirely rather than an expected condition to handle; retrying without any backoff, which can reproduce the exact same deadlock immediately.

Q9 How do EF Core's execution strategies interact with a manually-controlled transaction, and why can't you just wrap BeginTransactionAsync in your own retry loop?#

Short answer: When you enable a retrying execution strategy (EnableRetryOnFailure()), EF Core treats each query and each SaveChanges call as its own independently-retriable unit — but a transaction you start yourself with BeginTransactionAsync defines a group of operations that must be replayed together on failure, which the automatic per-call retry logic can't do safely, so EF Core throws an InvalidOperationException if you try to combine a user-initiated transaction with the default retrying strategy. The fix is to retrieve the execution strategy explicitly and hand it a delegate representing the entire unit of work, so the whole block gets replayed together if a transient failure occurs.

var strategy = context.Database.CreateExecutionStrategy();

await strategy.ExecuteAsync(async () =>
{
    await using var tx = await context.Database.BeginTransactionAsync();

    context.Orders.Add(order);
    await context.SaveChangesAsync();

    await tx.CommitAsync();
});

There's a subtler failure mode worth raising unprompted: if the connection drops while the commit itself is in flight, the transaction's actual outcome is unknown — it may have committed on the server even though the client never saw the acknowledgment. A naive retry in that scenario can re-insert a row with a store-generated key and cause a duplicate, or otherwise assume a rollback that didn't happen. The documented mitigations range from accepting the (small) risk and using client-generated keys so a duplicate insert fails loudly instead of silently, to verifying success by re-reading application state after a suspected commit failure, to explicitly tracking transaction IDs in a dedicated table you can check after a reconnect — the right choice depends on how expensive a false retry would be for that specific operation.

What interviewers look for: knowing this isn't paranoia — it's a documented, real failure mode of retrying execution strategies — and being able to describe at least one concrete mitigation beyond "just retry."

• Follow-up questions: Why does enabling retry-on-failure also change how EF Core buffers query results? How would you make an INSERT idempotent to make this whole class of problem simpler to reason about?

Q10 Design question — you need to transfer funds between two accounts in the same database under heavy concurrent load. What isolation level, locking approach and retry policy would you choose?#

Short answer: For same-database transfers, I'd use an explicit transaction under Read Committed with RCSI enabled, update both account rows in a consistent, deterministic order (for example, always by ascending account ID regardless of which account is the sender), and wrap the whole operation in an EF Core execution strategy with a bounded retry count — deliberately avoiding both full Serializable isolation and hand-rolled pessimistic locking unless measurement shows plain RCSI-backed transactions aren't holding up under the actual contention profile.

The reasoning: a transfer is fundamentally two row updates (debit one balance, credit another) that must be atomic together, which a plain database transaction already guarantees without needing Serializable — the risk isn't reading stale data, it's two concurrent transfers touching the same accounts in conflicting order, which consistent access ordering solves directly and cheaply, without the throughput cost of upgrading the isolation level for every transfer. I'd add a rowversion column on the account balance as a defense-in-depth optimistic check even inside the explicit transaction, so any code path that reads a balance outside this transfer flow and writes it back still gets caught rather than silently overwriting a concurrent transfer's result. For idempotency under retry — essential once you accept that 1205s and transient faults will happen — each transfer request would carry a client-supplied idempotency key stored in the same transaction, so a retried request can detect "this transfer already happened" and return the prior result instead of moving money twice.

I'd only escalate to Serializable, or to explicit UPDLOCK/HOLDLOCK pessimistic locking on the account rows, if production telemetry showed the deadlock-and-retry rate under this design was actually a problem — not as a starting assumption, since both cost more throughput than most transfer volumes need.

What interviewers look for: a synthesis of everything above into one coherent, justified design — consistent lock ordering to avoid deadlocks, RCSI over Serializable by default, rowversion as a safety net, and idempotency keys as the piece most candidates forget entirely.

• Common mistakes: reaching for Serializable or full pessimistic locking by default "to be safe," without justifying the throughput cost; forgetting idempotency, which turns a well-intentioned retry policy into a double-spend bug.