Transactions & Concurrency Control

1. What are the ACID properties in a transaction, and why are they critical for database integrity?

ACID is a set of four guarantees that every database transaction should provide. They make sure your data stays correct even when things go wrong.

• Atomicity — a transaction is all or nothing. If any step fails, the whole thing is rolled back. No partial updates.
• Consistency — a transaction takes the database from one valid state to another. Rules, constraints, and triggers are never broken mid-way.
• Isolation — concurrent transactions behave as if they run one after another, so they don’t see each other’s half-finished work.
• Durability — once a transaction is committed, it stays committed, even if the power dies right after.

Why it matters: Think of a bank transfer. Money leaves Account A and enters Account B. If the system crashes after the money leaves but before it arrives, that money is lost. Atomicity rolls both steps back together, so the transfer either fully happens or fully doesn’t.

2. What is a Deadlock in the context of database transactions, and how can it be avoided?

A deadlock happens when two or more transactions are stuck waiting for resources that the other one holds, and neither can move forward.

Example:

• Transaction A locks row 1 and waits for row 2.
• Transaction B locks row 2 and waits for row 1.

Each is holding something the other needs. Unless something intervenes, both wait forever.

How databases handle it: The database has a deadlock detector that watches for circular waits. When it finds one, it picks a victim transaction, rolls it back, and lets the other one finish. The rolled-back transaction can be retried.

How to avoid deadlocks:

• Access resources in a consistent order (always lock row 1 before row 2).
• Keep transactions short so they hold locks for less time.
• Use the same lock granularity across the application.
• Avoid user input inside a transaction, since waiting on a human invites deadlock.

The key idea: deadlocks aren’t about slow queries or low memory — they’re about circular waiting between transactions.

3. What does the ‘Atomicity’ property in ACID ensure?

Atomicity means a transaction is all-or-nothing. Either every operation inside it completes successfully, or none of them do.

Example: Transfer ₹500 from A to B. Two steps: debit A, credit B. If the credit fails, atomicity rolls back the debit too. You never end up with money taken from A but not delivered to B.

If the system crashes mid-transaction, the database uses the transaction log to undo any partially applied changes, restoring the pre-transaction state.

4. What is a ‘Dirty Read’ in transaction management?

A dirty read happens when one transaction reads data that another transaction has modified but not yet committed.

Example sequence:

1. T1 updates a row’s balance from 100 to 500 (uncommitted).
2. T2 reads the balance and sees 500.
3. T1 rolls back — the balance is back to 100.

T2 made decisions based on data that never actually existed. That’s the dirty read.

Isolation levels prevent this: READ COMMITTED stops a transaction from seeing uncommitted changes from others, which is why it’s the default in most databases.

5. What is the purpose of a Two-Phase Locking (2PL) protocol?

Two-Phase Locking is a concurrency control protocol that guarantees serializability — meaning concurrent transactions produce the same result as if they ran one after another.

It has two phases:

1. Growing phase — the transaction acquires locks but never releases any.
2. Shrinking phase — the transaction releases locks but never acquires new ones.

Because a transaction can’t release a lock and then grab more, no two transactions can deadlock on a partial lock-release/acquire cycle in a way that breaks serializability.

One catch: the basic 2PL doesn’t eliminate deadlocks entirely — transactions can still wait on each other’s held locks. Strict 2PL (holding all locks until commit) is what most databases actually use.

6. What is a ‘Phantom Read’ anomaly in a database?

A phantom read happens when a transaction runs the same query twice and a second time finds new rows that a concurrent committed transaction inserted in between.

Example sequence:

1. T1 runs SELECT * FROM orders WHERE status = 'pending' — gets 3 rows.
2. T2 inserts a new pending order and commits.
3. T1 runs the same SELECT again — now sees 4 rows.

The new row “appeared like a phantom.” Row-level locks can’t prevent this, because the inserted row didn’t exist to be locked.

The SERIALIZABLE isolation level fixes it with range/gap locks that block inserts into the queried range.

7. What is a Live Lock? How is it different from a Deadlock?

Both are concurrency failures, but they feel different.

• Deadlock — each process holds a resource the other needs. Neither can move. They wait forever.
• Live lock — processes keep changing state and retrying, but make no actual progress. The CPU is busy, but the work never completes.

Analogy: Two people meet in a hallway. Deadlock: both stand still refusing to step aside. Live lock: both keep stepping left and right into each other, never passing.

In databases, livelocks are rarer and are usually handled by adding random backoff to retries.

8. What is the role of a Write-Ahead Log (WAL) in a transaction engine?

A Write-Ahead Log records changes to a persistent log before they’re applied to the actual database blocks.

The rule is simple: the log entry must be durable before the data change is considered committed. If the system crashes, the database replays the log to restore any changes that were logged but not yet written to disk.

Why it’s powerful: it gives you durability and crash recovery without having to flush every data block on every commit. Writing one small log record is far cheaper than writing many random data pages.

PostgreSQL, MySQL InnoDB, and SQLite all use WAL-style logging.

9. What is the difference between Optimistic and Pessimistic Concurrency Control?

The two approaches differ on when they lock.

• Pessimistic — locks the data as soon as you access it and holds the lock until commit. Assumes conflicts are frequent, so it prevents them upfront. Safe but serializes work.
• Optimistic — reads without locking, and only checks for conflicts at commit time (via a version number or timestamp). Assumes conflicts are rare, so it detects them late and rolls back the loser.

	Pessimistic	Optimistic
