Transactions & Concurrency Control

Atomicity means a transaction is all-or-nothing: either every change it made becomes visible together, or none does. If it fails partway, the engine reverses the work already done, leaving the database as if the transaction never ran.

C means a transaction moves the database from one valid state to another: every declared integrity rule (primary key, unique, not null, check, foreign key) holds at commit. The engine enforces the declared rules; the developer decides which rules to declare.

It promises the committed changes survive a crash or power loss of that node: the data reached stable storage, not just memory. It does not promise the data survives losing that machine or its disk, nor that any replica already has it.

Isolation means concurrent transactions must not see each other's unfinished work: the outcome should match some order where they ran one at a time. Enforcing that fully costs locking, blocking and aborts, so engines offer weaker levels that trade specific anomalies for throughput.

The engine durably records how to reverse each change before making it — before-images or inverse operations — and rollback replays those backwards. Multi-version engines instead leave the old row version in place and simply never make the new one visible.

A dirty read is reading a row version written by a transaction that has not committed. If that writer rolls back, the value you read never existed in any committed state, so any decision, calculation or write you based on it is derived from data the database itself disowned.

Two transactions read the same row, each computes a new value from what it read, then both write. The second write overwrites the first, so one committed change silently disappears. No error is raised — only the final value is wrong.

A transaction reads a row, another transaction updates that row and commits, and the first transaction reads the same row again and sees a different value. The re-read is not repeatable — the data changed underneath a transaction that is still running.

A phantom read is when a transaction re-runs the same search condition and the set of matching rows changes, because another transaction committed inserts or deletes. A non-repeatable read is when a row you already read comes back with different values.

The in-place form reads and writes inside one statement while holding the row's write lock, so no other transaction can slip in between. The application version leaves a gap between the read and the write in which another transaction can commit a change you never see.

It guarantees one thing: you never read data written by a transaction that has not committed — no dirty reads. Everything weaker still happens: non-repeatable reads, phantoms, read skew across statements, and lost updates in read-modify-write code.

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.

An update writes a new version of the row instead of overwriting it in place. The reader keeps seeing the version that was current when its snapshot began, so it needs no lock on the row, and the writer has no reader lock to wait behind.

Updates and deletes leave the old row version behind so older snapshots can still read it. Until a background cleanup process reclaims those dead versions, tables and indexes hold space no query needs. That waste is bloat.

Every row version records the id of the transaction that created it and, once superseded or deleted, the id of the transaction that expired it. A reader sees a version only if its creator had committed as of the reader's snapshot and its expirer had not.

The first updater takes an exclusive row lock and creates a new version. The second UPDATE finds the row locked and blocks. When the first commits, the second either re-reads the new version and applies its change, or aborts with a serialization error, depending on isolation level.

Cleanup may only remove versions that no live snapshot can see. The oldest active snapshot sets a single global horizon; anything that died after it must be kept. One ancient transaction holds that horizon back, so dead rows everywhere become unreclaimable.

It is a deadlock. The database detects the cycle of waits, picks one transaction as victim, and rolls it back with a deadlock error; the survivor proceeds. The application must catch that error and retry the whole transaction, ideally after a short backoff.

Growing phase: the transaction may take locks but release none. Shrinking phase: it may release locks but take no new ones. The lock point is the instant between them, where it holds every lock it will ever hold.

A shared (S) lock lets other shared locks in but blocks exclusive ones — many readers, no writer. An exclusive (X) lock excludes everything, readers and writers alike. Writes take X on the rows they modify; reads take S only in lock-based (non-MVCC) reading.

Fine granularity (row) maximises concurrency but costs memory and CPU per lock; coarse granularity (table) is nearly free to track but serialises unrelated work. Engines choose by how many rows a statement touches — few rows, lock rows; whole table, lock the table.

FOR UPDATE takes exclusive row locks on the rows it reads, held until commit, so no one else can modify or lock them — it turns a read into a claim. FOR SHARE takes shared row locks: others may still read, but nobody may modify the rows until you commit.

For the whole API call the transaction keeps its locks, keeps its read snapshot, and holds a pooled connection. Other writers queue behind those locks and old row versions cannot be cleaned up. Move the remote call outside the transaction.

Pessimistic locks the row when you read it, so the second writer waits — safe, but it holds locks and blocks. Optimistic takes no lock: you remember a version, and the update applies only if the version is unchanged; otherwise you re-read and retry. Rare conflicts favor optimistic; hot contended rows favor pessimistic.

In autocommit each statement is its own transaction, committed the moment it succeeds. So the insert is durable even if the update then fails, leaving an order with no stock deducted, and nothing can roll it back. Both statements must run inside one explicit transaction on the same connection.

An open transaction pins a snapshot, and the engine may not reclaim any row version that the oldest live snapshot could still need. So while it sits there, superseded versions from every other transaction accumulate: table and index bloat, growing undo storage, slower scans.

Zero affected rows means someone else wrote the row, so your in-memory copy is stale. Retry the whole unit of work — new transaction, fresh read, recompute, write again — not just the UPDATE. Cap attempts (3–5), use jittered backoff, and keep external side effects out of the retried block or make them idempotent.