Transactions, Concurrency & Recovery Interview Questions - DBMS

Transactions ensure reliable and atomic operations in a database, even with multiple users accessing data concurrently. Concurrency control techniques prevent conflicts and maintain consistency, while recovery methods help restore the database after failures. Understanding these mechanisms is essential for building robust DBMS and tackling complex interview questions on the topic.

1. What is a transaction in DBMS? What are the properties of a transaction?

A transaction in DBMS is a sequence of one or more SQL operations executed as a single unit of work. A transaction ensures data integrity, consistency, and isolation, and it guarantees that the database reaches a valid state, regardless of errors or system failures.

Properties of a transaction (ACID Properties):

• Atomicity: All operations within the transaction are completed successfully, or none are applied (i.e., the transaction is atomic).
• Consistency: The transaction brings the database from one valid state to another valid state.
• Isolation: The operations of one transaction are isolated from others; intermediate results are not visible to other transactions.
• Durability: Once a transaction is committed, its effects are permanent, even in the event of a system crash.

2. What is a deadlock in DBMS? How can it be prevented?

A deadlock occurs when two or more transactions are blocked because each transaction is waiting for the other to release resources. This results in a situation where none of the transactions can proceed.

Prevention techniques:

• Lock ordering: Ensuring that all transactions acquire locks in the same predefined order.
• Timeouts: Automatically rolling back transactions that have been waiting too long for resources.
• Deadlock detection: Periodically checking for deadlocks and aborting one of the transactions to break the cycle.

3. What are the different types of database locks?

There are several types of locks in DBMS to ensure data consistency when multiple transactions are involved:

• Shared Lock (S Lock): Allows multiple transactions to read a resource but prevents modification.
• Exclusive Lock (X Lock): Prevents any other transaction from reading or modifying the locked resource.
• Intent Lock: Signals that a transaction intends to lock a resource.
• Update Lock (U Lock): Used when a transaction intends to update a resource.

4. How does DBMS handle concurrency control?

Concurrency control ensures that database transactions are executed in a way that prevents conflicts, such as data inconsistency, when multiple transactions are executed simultaneously. DBMS uses the following techniques:

• Locking: Transactions acquire locks (shared or exclusive) on the data to prevent other transactions from modifying it while one transaction is in progress.
• Timestamp Ordering: Assigns a unique timestamp to each transaction and uses these timestamps to determine the order in which transactions should be executed.
• Optimistic Concurrency Control: Transactions are executed without locking data, but before committing, the system checks whether there were conflicts with other transactions.
• Two-Phase Locking: Involves two phases, growing (acquiring locks) and shrinking (releasing locks), to avoid deadlocks and ensure serializability.

5. What is a transaction log in DBMS?

A transaction log is a record that keeps track of all transactions executed on a database. It ensures that changes made by transactions are saved, and in case of a system failure, the log can be used to recover the database to its last consistent state. The transaction log contains:

• The details of each transaction (e.g., start, commit, rollback).
• Information about data modifications (insertions, updates, deletions).
• Details about the data before and after the change.

6. How can a transaction be serializable but still result in inconsistent database state?

Serializability ensures that the execution order of transactions is equivalent to some serial schedule, but it doesn’t guarantee correctness unless each transaction preserves database consistency individually. If a transaction is flawed (contains incorrect logic or violates integrity constraints), it can produce inconsistent results even if the schedule is serializable.
Example:

• T1 deducts balance from one account but forgets to credit another.
• T2 runs afterward in a serializable schedule, but database state is already invalid.

Key Point: Serializability ≠ correctness, correctness also depends on the integrity-preserving nature of transactions.

7. Explain how phantom reads can occur even under Repeatable Read isolation.

In Repeatable Read, the same row read twice will return the same values, preventing non-repeatable reads. However, phantom reads occur when a query’s result set changes because new rows satisfy the query’s WHERE condition in later executions.
Example:

