Concurrency control
Isolation is the PROMISE that transactions don't trample each other. Concurrency control is HOW the database actually keeps that promise when two transactions reach for the same row at the same moment. There are two big strategies, and they're opposite in spirit: LOCKING (pessimistic — assume there'll be a clash, so make people take turns) and MVCC (optimistic — assume clashes are rare, so let everyone work on their own copy and only check at the end). This lesson is the two playbooks.
Locking — the bathroom key
The first strategy is LOCKING, and it's called pessimistic because it assumes 'two people WILL want this row at once, so let's prevent any clash up front.' Think of a single-toilet bathroom with one key. To use the row, a transaction must grab its lock — take the key. While it holds the lock, anyone else who wants that row has to WAIT outside until the first one finishes and hands the key back. Only one writer touches the row at a time, so they can't possibly clobber each other — the lost-update problem just can't happen, because the second transaction never gets to start until the first is fully done. It's simple and dead-reliable. The cost is waiting: while one transaction holds a lock, everyone else who needs that row is stuck doing nothing, which slows things down when lots of people want the same hot row.
MVCC — everyone edits their own copy
The second strategy is MVCC — Multi-Version Concurrency Control. It's called optimistic because it bets 'clashes are actually rare, so let's not make anyone wait; we'll deal with a clash only if one really happens.' Here's the trick: instead of one shared row everyone fights over, the database keeps multiple VERSIONS of the row. When a transaction starts, it gets a frozen SNAPSHOT — a personal copy of the data as it looked at that instant. It reads and writes against its own copy, never blocking anyone, and nobody blocks it. Readers never wait for writers, writers never wait for readers. That's a huge win for how many transactions can run at once. The catch comes at COMMIT. The database checks: 'did anyone else change this row while you were working on your copy?' If not, your changes go in cleanly. If yes — a real conflict — one of the transactions is aborted and must retry. So MVCC trades simple waiting for cleverer bookkeeping (keeping versions around) plus the occasional retry.
When locks collide: the door to deadlock
Locking keeps writers from clobbering each other, but it opens a new danger. What if Transaction 1 grabs the lock on row A and then wants row B — while Transaction 2 has already grabbed row B and now wants row A? T1 is waiting for T2 to release B; T2 is waiting for T1 to release A. Neither will ever let go. Both are frozen forever. That stuck-forever standoff is called a DEADLOCK — the deadly embrace, two transactions each holding what the other needs. It's the price of locking, and databases have a whole mechanism to detect and break it. That's the very next lesson, deadlock in databases. (MVCC dodges most of this by not making readers wait at all, but its commit-time conflict checks can still force aborts and retries — there's no completely free lunch in concurrency.)
It's a tradeoff
| Option | 👍 Pro | 👎 Con |
|---|---|---|
| Locking (pessimistic) | Simple and rock-solid: grab the lock, and no one else can touch the row, so clashes are impossible. Easy to reason about. | Causes waiting and blocking — others sit idle while a lock is held — and can create deadlocks (two transactions each waiting on the other's lock). |
| MVCC (optimistic) | Far more concurrency: readers never block writers and vice versa, since everyone works on their own snapshot. Great when conflicts are rare. | More bookkeeping — the database keeps multiple versions of rows — and when a real conflict does happen at commit, a transaction must abort and retry. |
Questions you might have
▸Why call locking 'pessimistic' and MVCC 'optimistic'?
It's about what each one ASSUMES. Locking is pessimistic — it expects clashes, so it prevents them up front by making everyone take turns. MVCC is optimistic — it expects clashes to be rare, so it lets everyone proceed freely and only checks for a problem at the very end. Pessimist locks the door first; optimist hopes the door's fine and checks on the way out.
▸With MVCC, if everyone edits their own copy, how does the real row ever get updated?
At commit. Your snapshot is just for working safely without blocking anyone. When you commit, the database takes your changes and writes them as a new version of the real row — after checking nobody else changed it underneath you. If someone did, you conflict and have to retry. So the copies are temporary workspaces; commit is when one of them becomes the new truth.
▸Does MVCC mean there are no locks at all?
Not quite. MVCC removes the need for readers and writers to block EACH OTHER, which is the big win. But two writers aiming at the same row still need to be sorted out — many real databases use MVCC for reads and still use locks (or commit-time checks) to settle write-write conflicts. It reduces locking dramatically rather than abolishing it.
▸If locking can cause deadlocks, why use it at all?
Because it's simple, predictable, and guarantees correctness, and deadlocks are usually rare and the database can detect and break them automatically. For many workloads the occasional deadlock is a fine price for dead-simple, reliable safety. The next lesson shows exactly how databases catch and resolve them.
Best read after: ACID transactions