Transactions and isolation levels

What a transaction is

A transaction groups several operations so they are treated as one unit. The classic guarantees are ACID:

• Atomicity: all or nothing.
• Consistency: rules (constraints) always hold afterwards.
• Isolation: concurrent transactions do not see each other's half-done work.
• Durability: once committed, it survives a crash.

Isolation is the subtle one, because perfect isolation is expensive.

What can go wrong when transactions overlap

Anomaly	What happens
Dirty read	You read data another transaction has not committed (and may roll back)
Non-repeatable read	You read a row twice and it changed in between
Phantom read	You run the same query twice and new matching rows appear
Lost update	Two transactions read, then both write, and one overwrites the other

Isolation levels

Databases offer levels that trade safety for speed:

Level	Prevents	Still allows
Read uncommitted	nothing much	dirty reads and more
Read committed (a common default)	dirty reads	non-repeatable and phantom reads
Repeatable read	dirty and non-repeatable reads	phantoms (varies by database)
Serializable	all of the above	nothing, but slower or more retries

The exact behaviour differs between databases, so read your database's documentation instead of assuming.

100%

Figure SDA-CORE-02-1 · Isolation levels · © forcsguy.com

How databases implement it

• Locks: readers and writers wait for each other. Simple, but can block and deadlock.
• MVCC (multi-version concurrency control): each transaction sees a consistent snapshot while writers create new versions. Readers do not block writers. Used by PostgreSQL, MySQL (InnoDB) and others.

Two mechanisms keep concurrent transactions apart. Locks make others wait; row versions let readers carry on:

100%

Figure SDA-CORE-02-2 · Locks versus row versions · © forcsguy.com

A multi-version example

Say row X holds 100 at version 1. Transaction A begins and takes a snapshot. Transaction B updates X to 150, creating version 2, and commits. A still reads 100 because its snapshot predates version 2, while a transaction started afterwards reads 150. Readers never block writers; old versions are cleaned up once no snapshot needs them.

Practical advice

• Keep transactions short. Long ones hold locks and snapshots and hurt everyone.
• Prefer conditional updates (WHERE seats > 0) over read-then-write in application code.
• Be ready to retry on serialization failures and deadlocks.
• Across several databases or services there is no single transaction: use patterns such as sagas (later lessons).

Real-world reading