1. Transaction T1 executes:

   SELECT * FROM Orders WHERE amount > 1000;

2. T2 inserts a new order with amount = 2000 and commits.
3. T1 executes the same query again and sees the new row — a “phantom” tuple.

Reason: Repeatable Read locks only existing rows, not the predicate space.
Prevention: Use Serializable isolation or predicate locking.

8. Two transactions deadlock on a read–write conflict without any circular waits. How is that possible?

Classic deadlock theory assumes wait-for cycles. However, in some DBMS implementations with lock upgrades, deadlocks can occur without an explicit cycle.

Example:

• T1 holds a read lock on record A, wants to upgrade to write lock.
• T2 holds a read lock on same record, also wants to upgrade.
• Both wait for the other’s read lock to be released. neither releases -> upgrade deadlock.

Resolution: DBMS detects via lock upgrade deadlock detection and aborts one transaction.

9. What is the difference between Recoverable, Cascade-less, and Strict schedules, and why is “Strict” preferred?

• Recoverable Schedule: If T2 reads data written by T1, T1 commits before T2 commits. Prevents cascading aborts, but undo may still be required.
• Cascade-less Schedule: Every read happens only after the transaction that wrote the data commits -> prevents cascading aborts, still may require undo if crash occurs before commit.
• Strict Schedule: No read or write allowed on data written by uncommitted transactions -> guarantees easy recovery using only redo after crash.

Preferred because Strict schedules prevent both cascading aborts and the need to undo uncommitted changes after a crash, simplifies recovery.

10. How does Write-Ahead Logging (WAL) ensure atomicity and durability during recovery?

WAL’s rule: Log records must be written to stable storage before the corresponding data page is written to disk.

• Atomicity: If a crash occurs, the log contains enough info to undo incomplete transactions (before-images).
• Durability: After commit, redo log records ensure all committed changes are reapplied even if data pages weren’t yet flushed.
  Process:

1. Before modifying a page -> write undo and redo info to log.
2. On commit -> flush log to disk.
3. On recovery -> use redo for committed transactions, undo for incomplete ones.

This guarantees no committed transaction is lost and no partial transaction survives.

11. Can Two-Phase Locking (2PL) produce a non-serializable schedule?

• Basic 2PL: Can produce serializable schedules if all transactions obey it.
• However, if a transaction releases locks early and acquires new ones (violating 2PL), or if a system mixes locking with timestamp ordering, anomalies can occur.
• Cascading aborts are possible with 2PL unless Strict 2PL is used, which keeps all write locks until commit.

Pure 2PL = serializable, but practical deviations or improper lock management can break serializability.

12. How can ARIES recovery handle “loser” transactions that never wrote to disk?

In ARIES (Algorithm for Recovery and Isolation Exploiting Semantics):

• “Loser” transactions are those active at crash time.
• Even if their dirty pages were never flushed to disk, ARIES:
  1. Analysis Phase: Finds them from transaction table.
  2. Undo Phase: Writes compensation log records (CLRs) even if no actual data undo is required, to maintain idempotence in future recovery attempts.

This ensures recovery is repeatable and consistent, avoiding partial undos.

13. How does Multiversion Concurrency Control (MVCC) avoid read-write conflicts without locks?

MVCC keeps multiple versions of each record, tagged with timestamps.

• A reader sees the latest committed version as of its transaction start time -> no blocking on writes.
• Writers create a new version without overwriting existing ones -> no blocking on reads.

Example:

• T1 starts, sees version V1.
• T2 updates -> creates version V2, commits.
• T1 still reads V1 until it commits.

Trade-off: Needs garbage collection of old versions.

14. Explain a situation where checkpointing reduces recovery time but increases transaction latency.

• Checkpointing flushes dirty pages and logs periodically to limit recovery work.
• During checkpoint:

• I/O bursts occur (pages written to disk).
• This can delay ongoing transactions due to buffer contention and log writes being paused.

