Transactions

A transaction is a group of operations that the database treats as a single unit. All succeed, or none do. Without transactions, two concurrent writes can interleave in ways that leave data permanently inconsistent.

Analogy

Think of a wedding ceremony. The officiant asks each person "do you?" in turn; only after both say "I do" and the paperwork is signed does the marriage become official. If the power cuts out mid-vow, the couple is not half-married — the ceremony rewinds and has to start again. Isolation is keeping the guests quiet in the pews: they don't get to whisper rumours about the vows until the ceremony concludes. A deadlock is two couples both waiting to use the only aisle in opposite directions, refusing to step aside. Optimistic locking is the wedding venue double-booking on purpose: if the other couple actually shows up, somebody's rescheduling.

ACID

The four guarantees every ACID-compliant database provides:

Property	Guarantee
Atomicity	All operations in the transaction commit, or none do. A crash mid-transaction leaves no partial state.
Consistency	The transaction moves the database from one valid state to another, respecting all constraints and rules.
Isolation	Concurrent transactions behave as if they ran serially — their intermediate state is hidden from each other.
Durability	Once committed, changes survive crashes, power loss, and restarts. The engine writes to disk before acknowledging the commit.

Isolation levels

Isolation is expensive. Databases offer a dial:

Level	Dirty read	Non-repeatable read	Phantom read
READ UNCOMMITTED	possible	possible	possible
READ COMMITTED	prevented	possible	possible
REPEATABLE READ	prevented	prevented	possible
SERIALIZABLE	prevented	prevented	prevented

Dirty read

T1 reads data that T2 wrote but has not committed. If T2 rolls back, T1's read was based on data that never existed.

Non-repeatable read

T1 reads a row. T2 updates and commits that row. T1 reads the row again — the value changed within the same transaction.

Phantom read

T1 queries a range of rows. T2 inserts a row that falls in that range and commits. T1 re-runs the query — there is now a "phantom" row it did not see before.

Deadlocks

T1 holds lock A and waits for lock B. T2 holds lock B and waits for lock A. Neither can proceed. The database detects this cycle and rolls back one transaction (the "victim"), returning an error to that connection.

Prevention strategies:

• Always acquire locks in the same order across transactions.
• Use SELECT ... FOR UPDATE with an explicit lock order.
• Keep transactions short — less time to accumulate locks.

Optimistic vs pessimistic locking

Pessimistic locking (SELECT ... FOR UPDATE) blocks other writers immediately. Safe, but causes contention.

Optimistic locking reads a version column (or timestamp) and includes it in the WHERE clause of the update:

UPDATE orders
SET status = 'shipped', version = version + 1
WHERE id = 42 AND version = 7;

If another writer committed first, version will not match, zero rows update, and the application retries. No locks held, but retries can pile up under high contention.

The playground

The transaction simulator lets you step through two interleaved transactions. Choose an isolation level, then step T1 and T2 operations one by one. The grid shows which anomalies are permitted at each level — and whether the current step would expose one.