Top 28 DBMS ACID Transactions Interview Questions (2026)

Last Updated: June 2026 | Level: Beginner to Advanced | Format: Q&A with SQL, Transaction Diagrams, and Anomaly Scenarios

ACID transactions are among the most critical DBMS topics in backend SDE interviews. Candidates report that isolation level questions and concurrency anomaly scenarios appear at product companies including Amazon, Flipkart, and Swiggy, as well as in DBMS theory rounds at service companies. Based on public preparation resources and candidate-reported accounts, dirty read and serializable isolation scenarios are the most commonly asked numerical and scenario questions.

---

Table of Contents

1. ACID Properties (Q1-Q6)
2. Transaction Isolation (Q7-Q14)
3. Concurrency Control (Q15-Q21)
4. Recovery and Advanced (Q22-Q28)

---

ACID Properties

Q1. What are the ACID properties? Explain each. Easy

ACID defines the properties that guarantee reliable database transactions even in the presence of concurrent access or system failures.

Property	Guarantee	Example
Atomicity	A transaction is all-or-nothing. Either all operations succeed, or none are applied.	Transfer Rs.500 from A to B: debit A AND credit B. If credit fails, debit is rolled back too.
Consistency	A transaction takes the database from one valid state to another valid state. All constraints remain satisfied.	Total money before transfer = total money after. No negative balances if that is a constraint.
Isolation	Concurrent transactions execute as if they were serial. One transaction's intermediate state is not visible to others.	T1 deducting Rs.500 from A and T2 checking A's balance concurrently: T2 sees either the before or after state, never the mid-transaction state.
Durability	Once a transaction commits, the changes persist even through power failures, crashes, or errors.	After commit confirmation, the Rs.500 transfer survives a server reboot.

---

Q2. What does Atomicity mean? How is it implemented? Medium

Atomicity: Either all operations in a transaction execute, or none do. There is no partial state visible.

Implementation:

1. Write-Ahead Logging (WAL): Before modifying data, write what you are about to do to a log (redo log). On crash mid-transaction, the log tells the DB what was not committed and can undo partial work.

2. Undo log: Records the old values of rows before modification. On rollback (or crash), apply undo log to restore pre-transaction state.

3. Shadow paging: Maintain two copies of pages: current (active) and shadow (previous). On commit, flip the pointer. On abort, discard current copy. (Rarely used today; WAL is dominant.)

SQL example:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
-- System crashes here
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT;

On crash, WAL + undo log: account 1 is restored to pre-transaction balance. The transfer never happened. Atomicity preserved.

---

Q3. What does Consistency mean? What are the different types of constraints? Easy

Consistency: A transaction must bring the database from one consistent state to another. All database invariants (constraints) must hold before and after.

Types of constraints:

Constraint Type	Example	Enforcement
Entity integrity	Primary key cannot be NULL	PRIMARY KEY constraint
Referential integrity	FK must reference existing PK	FOREIGN KEY constraint
Domain constraint	Age must be between 0 and 150	CHECK (age BETWEEN 0 AND 150)
Business rule	Account balance cannot go negative	Application logic + CHECK (balance >= 0)
Unique constraint	Email must be unique	UNIQUE constraint

Important note: Consistency is partly the DBMS's responsibility (enforcing schema constraints) and partly the application's responsibility (business rules that the schema cannot express). A transaction can be atomic, isolated, and durable but still be inconsistent if the application logic is wrong.

---

Q4. What does Isolation mean? Why is it hard to achieve? Medium

Isolation: Concurrent transactions should not interfere with each other. The result of executing multiple transactions concurrently should be the same as executing them serially (one at a time).

Why it is hard: True isolation (serializability) requires extensive synchronization (locking). High levels of isolation hurt performance because:

• Locks prevent concurrent reads/writes.
• More locks = more waiting = lower throughput.
• Higher isolation levels can cause more lock contention and deadlocks.

The spectrum: SQL defines four isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE), each offering a different trade-off between isolation strength and performance. Most applications use READ COMMITTED or REPEATABLE READ.

---

Q5. What does Durability mean? How is it implemented? Easy

Durability: Once a transaction commits, its changes are permanent. They survive power failures, OS crashes, and hardware errors.

Implementation:

1. Write-Ahead Logging (WAL): The DBMS writes the commit record to durable storage (disk, SSD) before acknowledging the commit to the client. If the system crashes after commit, the WAL allows redo of the committed transaction on recovery.