So, while it speeds up post-crash recovery, it can momentarily slow active transactions, a trade-off DBAs must balance.

15. Why is “force” policy incompatible with “steal” policy in recovery design?

• Force: At commit, all modified pages of a transaction are forced to disk -> no redo needed.
• Steal: Buffer manager can write dirty pages of uncommitted transactions to disk -> undo needed.

If both are applied:

• At commit, no redo is needed (due to force), but undo may still be required for stolen pages of aborted transactions — complexity increases without benefit.
• In practice:

• Force + No-Steal: Simple, but high commit cost.
• No-Force + Steal: Common in ARIES -> needs both redo and undo.

16. Explain the concept of Strict Two-Phase Locking (Strict 2PL) and why it is essential for recoverability and serializability.

Strict 2PL is a refinement of the basic Two-Phase Locking protocol. It requires that:

• All exclusive (write) locks held by a transaction are released only after the transaction commits or aborts.
• Shared (read) locks may be released earlier but often kept until commit in strict 2PL.

This protocol ensures:

• Serializability: Because the lock acquisition and release ordering ensures no conflicting operations interleave improperly.
• Recoverability: By holding write locks until commit, it prevents other transactions from reading uncommitted data, thus avoiding cascading aborts.
• It guarantees strict schedules, where dirty reads are impossible, enabling simple recovery by undoing uncommitted changes after a crash.

Strict 2PL is widely used in practice as it balances concurrency with recoverability guarantees.

17. Describe the role of the “Transaction Table” and “Dirty Page Table” in ARIES recovery. How do these help during crash recovery?

In ARIES recovery, two main in-memory data structures persist through checkpoints:

• Transaction Table (TT): Keeps track of active transactions at the time of checkpoint, including their last log sequence number (LSN) and status (running, committed, aborted). Helps identify “loser” transactions at crash time that need to be undone.
• Dirty Page Table (DPT): Records pages that have been modified (dirtied) but not yet flushed to disk, along with the LSN of the first log record that caused the page to become dirty (recLSN). This allows recovery to know which pages need redo operations.

During recovery:

• The Analysis phase reconstructs these tables from the log to find out what transactions were active and which pages were dirty.
• The Redo phase uses the DPT to efficiently replay changes only for affected pages, avoiding unnecessary work.
• The Undo phase consults the TT to undo effects of uncommitted transactions.

Together, TT and DPT help ARIES minimize recovery time and correctly restore consistent state.

18. Explain timestamp ordering protocol in concurrency control. How does it handle conflicting operations?

Timestamp ordering assigns a unique, monotonically increasing timestamp to each transaction at its start. The protocol enforces execution order consistent with these timestamps to guarantee serializability.

For each data item, two timestamps are maintained:

• Read Timestamp (RTS): The largest timestamp of any transaction that successfully read the item.
• Write Timestamp (WTS): The largest timestamp of any transaction that successfully wrote the item.

When a transaction T wants to perform:

• Read(X): Allowed only if T’s timestamp > WTS(X), else T is aborted and restarted.
• Write(X): Allowed only if T’s timestamp > RTS(X) and WTS(X), else aborted.

This prevents conflicting operations that would violate the timestamp order, ensuring a serializable schedule consistent with timestamp ordering. Transactions that violate order are aborted to maintain correctness.

19. How do deadlock detection and prevention differ? Give examples of algorithms for each in DBMS.

• Deadlock Detection: System allows deadlocks to occur and periodically checks for cycles in the wait-for graph representing transactions waiting on locks. Upon detection, it selects and aborts victim transactions to break deadlocks.

Example: Wait-for graph cycle detection using DFS.

• Deadlock Prevention: System avoids deadlocks by imposing constraints on resource allocation to prevent circular waits.

Wait-Die scheme: Older transactions wait, younger abort.
Wound-Wait scheme: Older aborts younger, younger waits.

