Isolation Levels

A dirty read is seeing a row written by a transaction that has not committed — data that may be rolled back and never have existed. READ UNCOMMITTED is the only standard level that allows it; it is the weakest level and permits every other anomaly too.

That is a non-repeatable read. REPEATABLE READ gives a transaction a stable view of rows it has read — a fixed snapshot, or read locks held until commit — so the second SELECT returns the same value as the first.

One update is silently lost: both read 100, both write their own result, the second overwrites the first and no error is raised. Fixes: do the arithmetic in one UPDATE statement, lock the row on read with SELECT ... FOR UPDATE, or use an optimistic version column and retry when zero rows change.

READ COMMITTED establishes a new view of committed data at the start of every statement, not once per transaction. So any transaction that commits between your two queries becomes visible to the second one. A transaction-wide view would hide those commits instead.

ANSI REPEATABLE READ still permits phantoms: rows another transaction inserts or deletes can change the result of a repeated range query. Snapshot-based engines read everything from one fixed snapshot, so later inserts stay invisible and plain reads show no phantoms.

It guarantees the committed result is identical to running the transactions one at a time in some order, with no overlap. The engine may pick any such order, so it can run them concurrently and only has to produce an equivalent outcome, not a specific one.

Wrap the whole transaction in a loop: on a serialization failure, roll back and re-run everything including the reads, with a capped number of attempts and jittered backoff. Keep external side effects out of the retried block, and retry only serialization and deadlock errors.

Snapshot isolation reads a consistent snapshot fixed at transaction start and checks only write-write conflicts at commit. Two transactions that read overlapping data but write different rows never conflict, so read-write dependencies go unchecked. That gap allows write skew.

Your statement blocks on that row's write lock. If the blocker rolls back, your original view stands and you update the row. If it commits, typical engines re-examine the newest committed version: the row is skipped if it was deleted or no longer matches your WHERE, and updated using that new version if it does.

Lock-based: readers block writers, phantoms still possible, failures appear as lock waits and deadlocks. Snapshot-based: reads never block, plain reads see no phantoms, but the view goes stale and conflicting writes fail with a serialization error the application must retry.

That is write skew: each transaction verified the invariant on its own snapshot and then inserted a different row, so no write-write conflict was detected. Fix by switching to true serializability, locking or materializing the contended resource, or enforcing the rule with a database constraint.

Multi-versioning already lets readers proceed without blocking on writers, so exposing uncommitted versions buys nothing and would require extra machinery. It is conformant: the standard says which anomalies a level must prevent, and preventing more than required is always allowed.

No to both. Isolation levels govern reads; write locks are unaffected. Your writes still take exclusive row locks held until commit, and you still block on rows an uncommitted transaction has modified. Dirty writes are forbidden at every level, including this one.

Only one can commit. Under first-committer-wins the second commit is rejected; under first-updater-wins the second update blocks until the first ends and then aborts. The loser gets a serialization failure and must retry. This prevents lost update, not write skew.

Another transaction updated that row after your snapshot. Overwriting would lose their update; showing you their value would break read stability — so the engine aborts you. The fix is to catch the failure, roll back, and re-run the whole transaction, re-reading everything.

Locking prevents non-serializable schedules up front: locks held to commit, plus predicate or index-range locks so nobody can insert into a range you read — cost is blocking and deadlocks. SSI runs on snapshots, tracks read-write dependencies, and aborts a transaction when a dangerous pattern appears — cost is false-positive aborts.

It is the cheapest level that removes the clearly unacceptable anomaly. Reads never block and never abort, version retention stays short, and most OLTP work is single-statement anyway. Escalate only where a real invariant spans statements and constraints or row locks cannot express it.

Expect throughput to fall with contention, not uniformly: uncontended paths change little, hot rows and wide scans degrade sharply through waits or aborts, and abort rates rise superlinearly with transaction length. Weigh declarative constraints, single-statement updates, explicit locking and targeted use before adopting it globally.

SSI tracks which transactions read data that another later wrote (anti-dependencies) and aborts when a transaction has both an incoming and an outgoing such edge — the dangerous structure. Detection is conservative, so some aborts are false positives. Adopting it means budgeting for aborts, short transactions, and a universal retry path.

Defensible only for read-only, approximate, disposable results — rough counts, an operator peek during a long batch, progress indicators. Beyond rolled-back data, an unlocked scan can double-count or miss rows when concurrent writes move them, so even the approximation can be wrong in unbounded ways.