MVCC and Locking: Optimistic vs Pessimistic
MVCC lets readers use old row versions so they never block writers. Locks still decide write conflicts: pessimistic locks first, optimistic checks a version at commit. Run output included.
Multi-version concurrency control (MVCC) keeps several versions of each row, so a reader sees a fixed snapshot while writers create newer versions, and plain reads never wait for writes. The detail interviewers expect: MVCC does not remove write conflicts, which are handled by pessimistic locking (SELECT ... FOR UPDATE) or optimistic locking (check a version number on save).
Multi-version concurrency control keeps several versions of each row so a reader sees a fixed snapshot while writers create newer versions, which means plain reads never wait for writes. It does not remove write conflicts. For those you either lock the row before changing it (pessimistic) or change it freely and check a version number when you save (optimistic).
- Under MVCC, a write creates a new row version instead of overwriting, and a read returns the newest version committed at or before the transaction's snapshot.
- PostgreSQL keeps old row versions in the table and removes them with VACUUM, while MySQL InnoDB keeps the newest version in place and rebuilds older ones from undo logs.
- Snapshot isolation does not prevent write skew, where two transactions write different rows and together break an invariant; serializable isolation or an explicit lock does.
- Optimistic locking updates a row with WHERE id = ? AND ver = ?, bumps the version, and retries when the statement reports 0 rows changed.
- In a sqlite3 test with 8 threads each adding 1 to a counter 200 times, naive read-then-write ended at 376 of 1,600, while the optimistic version check reached 1,600 with 13 retries.
- Pessimistic locking with SELECT ... FOR UPDATE suits heavily contended rows, while optimistic locking suits rare conflicts and edits spanning HTTP requests.
01Why plain read and write locks hurt
Suppose a nightly report scans the accounts table while the app keeps moving money. In a purely lock-based engine the report takes shared locks and holds them until it finishes. Every transfer that wants an exclusive lock on one of those rows waits. Flip it around and a single big update stalls every reader behind it.
That design is correct, because nobody sees half-finished work. It is also slow for any workload where reads and writes overlap, and most workloads are like that. The two ideas in this article are the standard answers. MVCC removes the reader-versus-writer waiting. Pessimistic and optimistic locking are the two ways to handle what remains, which is two writers going after the same row.
02MVCC: keep old versions, read from a snapshot
Instead of overwriting a row, the engine writes a new version and keeps the old one for as long as some running transaction might still need it. Every version carries the id or timestamp of the transaction that created it. When a transaction starts (or, in Read Committed, when each statement starts) it takes a snapshot, and a read returns the newest version that was committed at or before that snapshot.
PostgreSQL keeps old versions in the table itself and a background process called VACUUM removes the ones no snapshot can see. MySQL InnoDB keeps the newest version in place and rebuilds older ones from undo logs. The mechanism differs, the visible behaviour is the same.
This is a toy model I wrote to show the rule. It is a simulation of the idea, not how any real engine stores data. The clock ticks on every begin and commit.
import itertools
class Store:
def __init__(self):
self.clock = itertools.count(1)
self.versions = {} # key -> list of (commit_ts, value)
def begin(self):
return Txn(self, next(self.clock))
class Txn:
def __init__(self, store, snap):
self.s, self.snap, self.writes = store, snap, {}
def read(self, k):
if k in self.writes: return self.writes[k]
for ts, v in reversed(self.s.versions.get(k, [])):
if ts <= self.snap: return v
def write(self, k, v): self.writes[k] = v
def commit(self):
# first-committer-wins: abort if someone committed this key after our snapshot
for k in self.writes:
vs = self.s.versions.get(k, [])
if vs and vs[-1][0] > self.snap:
return False
ts = next(self.s.clock)
for k, v in self.writes.items():
self.s.versions.setdefault(k, []).append((ts, v))
return True
s = Store()
t0 = s.begin(); t0.write("balance", 100); print("seed commit", t0.commit())
reader = s.begin()
writer = s.begin(); writer.write("balance", 70); print("writer commit", writer.commit())
print("reader still sees", reader.read("balance"))
print("new txn sees", s.begin().read("balance"))
a, b = s.begin(), s.begin()
a.write("balance", a.read("balance") - 10); b.write("balance", b.read("balance") - 20)
print("a commit", a.commit(), "| b commit", b.commit())
print("versions", s.versions["balance"])Running it prints:
seed commit True
writer commit True
reader still sees 100
new txn sees 70
a commit True | b commit False
versions [(2, 100), (5, 70), (9, 60)]The reader began before the writer committed, so it keeps seeing 100 even after the balance became 70. The new transaction sees 70. The version list shows nothing was overwritten; each commit appended a version stamped with its commit time. The last two lines are the interesting part: transactions a and b both read 70 and tried to save, and the second committer was rejected.
03What MVCC does not fix: write conflicts and write skew
Two kinds of trouble remain.
The first is a plain write-write conflict, shown above where a and b both subtract from the same balance. The engine has to either make the second writer wait or reject it. PostgreSQL at Read Committed makes it wait for the first to finish and then re-reads the row. At Repeatable Read, which in PostgreSQL is snapshot isolation, the second writer gets a serialization failure ("could not serialize access due to concurrent update") and the application must retry.
The second is write skew. Two on-call doctors each check that someone else is still on call, then each marks themselves off. They write different rows, so a row-level conflict check sees nothing wrong. I ran that story against the same toy store:
alice commit True | bob commit True
on call now: False FalseBoth commits succeeded and the rule "at least one doctor on call" is broken. Only true serializable isolation, or an explicit lock on a row both transactions touch, prevents it. This is the classic gap between snapshot isolation and serializable, and it is a favourite follow-up question.
04Pessimistic locking: lock first, then work
The pessimistic assumption is that conflicts are likely, so claim the row before you read it for the purpose of changing it:
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- row is now locked
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
COMMIT; -- lock releasedA second transaction running the same SELECT ... FOR UPDATE blocks until the first commits or rolls back, then sees the new value. No update is lost and nobody has to retry.
The costs are real. A transaction holding locks while the user thinks, or while a slow network call runs, blocks everyone else on that row. Locking several rows in different orders in different transactions causes deadlocks; the database detects one and aborts a victim, so you still need retry code. Keep these transactions short and lock rows in a consistent order, for example by ascending primary key.
05Optimistic locking: change freely, check at the end
The optimistic assumption is that conflicts are rare, so take no lock at all. Read the row together with its version number. When saving, include the version in the WHERE clause and bump it:
UPDATE acct SET bal = ?, ver = ver + 1 WHERE id = 1 AND ver = ?;If the statement reports 0 rows changed, another writer incremented the version in between. Reread and try again. ORMs expose this as a version or row-version attribute.
Here is a measurement with Python's sqlite3, eight threads each adding 1 to a counter 200 times, expected total 1600. The naive version reads the value, yields, and writes value+1. The optimistic version uses the version check and retries.
import sqlite3, threading, os, time, tempfile
path = os.path.join(tempfile.mkdtemp(), "t.db")
c = sqlite3.connect(path); c.execute("create table acct(id integer primary key, bal integer, ver integer)")
c.execute("insert into acct values(1,0,0)"); c.commit(); c.close()
N_THREADS, N_OPS = 8, 200
retries = [0]*N_THREADS
def naive(t):
con = sqlite3.connect(path, timeout=30, isolation_level=None)
for _ in range(N_OPS):
bal = con.execute("select bal from acct where id=1").fetchone()[0]
time.sleep(0) # yield between read and write
con.execute("update acct set bal=? where id=1", (bal+1,))
con.close()
def optimistic(t):
con = sqlite3.connect(path, timeout=30, isolation_level=None)
for _ in range(N_OPS):
while True:
bal, ver = con.execute("select bal, ver from acct where id=1").fetchone()
time.sleep(0)
cur = con.execute("update acct set bal=?, ver=ver+1 where id=1 and ver=?", (bal+1, ver))
if cur.rowcount == 1: break
retries[t] += 1
con.close()
def run(fn):
c = sqlite3.connect(path); c.execute("update acct set bal=0, ver=0"); c.commit(); c.close()
th = [threading.Thread(target=fn, args=(i,)) for i in range(N_THREADS)]
t0 = time.time(); [x.start() for x in th]; [x.join() for x in th]
c = sqlite3.connect(path); r = c.execute("select bal, ver from acct").fetchone(); c.close()
return r
print("expected", N_THREADS*N_OPS)
print("naive read-then-write ->", run(naive))
print("optimistic version check ->", run(optimistic), "retries", sum(retries))One run printed:
expected 1600
naive read-then-write -> (376, 0)
optimistic version check -> (1600, 1600) retries 13The naive code lost 1,224 of 1,600 increments. The optimistic code lost none and needed 13 retries across all 1,600 successful updates. The exact numbers change on every run because they depend on thread timing, but the pattern holds: without a check, lost updates are the norm under overlap.
06Choosing between them
The rule of thumb is contention. If two users rarely edit the same row, optimistic locking costs one extra column and almost never retries, and it works across HTTP requests where holding a database lock for minutes would be unacceptable. A profile edit form is a good fit.
If many transactions fight for the same row, such as a stock counter during a sale or a seat in a booking system, optimistic retries pile up and the work is repeated for nothing. A short pessimistic lock serialises the contenders cleanly. Retries are also a problem when the transaction triggers side effects that cannot be repeated, like sending an email.
MVCC sits underneath both. It keeps the read side cheap, which is why report queries rarely interfere with either strategy. I did not benchmark the crossover point here, because it depends on the engine, hardware and row count; measure it on your own workload if the decision matters.
07Common interview questions
Q: How does MVCC let readers and writers run without blocking each other?
A: Writers create new row versions instead of overwriting, and readers read the newest version committed before their snapshot, so neither needs to wait for the other's lock.
Q: What is the downside of keeping old versions?
A: Dead versions take space until they are cleaned up. PostgreSQL needs VACUUM for this, and a long-running transaction can hold back cleanup because its snapshot may still need old versions.
Q: What is write skew and does snapshot isolation prevent it?
A: Two transactions read overlapping data and write different rows in a way that together breaks an invariant. Snapshot isolation does not prevent it; serializable isolation or an explicit lock does.
Q: Explain optimistic locking in one sentence.
A: Read a version number with the row and update with WHERE version equals that number, retrying if no row was changed.
Q: When would you choose pessimistic locking?
A: When a row is heavily contended or a retry is expensive or has side effects, so queueing on SELECT FOR UPDATE is cheaper than repeated failures.
Q: Can a deadlock occur with optimistic locking?
A: Not from the version check itself, since it holds no lock while you think. Ordinary row locks taken by the UPDATE statements can still deadlock if transactions touch several rows in different orders.