Detection is reactive, prevention is proactive. Detection allows more concurrency but requires overhead for cycle detection; prevention simplifies logic but may abort more transactions unnecessarily.

20. What is cascading rollback? How do concurrency control protocols avoid it?

Cascading rollback occurs when a transaction T1 aborts and the abort forces other transactions T2, T3,... that read uncommitted data from T1 to abort as well, potentially causing a chain reaction.

Avoidance methods:

• Use Strict 2PL so that no transaction can read data from uncommitted transactions (avoids dirty reads).
• Use Cascadeless schedules where a transaction reads only committed data.
• Multiversion concurrency control (MVCC) also avoids cascading rollbacks by allowing readers to see stable versions.

Avoiding cascading rollback improves system stability and simplifies recovery.

21. Compare Undo Logging and Redo Logging in transaction recovery. What are the advantages and disadvantages of each?

Undo Logging:

• Logs the before-image of data before changes are made.
• On failure, uncommitted changes are undone using this log.
• Requires force policy at commit to ensure durability.
• Advantage: Easy to undo changes on abort.
• Disadvantage: Recovery may be slower; must undo before redo.

Redo Logging:

• Logs the after-image of data after changes are made.
• Used to reapply committed changes after a crash.
• Allows no-force policy at commit; data pages can be flushed anytime.
• Advantage: Efficient recovery for committed transactions.
• Disadvantage: Requires additional undo mechanism if combined with steal policy.

ARIES combines both undo and redo logging to handle all cases effectively.

22. Explain the concept of “Commit Dependencies” and how they affect transaction commit protocols.

Commit dependencies arise when a transaction T2 depends on the outcome of another transaction T1 that has not yet committed, usually because T2 read data written by T1.

In this case:

• T2 cannot commit until T1 commits to ensure recoverability.
• If T1 aborts, T2 must also abort (cascading aborts).

Protocols handle this by:

• Delaying commit confirmation until dependent transactions commit (e.g., Strict 2PL avoids this).
• Using commit dependencies to ensure the correct order of commit messages and maintain consistency.

Proper management avoids write skew and inconsistent commit states.

23. What are Compensation Log Records (CLRs) in ARIES, and why are they critical?

CLRs are special log records written during the undo phase of recovery in ARIES to record that an undo operation has been performed for a specific action.

Purpose:

• Idempotency: Ensures if recovery is interrupted and restarted, the undo of the same action isn’t repeated.
• Redo Capability: CLRs themselves are redoable, enabling the recovery process to be repeated safely.

CLRs allow ARIES to perform partial undos without losing progress, ensuring reliable crash recovery even if crashes occur during recovery.

24. How does Snapshot Isolation prevent dirty reads and non-repeatable reads? Can it cause anomalies?

Snapshot Isolation (SI) works by each transaction reading from a consistent snapshot of the database at its start time. This ensures:

• No dirty reads: Because readers see only committed data as of their start.
• No non-repeatable reads: Because repeated reads access the same snapshot.

However, SI can cause write skew anomalies — a subtle form of inconsistency due to concurrent transactions modifying disjoint sets of data that violate integrity constraints when combined. Hence, SI provides stronger isolation than Read Committed but is not fully serializable unless enhanced with conflict detection.

25. Explain the purpose of the checkpoint in database recovery and the difference between fuzzy and non-fuzzy checkpoints.

Checkpointing reduces recovery time by flushing dirty pages and recording a consistent point in the log, so the recovery process starts from this point instead of the beginning.

Non-fuzzy checkpoint:

• Entire checkpoint process is atomic and blocks transactions until it completes.
• Guarantees consistency but causes system stalls.

Fuzzy checkpoint:

• Transactions continue while checkpoint proceeds.
• Logs the start and end of checkpoint, allowing recovery to handle partial checkpoints.
• Improves system availability but recovery process becomes more complex.

Fuzzy checkpoints are more common in modern systems due to lower overhead during normal operation.