Lock time	Immediately on access	At commit/validation
Assumption	Conflicts are common	Conflicts are rare
Best for	High-contention writes	Read-heavy, low-contention workloads

If your app mostly reads, optimistic wins. If writes collide often, pessimistic avoids wasted retries.

10. What does the ‘Consistency’ property in ACID mean?

Consistency means a transaction moves the database from one valid state to another. It never leaves the database violating its rules.

The rules include: primary keys stay unique, foreign keys point to real rows, CHECK constraints hold, column types match.

Example: A bank transfer must preserve the invariant “total money across all accounts is unchanged.” If a transfer debits one account but the credit violates a constraint and is blocked, the whole transaction rolls back so the invariant stays intact.

Consistency is about the database’s correctness rules, not about performance or replication timing.

11. What is a ‘Cascading Rollback’ in transaction execution?

A cascading rollback happens when one transaction fails and forces other transactions to roll back too — because they had read uncommitted data from it.

Example sequence:

1. T1 updates row X (uncommitted).
2. T2 reads row X, sees T1’s new value.
3. T1 fails and rolls back.
4. T2 already made decisions based on T1’s now-reverted value, so T2 must roll back as well.

T2’s rollback might then force T3 to roll back, and so on — a chain reaction.

This is why strict isolation (transactions can’t read uncommitted data) is important — it prevents cascading rollbacks entirely.

12. How does a DBMS typically handle or resolve a Deadlock situation?

The DBMS detects deadlocks and resolves them by choosing a victim:

1. Detection — a wait-for graph tracks which transactions wait for which locks. A cycle in that graph = deadlock.
2. Victim selection — pick one transaction to sacrifice, usually the one that has done the least work or is the cheapest to roll back.
3. Rollback — the victim is rolled back, releasing all its locks.
4. The surviving transactions proceed; the victim’s application gets an error and can retry.

Some systems avoid detection entirely by using lock timeouts — if a transaction waits too long, it gives up on its own. Both approaches ensure the system never waits forever.

13. What are Database Locks?

Locks are mechanisms that prevent concurrent sessions from corrupting the same data. The main types:

• Shared (S) lock — allows multiple readers at once; blocks writers.
• Exclusive (X) lock — allows only one session; blocks everyone else.
• Update (U) lock — a middle step before upgrading to exclusive, preventing two sessions from both waiting to upgrade.
• Intent locks — signal at a higher level (e.g., table) that a lower level (row) is locked, so the database can check conflicts efficiently.

Example: Two sessions SELECT the same row (shared locks — fine). One session tries UPDATE — it needs an exclusive lock, which waits until the shared locks release.

Locking is what gives transactions their isolation, but it’s also what causes blocking and, in the worst case, deadlocks.

14. What are the transaction isolation levels?

The isolation level defines how much concurrency a transaction tolerates — how isolated one transaction’s work is from others. There are four levels, from weakest to strongest:

• Read Uncommitted — a transaction can read uncommitted data from others (allows dirty reads). Fastest, least safe. Almost never used in practice.
• Read Committed — only committed data is readable — no dirty reads. But two reads in one transaction can see different values if another commits between them (non-repeatable read). The default in PostgreSQL, SQL Server, Oracle.
• Repeatable Read — a value read once stays the same for the transaction — no dirty reads, no non-repeatable reads. But new rows can still appear between reads (phantom reads). The default in MySQL/InnoDB.
• Serializable — transactions behave as if they ran one after another. No dirty reads, no non-repeatable reads, no phantoms — full isolation. Costs the most concurrency.

Level	Dirty read	Non-repeatable read	Phantom read
Read Uncommitted	✔ allowed	✔ allowed	✔ allowed
Read Committed	✗	✔ allowed	✔ allowed
Repeatable Read	✗	✗	✔ allowed
Serializable	✗	✗	✗

The ladder to remember: dirty → non-repeatable → phantom, each weaker level allows the next anomaly. Serializable blocks phantoms (via range locks / MVCC snapshots); Repeatable Read handles rows but not new rows. Trade-off: stronger isolation = fewer anomalies = less concurrency.

15. What is serializability and how do you check it with a precedence graph?

Serializability is the guarantee that a concurrent schedule produces the same result as if the transactions ran one after the other (serially). A serializable schedule is correct even though operations interleaved.

Conflict serializability is the practical test. Two operations conflict if they involve different transactions, access the same data, and at least one is a write. A schedule is conflict-serializable if it can be reordered into a serial schedule by swapping non-conflicting operations.

The precedence graph method:

1. Nodes = transactions.
2. Draw edge Ti → Tj if an operation of Ti conflicts with a later operation of Tj.
3. If the graph is acyclic → the schedule is conflict-serializable. If it has a cycle → it’s not.

Schedule: T1: r(A)          T2:          w(A)   r(B)          w(B)
Edges: T1 → T2 (T1 read A before T2 wrote A)
       T2 → T1 (T2 wrote B before T1... wait, T1 has no B op)
       → acyclic → serializable

Two-phase locking (2PL) is the mechanism that produces conflict-serializable schedules; the precedence graph is the way you verify one. A cycle in the graph means the transactions can’t be reordered into a serial execution — that schedule can corrupt data. The interview one-liner: serializable = equivalent to some serial order; precedence graph acyclic = serializable.