Database Transactions: ACID Properties, Schedules, and Concurrency Control

What is a transaction?

A sequence of database operations executed as one logical unit.

Intuition of a transaction

A "mini-program" that must happen completely or not at all.

Example of a transaction

Withdraw $100: Read balance → subtract → write new balance.

Why transactions matter

Everything in concurrency control + recovery is built around transactions.

ACID: Atomicity

A transaction's changes occur entirely or not at all.

Intuition of Atomicity

No partial updates allowed.

Example of Atomicity

If ATM crashes mid-withdrawal, your balance must not change.

Why Atomicity matters

Exam will ask which component (logging) enforces atomicity.

ACID: Consistency

A transaction must bring the DB from one valid state to another valid state.

Intuition of Consistency

"Rules of the world" must remain true.

Example of Consistency

Account balance must never be negative.

Why Consistency matters

Consistency constraints appear in SQL and recovery questions.

ACID: Isolation

Concurrent transactions must behave as if executed one at a time.

Intuition of Isolation

Your transaction should run as if nobody else exists.

Example of Isolation

Two transfers happening shouldn't see each other's temporary states.

Why Isolation matters

All serializability questions = testing isolation.

ACID: Durability

Once a transaction commits, its changes are permanent even if system crashes.

Intuition of Durability

If it says "Transaction Complete," it must survive any crash.

Example of Durability

Deposit $500 → power outage → money still deposited.

Why Durability matters

ARIES + WAL enforce durability.

What is a schedule?

An interleaving of operations from multiple transactions.

Intuition of a schedule

What actually happens when transactions run concurrently.

Example of a schedule

T1: R(X), T2: W(Y), T1: W(X), T2: R(Y)

Why schedules matter

Used to determine serializability.

What is a serial schedule?

Transactions run one after another, with no interleaving.

Intuition of a serial schedule

Take turns — T1 runs, then T2 runs.

Example of a serial schedule

T1 R(X) W(X) → T2 R(X) W(X)

Why serial schedules matter

Always correct; used as a baseline for correctness.