2. Sync/fsync: DBMS calls fsync() to force the operating system to flush data from OS buffer cache to disk. Without this, a commit could be in RAM and lost on power failure.

3. Battery-backed RAID controllers: Write caches in enterprise storage with capacitor backup allow safe async writes.

4. Replication: Durability across hardware failures achieved by replicating commits to multiple machines (synchronous replication: commit confirmed only after majority of replicas acknowledge).

Trade-off: Calling fsync() on every commit adds latency (~10ms per commit on spinning disk). PostgreSQL's fsync=off and MySQL's innodb_flush_log_at_trx_commit=0 can disable this for performance (at the cost of potential data loss on crash).

---

Q6. What is the difference between a transaction and a statement in SQL? Easy

Aspect	Statement	Transaction
Scope	Single SQL operation (INSERT, UPDATE, DELETE, SELECT)	One or more statements treated as a logical unit
Atomicity	Single statement is atomic by default	Multiple statements, all commit or all rollback
Explicit control	None	BEGIN / COMMIT / ROLLBACK
Auto-commit	In auto-commit mode, each statement is its own transaction	Explicit transaction groups statements

-- Auto-commit mode (each statement is its own transaction):
UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- Committed immediately
UPDATE accounts SET balance = balance + 500 WHERE id = 2;  -- Committed immediately
-- If second statement fails, first is already committed. Money lost!

-- Explicit transaction (atomic):
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;  -- Both committed together, or neither (on ROLLBACK)

---

Transaction Isolation

Q7. What are the three read anomalies in concurrent transactions? Easy

Dirty Read: Reading data written by a transaction that has not yet committed. If that transaction rolls back, you read data that never existed.

T1: UPDATE balance = 500 WHERE id=1 (not committed)
T2: SELECT balance FROM accounts WHERE id=1  --> reads 500 (dirty!)
T1: ROLLBACK  --> balance reverts to original. T2 used non-existent data.

Non-Repeatable Read: Reading the same row twice in the same transaction and getting different results because another committed transaction modified it in between.

T1: SELECT balance FROM accounts WHERE id=1  --> reads 1000
T2: UPDATE balance = 500 WHERE id=1; COMMIT
T1: SELECT balance FROM accounts WHERE id=1  --> reads 500 (different!)

Phantom Read: Running the same query twice in the same transaction and getting different rows because another committed transaction inserted or deleted rows.

T1: SELECT COUNT(*) FROM orders WHERE amount > 1000  --> returns 5
T2: INSERT INTO orders VALUES (6, 1500); COMMIT
T1: SELECT COUNT(*) FROM orders WHERE amount > 1000  --> returns 6 (phantom!)

---

Q8. What are the four SQL isolation levels? What anomalies does each prevent? Medium

Isolation 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

SQL syntax:

-- Set isolation level for session
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- ... transaction ...
COMMIT;

Default isolation levels:

• MySQL InnoDB: REPEATABLE READ
• PostgreSQL: READ COMMITTED
• SQL Server: READ COMMITTED
• Oracle: READ COMMITTED

---

Q9. How does READ COMMITTED isolation work internally? Medium

READ COMMITTED prevents dirty reads by ensuring each query within a transaction sees only committed data at the time the query executes.

Implementation (PostgreSQL MVCC): Each row version has a transaction ID that created it. When a query runs, it sees only row versions committed before the query started.

T1 started at timestamp 100.
T2 (started at 110) updates row A and commits at 115.
T1 runs SELECT at 120:
  With READ COMMITTED: T1 sees T2's update (T2 committed at 115 before query at 120). ✓
  Non-repeatable read: if T1 re-runs SELECT, sees same committed value. ✓ at this point.
  But T3 commits update at 125, T1 re-runs at 130: sees T3's update too. NON-REPEATABLE READ possible.

Implementation (locking approach): Acquire shared locks before reading, release IMMEDIATELY after reading (not held until transaction end). This prevents reading in-progress (uncommitted) writes but allows other committed writes between reads.

---

Q10. How does REPEATABLE READ isolation work? Medium

REPEATABLE READ ensures that if a transaction reads a row, re-reading the same row in the same transaction gives the same result. Other committed transactions' updates to EXISTING rows are not visible.

