Transactions & Concurrency

A transaction is a group of operations that must behave as one unit, like transferring ₹500:

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 'A';
UPDATE accounts SET balance = balance + 500 WHERE id = 'B';
COMMIT;

ACID

Property	Meaning	In the transfer	How the DBMS does it
Atomicity	All or nothing	Never debit A without crediting B	Undo log, rollback
Consistency	Constraints hold before and after	Total money is unchanged; no negative balances	Constraints plus correct transaction logic
Isolation	Concurrent transactions don’t see each other’s partial work	Another query never sees ₹500 “in flight”	Locking or MVCC
Durability	Once committed, it survives crashes	After “transfer successful”, a power cut can’t undo it	Write-ahead log flushed to disk at commit

Transaction states

stateDiagram-v2
  [*] --> Active
  Active --> PartiallyCommitted: last statement done
  PartiallyCommitted --> Committed: changes made durable
  Active --> Failed: error
  PartiallyCommitted --> Failed: write to disk fails
  Failed --> Aborted: rolled back
  Committed --> [*]
  Aborted --> [*]

After aborting, the system can restart the transaction (if the failure was temporary) or kill it.

Concurrency problems

Running transactions concurrently improves throughput but, without control, causes anomalies:

Problem	What happens
Lost update	T1 and T2 both read balance 1000, each adds 100 and writes; the result is 1100, not 1200
Dirty read	T2 reads T1’s uncommitted change; T1 then aborts
Non-repeatable read	T1 reads a row twice and gets different values because T2 updated and committed in between
Phantom read	T1 runs the same range query twice and new rows appear (T2 inserted them)

Schedules and serializability

A schedule is an interleaving of the operations of several transactions. A serial schedule runs them one after another, which is always correct but slow. A schedule is serializable if its effect equals some serial schedule.

Conflict serializability

Two operations conflict if they belong to different transactions, use the same item, and at least one is a write (R–W, W–R, W–W).

Test: draw a precedence graph with one node per transaction and an edge Ti → Tj whenever an operation of Ti conflicts with a later operation of Tj. The schedule is conflict serializable iff the graph has no cycle; any topological order is an equivalent serial schedule.

S:  T1: R(A)          W(A)
    T2:       R(A)          W(A)

T1’s R(A) comes before T2’s W(A), so T1 → T2. T2’s R(A) comes before T1’s W(A), so T2 → T1. That’s a cycle: not conflict serializable (this is the lost-update pattern).

View serializability is weaker (it accepts more schedules, such as some with blind writes), but testing it is NP-complete, so real systems enforce conflict serializability.

Recoverability

Schedule	Rule	Why it matters
Irrecoverable	T2 reads T1’s write and commits before T1	If T1 aborts, T2’s commit can’t be undone
Recoverable	If T2 reads T1’s write, T2 commits only after T1	Safe, but T1’s abort forces T2 to abort too
Cascadeless	Transactions read only committed data	No cascading rollbacks
Strict	No read or write of an item until the last writer has committed or aborted	Simple undo using before-images

Strict ⊂ Cascadeless ⊂ Recoverable. Serializability and recoverability are separate questions: a schedule can be serializable yet not recoverable.

Concurrency control

Two-phase locking (2PL)

Shared (S) lock to read, exclusive (X) lock to write. S is compatible with S; X is compatible with nothing.
Each transaction has a growing phase (acquire locks, release none) and then a shrinking phase (release locks, acquire none).

2PL guarantees conflict serializability. Strict 2PL holds X locks until commit, which also makes schedules strict (cascadeless). Locking can deadlock; databases detect cycles and abort a victim, or use timeouts.

Other approaches

Timestamp ordering: each transaction gets a timestamp, and conflicting operations must follow timestamp order, or the later transaction is rolled back.
MVCC (multiversion concurrency control): writers create new versions; readers see a consistent snapshot without blocking writers. Used by PostgreSQL, MySQL InnoDB and Oracle.

Isolation levels (SQL standard)

Level	Dirty read	Non-repeatable read	Phantom
Read Uncommitted	possible	possible	possible
Read Committed	prevented	possible	possible
Repeatable Read	prevented	prevented	possible*
Serializable	prevented	prevented	prevented

*PostgreSQL’s and MySQL InnoDB’s Repeatable Read also stop most phantoms through snapshots. Read Committed is the default in PostgreSQL and Oracle; Repeatable Read in MySQL.

Recovery in one paragraph

Before changing data on disk, the DBMS writes a log record (write-ahead logging). After a crash it redoes committed transactions whose changes hadn’t reached disk and undoes uncommitted ones. Checkpoints limit how much log must be replayed.

Questions interviewers ask

Explain ACID with an example. The bank transfer.
What is a dirty read / phantom read?
How do you check whether a schedule is conflict serializable? The precedence graph and a cycle test.
What is 2PL? Does it prevent deadlocks? It guarantees serializability, but no, it can deadlock.
What isolation level would you use for a ticket-booking system? Serializable, or Read Committed with SELECT … FOR UPDATE on the seat row.

ACID

Transaction states

Concurrency problems

Schedules and serializability

Conflict serializability

Recoverability

Concurrency control

Two-phase locking (2PL)

Other approaches

Isolation levels (SQL standard)

Recovery in one paragraph

Questions interviewers ask

Check yourself