Transactions and Isolation: ANSI Levels, MVCC, and SERIALIZABLEMixed sources2 sources, drawn from a mix of official docs, project repositories and research.This rates where the sources come from. It doesn't prove every claim.
Four ANSI levels, MVCC vs 2PL, EF Core BeginTransactionAsync, [Timestamp] rowversion optimistic concurrency, FOR UPDATE SKIP LOCKED, write-skew, retry on 40001/40P01 with exponential backoff, transactional outbox over 2PC.
By Amit UpadhyayPublished Updated 14 minArticle 11 of 13 in Databases
AI assistant · answers from this article
1 of 6
One writer at a time
Nothing to add yet
While only one thing changes a row at a time, a single statement is correct by itself.
On this page
Transactions, in one sentence
A transaction is the database’s promise that a group of changes happens all together or not at all, and that work running at the same moment does not trample it.
You reach for it when several requests change the same data at the same moment, such as two customers buying the last item. Not before: it costs some requests wait or fail and must be retried, and stricter isolation reduces throughput.
The cheat sheet
What to use when
You do not need all of this on day one. Each row is one signal and the piece that answers it. Stop at the row your system has reached, or pick one to see it in the picture above.
Where you areThe signalWhat to useWhat it costs
None. Two requests never touch the same row at the same moment.Nothing. Single statements with autocommitNothing
A sale of 50 units ends with 63 orders. Both buyers read “one left”, and both bought it.Make the check and the write one statement, or lock the row firstStep 3 ↓Waiting, when many requests want the same row
The stock went down, the order was never written, and nobody noticed.One transaction around the changes that must succeed togetherStep 1 ↓Locks held for as long as it stays open
Two doctors each see the other on call, and both sign off, leaving nobody.SERIALIZABLE, or explicit locks, for that ruleStep 4 ↓Aborted transactions that you must retry
Requests fail in the night with deadlock and serialization errors.Retry the whole transaction, and lock rows in a consistent orderStep 5 ↓Work that has to be safe to run again
The order commits, the process dies before the event is published, and the warehouse never hears of it.An outbox row written in the same transactionStep 6 ↓A relay to run, and consumers that tolerate repeats
Start With Single Statements
While only one thing changes a row at a time, a single statement with autocommit is correct, and you don't need to think about isolation. That stops being true as soon as two requests can read and change the same row at the same moment, which is the situation below.
Take a typical case, made up for illustration. A flash sale on 50 units ends with 63 orders. Nobody wrote a bug you can point to: the code checked the stock, then decremented it. Two requests just did that at the same moment, and both saw one unit left.
Transactions are the database’s promise that a group of changes happens all together or not at all, and that concurrent work does not trample itself. How strong that second promise is depends on the isolation level, and most teams never look past the default. Below is what each level really guarantees, and the patterns that keep concurrent writes correct.
If concurrency has bitten you
You read a counter, added 1, and wrote it back — and lost updates under load
You don't know the difference between READ COMMITTED and SERIALIZABLE
You've hit a deadlock at 2am and just retried until it worked
You don't know what "write skew" is
Pro deep dive14 min full read
Keep reading with Pro
This is one of our advanced production deep dives. Pro unlocks the rest of it, every other deep dive, and the See, Animate, Test and Build modes on every topic.
What it covers
Four ANSI levels, MVCC vs 2PL, EF Core BeginTransactionAsync, [Timestamp] rowversion optimistic concurrency, FOR UPDATE SKIP LOCKED, write-skew, retry on 40001/40P01 with exponential backoff, transactional outbox over 2PC.