Implementation (MVCC -- PostgreSQL, InnoDB): Each transaction uses a snapshot of the database taken at the start of the transaction. Reads always return the snapshot version, not newer committed versions.

T1 starts at timestamp 100. Snapshot: sees all data committed before 100.
T2 updates row A to 500 at timestamp 110. Commits.
T1 re-reads row A: still sees the snapshot value from timestamp 100. NO non-repeatable read.

But phantom reads: T2 inserts new row B at timestamp 110.
T1 re-runs range query: with MVCC, T1 still does NOT see row B (post-snapshot).
MySQL InnoDB REPEATABLE READ: uses gap locks to prevent phantoms too (stricter than SQL standard requires for this level).
PostgreSQL REPEATABLE READ: phantom reads are also prevented (full snapshot isolation).

---

Q11. What is Serializable isolation? How is it enforced? Hard

Serializable isolation is the strongest level. It guarantees the outcome of concurrent transactions is as if they were executed one at a time in some serial order.

Enforcement approaches:

1. Two-Phase Locking (2PL): Acquire all locks before releasing any. Prevents all three anomalies (with range locks for phantoms). High contention.

2. Serializable Snapshot Isolation (SSI): Used by PostgreSQL (since 9.1) and SQL Server (SNAPSHOT ISOLATION). Tracks read/write dependencies between transactions. If a dangerous pattern (read-write cycle) is detected, one transaction is aborted.

3. Timestamp ordering: Each transaction gets a timestamp. Operations execute in timestamp order. Reads/writes that violate ordering cause rollback.

Performance trade-off:

READ UNCOMMITTED:  Fastest, least correct
READ COMMITTED:    Fast, used by most OLTP
REPEATABLE READ:   Moderate, MySQL default
SERIALIZABLE:      Slowest, highest correctness guarantee

When to use Serializable:

• Financial transactions (bank balance transfer).
• Inventory management (prevent overselling).
• Reservation systems (prevent double-booking).

---

Q12. What is a lost update problem? Medium

Lost Update: Two transactions read the same value, both modify it, and one write overwrites the other, effectively losing one transaction's update.

T1: read balance = 1000
T2: read balance = 1000
T1: write balance = 1000 + 500 = 1500  (deposit 500)
T2: write balance = 1000 - 200 = 800   (withdraw 200)  <- OVERWRITES T1's write!
Final: 800  Expected: 1000 + 500 - 200 = 1300

Prevention:

• REPEATABLE READ or SERIALIZABLE with locking: T1 holds a write lock until commit, T2 blocks on read until T1 commits.
• Optimistic locking: version number check on write (UPDATE ... WHERE version=old_version AND...; if 0 rows updated, retry).
• SELECT ... FOR UPDATE: Explicit read lock that prevents other transactions from modifying the locked row.

-- Pessimistic lock approach
BEGIN;
SELECT balance FROM accounts WHERE id=1 FOR UPDATE;  -- Exclusive lock
UPDATE accounts SET balance = balance + 500 WHERE id=1;
COMMIT;

---

Q13. What is the difference between optimistic and pessimistic concurrency control? Medium

Aspect	Pessimistic	Optimistic
Assumption	Conflicts are frequent	Conflicts are rare
Mechanism	Lock data before access	Proceed without lock; check at commit time
Conflict handling	Block until lock is released	Detect conflict at commit; abort and retry if conflict
Deadlock risk	Yes (multiple locks)	No
Best for	High-contention, write-heavy workloads	Read-heavy, low-contention workloads
SQL support	SELECT ... FOR UPDATE, LOCK TABLE	Optimistic locking via version columns

Optimistic locking in SQL:

-- Add a version column
ALTER TABLE products ADD COLUMN version INT DEFAULT 0;

-- Optimistic update: only succeeds if no one else changed it
UPDATE products
SET price = 999, version = version + 1
WHERE id = 42 AND version = 3;  -- version=3 was read value

-- Check if update affected rows
-- If 0 rows: conflict detected, retry

---

Q14. What is a savepoint? How is it different from a transaction? Easy

A savepoint is a named marker within a transaction to which you can partially roll back, without rolling back the entire transaction.

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- Debit
SAVEPOINT debit_done;

UPDATE accounts SET balance = balance + 500 WHERE id = 2;  -- Credit
-- Credit failed (account 2 closed)
ROLLBACK TO SAVEPOINT debit_done;  -- Undo only the credit, keep the debit

-- Handle the error: maybe credit a different account
UPDATE accounts SET balance = balance + 500 WHERE id = 3;
COMMIT;  -- Final state: account 1 debited, account 3 credited

Use case: Complex multi-step transactions where partial failure should only undo the failed step, not the entire transaction. Used in application frameworks (Spring's nested transactions).

Note: ROLLBACK TO SAVEPOINT does NOT end the transaction. The transaction is still open and can be committed or rolled back further.

---

Concurrency Control

Q15. What is Two-Phase Locking (2PL)? Medium

Two-Phase Locking is a concurrency control protocol that guarantees serializability.

Two phases:

1. Growing phase: Transaction can acquire locks but cannot release any.
2. Shrinking phase: Transaction can release locks but cannot acquire any new ones.

Transaction timeline:
|--Growing phase--|--Shrinking phase--|
  Lock A            Release A
  Lock B            Release B
  Lock C            Release C
         ^ Lock point (max locks held)

Why it works: By the time a transaction releases any lock (enters shrinking phase), it cannot acquire more locks that could create a new dependency. The serial order is equivalent to the sequence of lock points.

Lock types:

• Shared lock (S): For reading. Multiple transactions can hold S-locks on the same item.
• Exclusive lock (X): For writing. No other lock can coexist with an X-lock.

	S-lock held by another	X-lock held by another
Request S-lock	Compatible (grant)	Incompatible (wait)
Request X-lock	Incompatible (wait)	Incompatible (wait)

---

Q16. What are the variants of 2PL? Hard

Variant	Description	Properties
Basic 2PL	Growing then shrinking phase. Locks released before transaction ends.	Deadlock possible. Cascading rollbacks possible.
Strict 2PL	All X-locks (write locks) held until transaction commits or aborts. S-locks released during shrinking.	No cascading rollbacks. Deadlock still possible.
Rigorous 2PL	ALL locks held until transaction commits or aborts.	Simplest recovery. No cascading rollbacks. Deadlock possible.
Conservative 2PL	Acquire ALL needed locks before transaction starts.	No deadlock. But may not know all needed locks in advance.

Most common in practice: Strict 2PL (default in most RDBMS including MySQL InnoDB, PostgreSQL).

Cascading rollback problem (Basic 2PL): If T1 releases a lock mid-transaction and T2 reads that data, then T1 aborts, T2 must also abort (it read "dirty" data from T1). This cascade can abort many transactions.

Strict 2PL fix: Hold X-locks until commit. T2 cannot read T1's uncommitted writes (X-lock blocks T2 from reading).

---

Q17. What is MVCC (Multi-Version Concurrency Control)? Hard

MVCC avoids read-write conflicts by maintaining multiple versions of data rows. Readers never block writers and writers never block readers.

Mechanism: Each row has a version history:

Row 5, value history:
  version 1: balance=1000, txn_id=50,  visible to txn > 50 and not aborted
  version 2: balance=1500, txn_id=100, visible to txn > 100 and not aborted
  version 3: balance=800,  txn_id=150, visible to txn > 150 and not aborted (current)

When transaction T (with start timestamp 120) reads row 5:

• T sees version 2 (txn_id=100 < 120, committed before T started).
• T does not see version 3 (txn_id=150 > 120, started after T).

Key properties:

• Readers never blocked (they read their consistent snapshot).
• Writers create new versions (don't overwrite existing ones).
• Old versions garbage collected when no active transaction needs them (VACUUM in PostgreSQL).

MVCC vs 2PL:

	2PL	MVCC
Read-write conflict	Reader blocks writer or vice versa	No blocking
Performance	Lower under read-heavy load	Better for mixed read-write
Implementation	Lock manager	Version store + transaction IDs
Deadlock risk	Yes	Reduced (no read-write locks)

Used by: PostgreSQL, MySQL InnoDB, Oracle, CockroachDB.

---

Q18. What are intent locks? Hard

Intent locks are hierarchy-level locks used when a DBMS supports multi-level locking (table-level, page-level, row-level).

Problem without intent locks: Transaction T1 holds a row-level X-lock on row 5. Transaction T2 tries to lock the entire table. T2 must check ALL rows to see if any are locked. With millions of rows, this is O(n) overhead.

Solution: Intent locks on parent nodes When a transaction acquires a row-level lock, it also places an intent lock on the table (and page).

Lock Mode	Meaning
IS (Intent Shared)	Transaction intends to acquire S-lock on a lower level
IX (Intent Exclusive)	Transaction intends to acquire X-lock on a lower level
SIX (Shared + Intent Exclusive)	Current level is S-locked, but transaction may X-lock lower levels

Compatibility matrix:

	IS	IX	S	SIX	X
IS	Yes	Yes	Yes	Yes	No
IX	Yes	Yes	No	No	No
S	Yes	No	Yes	No	No
SIX	Yes	No	No	No	No
X	No	No	No	No	No

T2 wanting table X-lock sees IX on table from T1. Incompatible. T2 waits. No need to scan rows.

---

Q19. What is a deadlock in database transactions? How does MySQL detect it? Medium

Deadlock: Two or more transactions are each waiting for a lock held by the other, forming a circular wait.

T1: Lock(A). Wait for Lock(B).
T2: Lock(B). Wait for Lock(A).
Both wait forever.

MySQL InnoDB deadlock detection: InnoDB maintains a wait-for graph (WFG) where an edge T1->T2 means T1 is waiting for a lock held by T2. After each lock wait, InnoDB runs cycle detection on the WFG. If a cycle is found:

• InnoDB selects the transaction with the smallest "weight" (fewest rows changed, smallest undo log) as the victim.
• Victim transaction is rolled back with an error.
• The other transaction(s) proceed.

-- Session 1:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- locks row 1
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- waits for row 2

-- Session 2 (concurrent):
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- locks row 2
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- waits for row 1 -> DEADLOCK

-- InnoDB detects cycle. Rolls back Session 2:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Application response: Always catch deadlock errors and retry the transaction (with optional backoff).

---

Q20. What is lock granularity? What are the trade-offs? Medium

Lock granularity refers to the size of the data unit being locked.

Granularity	Lock unit	Concurrency	Overhead
Table-level	Entire table	Low (only one tx per table at a time)	Low
Page-level	Disk page (4-16KB)	Medium	Medium
Row-level	Individual row	High	High (many lock entries)
Field-level	Single column	Highest	Very high

Trade-off:

• Coarse-grained (table lock): Fast lock acquisition, but blocks many concurrent users. Used for DDL operations (ALTER TABLE), batch operations.
• Fine-grained (row lock): More concurrency, but expensive to manage thousands of locks. Used for typical OLTP (MySQL InnoDB, PostgreSQL default).

MySQL InnoDB default: Row-level locking for DML (INSERT, UPDATE, DELETE). Table-level for DDL.

Lock escalation: Some DBMSes (SQL Server) automatically escalate many row-level locks to a table-level lock to reduce lock manager overhead (at the cost of concurrency).

---

Q21. What is a phantom in the context of database transactions? Medium

A phantom occurs when a transaction re-runs a range query and the result set changes because another committed transaction inserted or deleted rows matching the range.

-- T1 counts premium users
BEGIN;
SELECT COUNT(*) FROM users WHERE plan = 'premium';  -- Returns 50

-- T2 inserts a new premium user and commits
INSERT INTO users VALUES (51, 'David', 'premium'); COMMIT;

-- T1 re-runs the same query
SELECT COUNT(*) FROM users WHERE plan = 'premium';  -- Returns 51 (PHANTOM!)
COMMIT;

Why REPEATABLE READ does not prevent phantoms (in theory): REPEATABLE READ ensures existing rows are not modified between reads. But new rows inserted by other transactions can appear in range queries (they did not exist to lock).

How SERIALIZABLE prevents phantoms:

• Range locks (predicate locking): Lock all rows that match a predicate (not just existing rows, but the "space" where new rows could appear). Expensive.
• Gap locks (MySQL InnoDB): Lock gaps between index keys. Prevents INSERT into the gap.
• SSI (PostgreSQL): Tracks read-write dependencies; if a phantom would affect the result, one transaction is aborted.

---

Recovery and Advanced

Q22. What is Write-Ahead Logging (WAL)? Medium

Write-Ahead Logging (WAL) is the standard technique for ensuring atomicity and durability in databases.

Rule: Before any data page is written to disk, the corresponding log record (describing the change) must be flushed to disk first.

Why WAL works:

Sequence of events during a transaction:
1. T1 begins
2. T1 modifies page P (in buffer pool, not yet on disk)
3. Log record for modification flushed to disk (WAL rule: log before data)
4. T1 commits -> commit log record flushed to disk
5. DB acknowledges commit to application
6. Data page P is written to disk (lazy, may happen later)

On crash before step 6:
  Log has commit record. On recovery, REDO the modification (replay log).
  Page P is brought up to date.

On crash between step 2 and step 4:
  Log has no commit record. On recovery, UNDO the modification (reverse log).
  Page P is rolled back to pre-T1 state.

WAL guarantees:

• Atomicity: Incomplete transactions (no commit record in log) are undone.
• Durability: Committed transactions (commit record in log) are redone if page not yet written.

---

Q23. What is the difference between undo log and redo log? Medium

	Undo Log	Redo Log
Purpose	Roll back uncommitted changes	Replay committed changes that did not make it to disk
Content	Old values before modification	New values after modification
Used for	Transaction rollback, crash recovery of uncommitted tx	Crash recovery of committed tx (if data page not yet on disk)
When written	Before making a change	Before (or alongside) making a change
Example	"Row 5, balance was 1000"	"Row 5, balance is now 1500, txn_id=100, committed"

MySQL InnoDB:

• Undo log: Stored in the system tablespace or undo tablespaces. Used for ROLLBACK and for MVCC (provides old row versions to readers).
• Redo log: InnoDB's WAL. Files ib_logfile0, ib_logfile1. Circular buffer. Durability for committed writes.

Recovery algorithm (ARIES):

Phase 1 (Analysis): Scan log from last checkpoint to find all active transactions at crash.
Phase 2 (REDO): Replay ALL logged operations (committed or not) from checkpoint to crash.
Phase 3 (UNDO): Undo changes from all transactions that had no commit record.

---

Q24. What is a checkpoint in database recovery? Medium

A checkpoint is a point in the transaction log where the DBMS guarantees that all modifications made before the checkpoint are already written to disk (persistent).

Why checkpoints matter: Without checkpoints, on crash, the DBMS must redo every log record since the beginning of time. With checkpoints, recovery only needs to process log records after the last checkpoint.

Fuzzy checkpoint (PostgreSQL, InnoDB approach):

1. Write BEGIN CHECKPOINT to log.
2. Flush all dirty pages to disk (background process, may take time).
3. Write END CHECKPOINT to log with list of all dirty pages at checkpoint start.
4. On recovery: only need to redo from BEGIN CHECKPOINT.

Sharp checkpoint (simple approach):

Pause all transactions.
Flush ALL dirty pages to disk.
Write checkpoint record.
Resume transactions.

Fuzzy checkpoint preferred (no pause). Used in production databases.

---

Q25. What is two-phase commit (2PC) in distributed databases? Hard

Two-Phase Commit (2PC) is a distributed transaction protocol that ensures all nodes in a distributed transaction either all commit or all abort (atomicity across multiple database nodes).

Roles: Coordinator (initiates) and Participants (actual database nodes).

Phase 1: Prepare (Voting phase)

Coordinator: "Are you ready to commit?" (PREPARE message to all participants)
Each participant:
  - Write the transaction data to stable storage (WAL).
  - If ready: reply VOTE-YES and prepare to commit (but don't commit yet).
  - If not ready: reply VOTE-NO and unilaterally abort.

Phase 2: Commit/Abort (Decision phase)

If ALL voted YES:
  Coordinator: "COMMIT" (to all participants)
  Each participant: apply changes, release locks, acknowledge.

If ANY voted NO:
  Coordinator: "ABORT" (to all participants)
  Each participant: rollback, release locks.

Problem: Blocking failure. If coordinator crashes after Phase 1 but before Phase 2, participants are stuck in "prepared" state (cannot commit or abort without coordinator). They hold locks indefinitely.

Modern alternative: Three-phase commit (3PC) reduces blocking. More commonly: Saga pattern or distributed sagas replace 2PC in microservices to avoid the blocking problem.

---

Q26. What is MVCC garbage collection? Why is VACUUM needed in PostgreSQL? Hard

MVCC creates old row versions every time a row is updated or deleted (to support snapshot isolation for other transactions). These old versions accumulate and consume disk space.

Garbage collection: Old row versions are safe to delete when no active transaction can ever read them (all transactions with start timestamps before the old version's creation timestamp have committed or aborted).

PostgreSQL VACUUM: PostgreSQL does not overwrite row versions in-place. Old versions (called "dead tuples") remain in the table. VACUUM marks dead tuples as free space (available for new rows).

VACUUM ANALYZE table_name;       -- Standard vacuum + stats update
VACUUM FULL table_name;          -- Full rewrite (locks table, reclaims more space, slower)
AUTOVACUUM (daemon):             -- Runs automatically based on dead tuple count threshold

Transaction ID wraparound (critical!): PostgreSQL uses 32-bit transaction IDs. After ~2 billion transactions, IDs wrap around. Old data could appear "in the future." VACUUM FREEZE marks old rows as visible to all transactions (removes dependency on transaction ID). Failing to run freeze can cause transaction ID exhaustion -- a serious production issue.

---

Q27. What is savepoint and nested transaction support? Medium

Savepoints (covered in Q14) provide partial rollback within a transaction. True nested transactions go further: a sub-transaction is an independent unit that can commit or rollback without affecting the parent.

SQL standard savepoints (most DBMSes):

BEGIN;
-- Outer transaction
INSERT INTO log VALUES ('started');
SAVEPOINT sp1;
INSERT INTO orders VALUES (...);  -- May fail
-- On failure:
ROLLBACK TO sp1;  -- Undo only the order insert, log entry survives
-- On success:
RELEASE SAVEPOINT sp1;
COMMIT;

True nested transactions (SQL Server, some ORMs via subtransactions):

• Inner transaction has its own BEGIN/COMMIT.
• Inner COMMIT: changes visible to outer transaction but not yet to other transactions.
• Inner ROLLBACK: only inner transaction's changes undone.
• Outer ROLLBACK: also rolls back committed inner transactions.

Spring's @Transactional(propagation=REQUIRES_NEW): Creates a truly independent new transaction (suspends outer). Changes in the new transaction committed independently. Used for audit logging that must persist even if the main transaction rolls back.

---

Q28. Design a transactions system for a banking transfer. What isolation level and locks would you use? Hard

Requirements:

• Debit source account, credit destination account.
• Both must succeed or both must fail.
• No double-spending (lost updates).
• No reading partial states.

Design:

-- Use SERIALIZABLE or REPEATABLE READ with explicit locking

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;

-- Lock both rows in a consistent order to prevent deadlock
-- (always lock lower account_id first)
SELECT balance FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- FOR UPDATE: acquire exclusive row locks. Other transactions block.

-- Check sufficient balance
-- (application fetches balance from above SELECT)
IF balance_of_1 < 500:
    ROLLBACK; -- Insufficient funds
END IF;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

INSERT INTO transfer_log (from_id, to_id, amount, timestamp)
VALUES (1, 2, 500, NOW());

COMMIT;

Key design decisions:

1. SERIALIZABLE or REPEATABLE READ + FOR UPDATE: Prevents lost updates and non-repeatable reads.
2. Lock ordering (order by id): Always lock accounts in the same order across all transactions to prevent deadlock.
3. Explicit lock with FOR UPDATE: Ensures balance check and debit are atomic.
4. Retry on deadlock error: Application retries the transfer with backoff.
5. Audit log inside transaction: Transfer log committed atomically with the balance changes.

At scale: Use optimistic locking with a version column for lower-contention scenarios (most accounts are not heavily contended). Retry on version mismatch instead of locking.

---

FAQ

Q: Is ACID required for all databases? No. NoSQL databases often sacrifice some ACID properties for performance and scalability (CAP theorem). MongoDB, by default, provides atomic single-document operations but not multi-document transactions (though it added multi-document ACID transactions in version 4.0). Cassandra provides eventual consistency by default.

Q: What is the difference between ACID and BASE? ACID (Atomicity, Consistency, Isolation, Durability) is the traditional relational DB model. BASE (Basically Available, Soft state, Eventually consistent) is the NoSQL alternative that accepts temporary inconsistency for higher availability and partition tolerance.

Q: Can two SELECT statements form a transaction? Yes. Even SELECT statements in a transaction are subject to isolation levels. Under SERIALIZABLE, two SELECTs in the same transaction are guaranteed to see consistent snapshots.

---

Related PapersAdda guides:

• DBMS Interview Questions 2026
• DBMS Normalization Questions 2026
• DBMS Indexing Questions 2026
• DBMS Joins Interview Questions 2026
• System Design Interview 2026