ACID Properties in DBMS MCQ 60 Practice Tests With Answers (2026)
PerfectNotesUpdated: April 2026~15 min practice 60 Questions · 3 Levels 60 Questions · 3 Levels
ACID Properties — Atomicity, Consistency, Isolation, and Durability — are the four foundational guarantees that every reliable database transaction must satisfy. These 60 carefully structured ACID MCQs take you from core definitions through advanced topics: isolation levels, Write-Ahead Logging, MVCC internals, deadlock prevention, Two-Phase Commit, and the ARIES recovery algorithm.
Questions are organized into three progressive levels of 20 questions each: Basics (covering core ACID definitions, bank transfer scenarios, commit/rollback, and locking fundamentals), Concepts (covering dirty reads, phantom reads, Two-Phase Locking, MVCC, cascading rollbacks, and savepoints), and Advanced (covering Two-Phase Commit, ARIES crash recovery, Snapshot Isolation, Write Skew, buffer pool policies, Saga Pattern, and Wait-Die deadlock prevention).
Use Study Mode to build conceptual understanding question-by-question, or use Exam Mode to simulate GATE, university finals, or job interview conditions with full scoring and detailed explanations revealed upon submission.
Contents
- 1.Basics (20 Questions)ACID definitions · bank transfer scenarios · commit/rollback · locking basics
- 2.Concepts (20 Questions)dirty reads · phantom reads · Two-Phase Locking · MVCC · cascading rollbacks · savepoints
- 3.Advanced (20 Questions)2PC · ARIES · Snapshot Isolation · Write Skew · Saga Pattern · Wait-Die
- 4.Conclusionsummary · next steps · study tips
- 5.Key Takeawaysquick-fire bullet recap of essential facts
- 6.Quick Review Summaryconcept · definition · key fact table
- 7.FAQcommon questions answered
ACID Properties — Basics
1What does the 'A' in the ACID acronym stand for?
CorrectA: Atomicity
The ACID acronym stands for Atomicity, Consistency, Isolation, and Durability.
IncorrectA: Atomicity
The ACID acronym stands for Atomicity, Consistency, Isolation, and Durability.
2Which ACID property ensures a transaction is treated as a single, indivisible logical unit of work?
CorrectB: Atomicity
Atomicity treats every transaction as a single, indivisible unit — all succeeds or all rolls back.
IncorrectB: Atomicity
Atomicity treats every transaction as a single, indivisible unit — all succeeds or all rolls back.
3The principle that a database must move strictly from one valid state to another perfectly describes which property?
CorrectC: Consistency
Consistency ensures the database moves from one valid state to another, never violating any integrity rule.
IncorrectC: Consistency
Consistency ensures the database moves from one valid state to another, never violating any integrity rule.
4Which property guarantees that once a transaction commits, its changes survive subsequent power losses or system crashes?
CorrectD: Durability
Durability guarantees committed changes survive any subsequent crash or power failure.
IncorrectD: Durability
Durability guarantees committed changes survive any subsequent crash or power failure.
5What does "Isolation" in a database context fundamentally prevent?
CorrectC: Interference and data corruption between concurrent transactions
Isolation prevents concurrent transactions from interfering with each other.
IncorrectC: Interference and data corruption between concurrent transactions
Isolation prevents concurrent transactions from interfering with each other.
6If a bank transfer fails halfway through and the system automatically reverts all partial changes, which property is primarily being enforced?
CorrectB: Atomicity
Atomicity demands all partial changes are rolled back when a transaction fails midway.
IncorrectB: Atomicity
Atomicity demands all partial changes are rolled back when a transaction fails midway.
7Which subsystem of a Database Management System is primarily responsible for ensuring Durability?
CorrectA: The Recovery Manager utilizing Write-Ahead Logging
The Recovery Manager uses Write-Ahead Logging to guarantee durability after a crash.
IncorrectA: The Recovery Manager utilizing Write-Ahead Logging
The Recovery Manager uses Write-Ahead Logging to guarantee durability after a crash.
8A transaction attempts to insert a record that violates a primary key constraint. The database outright rejects it. Which property is preserved here?
CorrectD: Consistency
Rejecting a primary key constraint violation preserves Consistency — the database stays valid.
IncorrectD: Consistency
Rejecting a primary key constraint violation preserves Consistency — the database stays valid.
9When multiple users attempt to book the exact same airline seat at the exact same millisecond, which property ensures only one succeeds cleanly?
CorrectB: Isolation
Isolation (via locking or MVCC) ensures only one seat-booking transaction succeeds cleanly.
IncorrectB: Isolation
Isolation (via locking or MVCC) ensures only one seat-booking transaction succeeds cleanly.
10What happens to an atomic transaction if a hardware failure occurs exactly one millisecond before the final commit command is logged?
CorrectA: It is completely rolled back upon system restart
Atomicity demands full rollback if a crash occurs before commit; the undo log reverses all changes.
IncorrectA: It is completely rolled back upon system restart
Atomicity demands full rollback if a crash occurs before commit; the undo log reverses all changes.
11Which term describes a transaction that has completed its execution successfully and made its changes permanent?
CorrectD: Committed
A committed transaction has written its COMMIT record and its changes are now permanent.
IncorrectD: Committed
A committed transaction has written its COMMIT record and its changes are now permanent.
12What is the typical fundamental mechanism a relational database uses to enforce Isolation?
CorrectC: Concurrency control and locking mechanisms
Isolation is enforced by two-phase locking (2PL) or MVCC — serializing or versioning concurrent access.
IncorrectC: Concurrency control and locking mechanisms
Isolation is enforced by two-phase locking (2PL) or MVCC — serializing or versioning concurrent access.
13Which property assures that an interrupted transaction leaves absolutely no partial data updates in the underlying tables?
CorrectB: Atomicity
Atomicity guarantees no partial updates survive an interrupt; the undo log is replayed on restart.
IncorrectB: Atomicity
Atomicity guarantees no partial updates survive an interrupt; the undo log is replayed on restart.
14The phrase "All or Nothing" is universally used by database architects to describe which concept?
CorrectA: Atomicity
"All or Nothing" is the classic description of Atomicity.
IncorrectA: Atomicity
"All or Nothing" is the classic description of Atomicity.
15Which ACID property relies heavily on the rigid definition of primary keys, foreign keys, and check constraints by the database designer?
CorrectC: Consistency
Consistency depends on constraints (primary keys, foreign keys, check constraints) the designer defines.
IncorrectC: Consistency
Consistency depends on constraints (primary keys, foreign keys, check constraints) the designer defines.
16How does a database typically achieve Durability without suffering massive performance penalties from constant, random hard drive writes?
CorrectD: By sequentially appending changes to a rapid write-ahead transaction log
Sequential Write-Ahead Logging is fast (sequential I/O) and lets data pages flush lazily.
IncorrectD: By sequentially appending changes to a rapid write-ahead transaction log
Sequential Write-Ahead Logging is fast (sequential I/O) and lets data pages flush lazily.
17If a database completely lacked Isolation, what operational disaster would immediately become prevalent under heavy load?
CorrectD: Data would become corrupted from concurrent overwrites and read anomalies
Without Isolation, concurrent transactions produce dirty reads, lost updates, and data corruption.
IncorrectD: Data would become corrupted from concurrent overwrites and read anomalies
Without Isolation, concurrent transactions produce dirty reads, lost updates, and data corruption.
18What does the 'C' in ACID represent?
CorrectA: Consistency
C stands for Consistency — moving from one valid, rule-compliant state to another.
IncorrectA: Consistency
C stands for Consistency — moving from one valid, rule-compliant state to another.
19A completed transaction's data is physically flushed to a solid-state drive array. This specific physical action directly fulfills which property?
CorrectB: Durability
Physically flushing data to persistent storage fulfils Durability.
IncorrectB: Durability
Physically flushing data to persistent storage fulfils Durability.
20Which database component guarantees that transactions executing at the exact same time yield the exact same result as if they ran entirely sequentially?
CorrectC: The Concurrency Control Manager
The Concurrency Control Manager ensures concurrent transactions equal some serial execution order.
IncorrectC: The Concurrency Control Manager
The Concurrency Control Manager ensures concurrent transactions equal some serial execution order.
ACID Properties — Concepts
1What defines a "Dirty Read" in database concurrency?
CorrectC: Reading uncommitted data modified by a separate, active transaction
A Dirty Read occurs when a transaction reads uncommitted data from another transaction that may later abort.
IncorrectC: Reading uncommitted data modified by a separate, active transaction
A Dirty Read occurs when a transaction reads uncommitted data from another transaction that may later abort.
2Which ANSI SQL isolation level provides the absolute highest degree of protection against concurrent transaction anomalies?
CorrectD: Serializable
Serializable is the strictest ANSI SQL isolation level, preventing all anomalies.
IncorrectD: Serializable
Serializable is the strictest ANSI SQL isolation level, preventing all anomalies.
3A "Non-repeatable Read" occurs when a transaction reads the same row twice and gets different data. What causes this anomaly?
CorrectA: Another transaction updated and committed changes to that specific row between the two reads
Non-repeatable Read: another transaction commits an UPDATE between two reads, producing different values.
IncorrectA: Another transaction updated and committed changes to that specific row between the two reads
Non-repeatable Read: another transaction commits an UPDATE between two reads, producing different values.
4Which database anomaly involves a transaction executing a range query twice and finding new, newly inserted rows the second time that match the criteria?
CorrectB: A Phantom Read
Phantom Read: a range query returns different rows because rows were inserted/deleted between two reads.
IncorrectB: A Phantom Read
Phantom Read: a range query returns different rows because rows were inserted/deleted between two reads.
5What is the primary architectural purpose of the Write-Ahead Log (WAL) protocol?
CorrectD: To ensure durability by writing changes to a fast sequential log before modifying the scattered actual database files
Write-Ahead Logging writes log records before touching data pages, enabling crash recovery.
IncorrectD: To ensure durability by writing changes to a fast sequential log before modifying the scattered actual database files
Write-Ahead Logging writes log records before touching data pages, enabling crash recovery.
6Which isolation level completely eliminates dirty reads but explicitly permits non-repeatable reads to occur?
CorrectC: Read Committed
Read Committed prevents dirty reads but still allows non-repeatable reads.
IncorrectC: Read Committed
Read Committed prevents dirty reads but still allows non-repeatable reads.
7In a rigorous Two-Phase Locking (2PL) protocol, what happens during the "growing phase"?
CorrectA: A transaction can acquire new locks but cannot release any existing locks
In 2PL growing phase a transaction may acquire locks but cannot release any — preventing circular dependencies.
IncorrectA: A transaction can acquire new locks but cannot release any existing locks
In 2PL growing phase a transaction may acquire locks but cannot release any — preventing circular dependencies.
8How does Strict Two-Phase Locking (Strict 2PL) specifically enhance standard 2PL to protect Atomicity?
CorrectB: It holds all exclusive locks until the transaction formally commits or aborts, explicitly preventing cascading rollbacks
Strict 2PL holds exclusive locks until commit/abort, preventing cascading rollbacks.
IncorrectB: It holds all exclusive locks until the transaction formally commits or aborts, explicitly preventing cascading rollbacks
Strict 2PL holds exclusive locks until commit/abort, preventing cascading rollbacks.
9Which mechanism is predominantly used by modern databases (like PostgreSQL) to provide high Isolation without aggressively locking rows for read operations?
CorrectC: Multi-Version Concurrency Control (MVCC)
MVCC keeps multiple timestamped snapshots; readers access an older committed version without blocking.
IncorrectC: Multi-Version Concurrency Control (MVCC)
MVCC keeps multiple timestamped snapshots; readers access an older committed version without blocking.
10When a "Cascading Rollback" occurs, what was the underlying concurrency failure that necessitated it?
CorrectD: A transaction read data modified by another uncommitted transaction that subsequently aborted
Cascading Rollback: if T1 aborts after T2 read its uncommitted data, T2 must also abort.
IncorrectD: A transaction read data modified by another uncommitted transaction that subsequently aborted
Cascading Rollback: if T1 aborts after T2 read its uncommitted data, T2 must also abort.
11What is the primary operational drawback of operating a database at the maximum "Serializable" isolation level?
CorrectA: It drastically reduces concurrent transaction throughput and performance
Serializable forces serialized access, dramatically reducing concurrency and throughput.
IncorrectA: It drastically reduces concurrent transaction throughput and performance
Serializable forces serialized access, dramatically reducing concurrency and throughput.
12Which statement accurately describes the "Read Uncommitted" isolation level?
CorrectB: It offers the highest possible read performance but exposes the application to dirty reads, non-repeatable reads, and phantoms
Read Uncommitted allows seeing uncommitted changes — fastest but exposes dirty reads and phantoms.
IncorrectB: It offers the highest possible read performance but exposes the application to dirty reads, non-repeatable reads, and phantoms
Read Uncommitted allows seeing uncommitted changes — fastest but exposes dirty reads and phantoms.
13In the context of Durability and recovery, what does a database "Checkpoint" achieve?
CorrectD: It forces all modified in-memory data pages to the physical disk, drastically reducing the time needed for crash recovery
A Checkpoint flushes dirty buffer pages; on recovery ARIES only replays records from the last checkpoint.
IncorrectD: It forces all modified in-memory data pages to the physical disk, drastically reducing the time needed for crash recovery
A Checkpoint flushes dirty buffer pages; on recovery ARIES only replays records from the last checkpoint.
14If a transaction attempts to modify a table's schema (e.g., executing an 'ALTER TABLE' to add a column) and then rolls back successfully, what kind of ACID property is being demonstrated?
CorrectC: Data Dictionary Atomicity
Rolling back a DDL (ALTER TABLE) demonstrates Atomicity at the schema (data dictionary) level.
IncorrectC: Data Dictionary Atomicity
Rolling back a DDL (ALTER TABLE) demonstrates Atomicity at the schema (data dictionary) level.
15What specific logical guarantee does the Consistency property provide during a financial transfer operation?
CorrectA: The sum total of assets across both accounts remains mathematically balanced and unchanged before and after the transfer
Consistency during a transfer preserves the invariant: total account balances remain unchanged.
IncorrectA: The sum total of assets across both accounts remains mathematically balanced and unchanged before and after the transfer
Consistency during a transfer preserves the invariant: total account balances remain unchanged.
16Which database lock allows multiple transactions to read a resource simultaneously but strictly prevents any transaction from writing to it?
CorrectB: Shared Lock
A Shared Lock allows concurrent reads but blocks any Exclusive Lock request.
IncorrectB: Shared Lock
A Shared Lock allows concurrent reads but blocks any Exclusive Lock request.
17What is an "Exclusive Lock" in concurrency control?
CorrectD: A lock that grants a single transaction absolute read and write access, blocking all other transactions from interacting with the row
An Exclusive Lock grants sole read+write access, blocking all other shared and exclusive requests.
IncorrectD: A lock that grants a single transaction absolute read and write access, blocking all other transactions from interacting with the row
An Exclusive Lock grants sole read+write access, blocking all other shared and exclusive requests.
18How does a "Deadlock" mechanically compromise database isolation architectures?
CorrectA: Two or more transactions cyclically wait for locks held by each other, bringing execution to a permanent halt
A Deadlock is a cycle of transactions waiting for each other's locks — resolved by aborting one victim.
IncorrectA: Two or more transactions cyclically wait for locks held by each other, bringing execution to a permanent halt
A Deadlock is a cycle of transactions waiting for each other's locks — resolved by aborting one victim.
19If a database system forcefully aborts a transaction to break a deadlock, which ACID property dictates that the aborted transaction's partial changes must be reversed?
CorrectC: Atomicity
When a deadlock victim is aborted, Atomicity demands complete rollback of all its partial changes.
IncorrectC: Atomicity
When a deadlock victim is aborted, Atomicity demands complete rollback of all its partial changes.
20In the context of complex Atomicity, what does defining a "Savepoint" allow a developer to do?
CorrectB: Roll back a specific, designated portion of a transaction without aborting the entire transaction
Savepoints let developers rollback only a portion of a long transaction without aborting it entirely.
IncorrectB: Roll back a specific, designated portion of a transaction without aborting the entire transaction
Savepoints let developers rollback only a portion of a long transaction without aborting it entirely.
ACID Properties — Advanced
1In a globally distributed database environment, which specific protocol is most commonly utilized to enforce global Atomicity across multiple independent nodes?
CorrectD: The Two-Phase Commit (2PC) Protocol
Two-Phase Commit (2PC) coordinates global Atomicity: Phase 1 polls participants, Phase 2 broadcasts decision.
IncorrectD: The Two-Phase Commit (2PC) Protocol
Two-Phase Commit (2PC) coordinates global Atomicity: Phase 1 polls participants, Phase 2 broadcasts decision.
2What is a critical architectural vulnerability of the standard Two-Phase Commit (2PC) protocol?
CorrectB: It is a blocking protocol; if the coordinator crashes during the commit phase, participating nodes remain locked indefinitely
2PC blocking problem: if the coordinator crashes after Prepare, participants are stuck holding locks forever.
IncorrectB: It is a blocking protocol; if the coordinator crashes during the commit phase, participating nodes remain locked indefinitely
2PC blocking problem: if the coordinator crashes after Prepare, participants are stuck holding locks forever.
3How does the ARIES (Algorithm for Recovery and Isolation Exploiting Semantics) algorithm optimize the redo phase during crash recovery?
CorrectC: It repeats history by applying all logged changes, even those of uncommitted transactions, before executing the targeted undo phase
ARIES repeats history in redo phase — applies all logged changes including uncommitted ones — then runs undo.
IncorrectC: It repeats history by applying all logged changes, even those of uncommitted transactions, before executing the targeted undo phase
ARIES repeats history in redo phase — applies all logged changes including uncommitted ones — then runs undo.
4Under Multi-Version Concurrency Control (MVCC), how does the database handle a read request for a row that is currently being exclusively updated by another transaction?
CorrectA: It serves an older, committed version of the row from a rollback segment or undo log, completely avoiding the need for read locks
MVCC serves readers an older committed snapshot from the undo log, avoiding read locks entirely.
IncorrectA: It serves an older, committed version of the row from a rollback segment or undo log, completely avoiding the need for read locks
MVCC serves readers an older committed snapshot from the undo log, avoiding read locks entirely.
5Which complex isolation anomaly is specifically associated with Snapshot Isolation (often used in MVCC implementations) and is not inherently prevented by the standard Repeatable Read level?
CorrectB: Write Skew
Write Skew: two transactions read a consistent snapshot and write non-overlapping rows, violating a business invariant.
IncorrectB: Write Skew
Write Skew: two transactions read a consistent snapshot and write non-overlapping rows, violating a business invariant.
6What is the "Phantom Read" anomaly, and why does standard Two-Phase Locking (on individual rows) fail to prevent it?
CorrectD: It involves new rows being inserted that match a query condition; row locks cannot physically lock rows that do not yet exist, necessitating predicate or table locks
Phantom rows don't exist yet so no row lock can block them — predicate locking or SSI is required.
IncorrectD: It involves new rows being inserted that match a query condition; row locks cannot physically lock rows that do not yet exist, necessitating predicate or table locks
Phantom rows don't exist yet so no row lock can block them — predicate locking or SSI is required.
7In the context of database Durability and recovery algorithms, what is the exact difference between a physical log and a logical log?
CorrectC: A physical log records exact byte changes to specific disk pages, whereas a logical log records high-level operations (like UPDATE Accounts SET Balance)
Physical log: exact byte changes. Logical log: high-level operations like UPDATE. Physical is faster to replay.
IncorrectC: A physical log records exact byte changes to specific disk pages, whereas a logical log records high-level operations (like UPDATE Accounts SET Balance)
Physical log: exact byte changes. Logical log: high-level operations like UPDATE. Physical is faster to replay.
8How does the "Force/No-Steal" buffer pool management policy interact with ACID properties?
CorrectA: It guarantees perfect Durability without requiring a redo log, but forces catastrophic disk I/O bottlenecks during commits because all dirty pages must be flushed immediately
Force/No-Steal: all dirty pages flushed at commit (no redo log needed) but commit I/O is brutal.
IncorrectA: It guarantees perfect Durability without requiring a redo log, but forces catastrophic disk I/O bottlenecks during commits because all dirty pages must be flushed immediately
Force/No-Steal: all dirty pages flushed at commit (no redo log needed) but commit I/O is brutal.
9Most modern, high-performance databases utilize a "No-Force/Steal" buffer management policy. What must be rigorously implemented to ensure Atomicity and Durability under this volatile policy?
CorrectD: Both an Undo log (to reverse stolen uncommitted pages) and a Redo log (to recreate unforced committed pages)
No-Force/Steal needs both an Undo log (reverse stolen uncommitted pages) and Redo log (recreate committed pages).
IncorrectD: Both an Undo log (to reverse stolen uncommitted pages) and a Redo log (to recreate unforced committed pages)
No-Force/Steal needs both an Undo log (reverse stolen uncommitted pages) and Redo log (recreate committed pages).
10What defines a "Heuristic Decision" in the complex context of distributed transaction management (like XA transactions)?
CorrectB: A local database node unilaterally deciding to commit or abort an in-doubt transaction after losing network contact with the global coordinator
A Heuristic Decision is a unilateral commit/abort by a node that lost contact with the 2PC coordinator.
IncorrectB: A local database node unilaterally deciding to commit or abort an in-doubt transaction after losing network contact with the global coordinator
A Heuristic Decision is a unilateral commit/abort by a node that lost contact with the 2PC coordinator.
11When analyzing the CAP Theorem, why must heavily distributed NoSQL databases often compromise strict ACID Consistency?
CorrectC: Because inevitable network partitions force the system to choose between rejecting writes entirely or accepting writes that lead to temporary node divergence
Network partitions force distributed systems to choose between consistency and availability — strict ACID is incompatible with partition availability.
IncorrectC: Because inevitable network partitions force the system to choose between rejecting writes entirely or accepting writes that lead to temporary node divergence
Network partitions force distributed systems to choose between consistency and availability — strict ACID is incompatible with partition availability.
12In advanced concurrency control mechanisms, what is "Optimistic Concurrency Control" (OCC)?
CorrectA: A method where transactions execute in an isolated workspace without taking locks, validating for data conflicts only at the exact moment they attempt to commit
OCC lets transactions run without locks; at commit it validates no conflicts occurred — aborts if they did.
IncorrectA: A method where transactions execute in an isolated workspace without taking locks, validating for data conflicts only at the exact moment they attempt to commit
OCC lets transactions run without locks; at commit it validates no conflicts occurred — aborts if they did.
13Under extreme high contention (many transactions desperately trying to update the exact same row), how does Optimistic Concurrency Control (OCC) generally perform compared to Pessimistic Locking?
CorrectD: It performs drastically worse due to massive amounts of transaction validation failures, resulting in endless rollbacks and retries
Under high contention OCC fails constantly — constant aborts/retries hurt performance vs pessimistic locking.
IncorrectD: It performs drastically worse due to massive amounts of transaction validation failures, resulting in endless rollbacks and retries
Under high contention OCC fails constantly — constant aborts/retries hurt performance vs pessimistic locking.
14What is the precise purpose of the "Log Sequence Number" (LSN) in Write-Ahead Logging architectures?
CorrectB: It uniquely identifies the strict chronological order of log records, allowing the recovery engine to determine exactly which disk pages require recovery
LSN uniquely identifies log records chronologically, telling ARIES which pages need redo or undo during recovery.
IncorrectB: It uniquely identifies the strict chronological order of log records, allowing the recovery engine to determine exactly which disk pages require recovery
LSN uniquely identifies log records chronologically, telling ARIES which pages need redo or undo during recovery.
15Which advanced isolation phenomenon occurs when a transaction reads data modified by another transaction, but that second transaction ultimately aborts, and the first transaction commits based on the flawed, erased data?
CorrectC: An Uncommitted Dependency (Dirty Read/Dirty Write)
Uncommitted Dependency: T1 reads T2's uncommitted change; T2 aborts; T1 committed based on data that never existed.
IncorrectC: An Uncommitted Dependency (Dirty Read/Dirty Write)
Uncommitted Dependency: T1 reads T2's uncommitted change; T2 aborts; T1 committed based on data that never existed.
16How do "Intention Locks" (like Intention Shared or Intention Exclusive) drastically improve the efficiency of database isolation?
CorrectA: They allow the system to lock a coarse-granularity object (like a table) to indicate that finer-granularity locks (like rows) are currently held within it, preventing conflicting table locks without scanning every row
Intention Locks on tables signal row-level locks are held inside, preventing conflicting table-level locks without row scans.
IncorrectA: They allow the system to lock a coarse-granularity object (like a table) to indicate that finer-granularity locks (like rows) are currently held within it, preventing conflicting table locks without scanning every row
Intention Locks on tables signal row-level locks are held inside, preventing conflicting table-level locks without row scans.
17In the context of the ARIES recovery algorithm, what is a "Compensation Log Record" (CLR)?
CorrectB: A special log record written specifically during the undo phase of a transaction rollback, ensuring that aborted operations are never undone twice if the system crashes again during recovery
CLRs are written during undo; they are never undone themselves, preventing double-undo if the system crashes during recovery.
IncorrectB: A special log record written specifically during the undo phase of a transaction rollback, ensuring that aborted operations are never undone twice if the system crashes again during recovery
CLRs are written during undo; they are never undone themselves, preventing double-undo if the system crashes during recovery.
18What is the concept of a "Saga Pattern" in modern microservices architecture, and how does it relate to traditional ACID properties?
CorrectD: It breaks a massive distributed transaction into a sequence of local ACID transactions, utilizing compensating transactions instead of global locks to handle failures and rollbacks
Saga Pattern: decomposes a distributed transaction into local ACID transactions with compensating transactions for rollback.
IncorrectD: It breaks a massive distributed transaction into a sequence of local ACID transactions, utilizing compensating transactions instead of global locks to handle failures and rollbacks
Saga Pattern: decomposes a distributed transaction into local ACID transactions with compensating transactions for rollback.
19If a financial application strictly requires absolute protection against Write Skew anomalies across multiple interwoven accounts, which isolation level is mathematically required?
CorrectA: Serializable
Write Skew requires Serializable isolation — even Repeatable Read (Snapshot Isolation) allows write skew.
IncorrectA: Serializable
Write Skew requires Serializable isolation — even Repeatable Read (Snapshot Isolation) allows write skew.
20How does the "Wait-Die" deadlock prevention scheme mechanically operate?
CorrectC: An older transaction is allowed to wait for a lock held by a younger transaction, but a younger transaction requesting a lock held by an older transaction is immediately aborted (dies)
Wait-Die: old transaction waits for young; young transaction requesting a lock held by old immediately aborts (dies).
IncorrectC: An older transaction is allowed to wait for a lock held by a younger transaction, but a younger transaction requesting a lock held by an older transaction is immediately aborted (dies)
Wait-Die: old transaction waits for young; young transaction requesting a lock held by old immediately aborts (dies).
Conclusion: Mastering ACID Properties
These 60 MCQs cover the complete ACID properties and transaction management spectrum — from analyzing bank transfer isolation anomalies to trace 2PL and strict 2PL schedules, to engineering crash recovery logs using the ARIES framework, to coordinating distributed transactions across partitioning boundaries.
The key mental model: Atomicity handles partial aborts; Consistency ensures state integrity constraints; Isolation structures concurrent execution; and Durability locks down committed records permanently. High-reliability database applications depend fundamentally on orchestrating these four properties.
After completing this practice set, deepen your database systems knowledge with the full ACID Properties in DBMS theory notes and explore engineering implementations such as lock managers and log recovery modules.
📌 Key Takeaways — ACID Properties
- Atomicity: "All or Nothing" — transactions fully commit or fully roll back, never leaving partial state.
- Consistency: Every transaction takes the database from one valid state to another, preserving all integrity constraints.
- Isolation: Concurrent transactions do not see each other's intermediate states — enforced by locking or MVCC.
- Durability: Committed data survives crashes — ensured by Write-Ahead Logging (WAL) and checkpoints.
- Isolation Levels: Read Uncommitted → Read Committed → Repeatable Read → Serializable (strictest).
- Dirty Read: Reading another transaction's uncommitted data (prevented by Read Committed and above).
- Phantom Read: New rows appearing between two range queries (prevented by Serializable only).
- 2PC: Two-Phase Commit enforces distributed Atomicity but is a blocking protocol if the coordinator crashes.
- MVCC: Readers see a snapshot; writers don't block readers — used by PostgreSQL, MySQL InnoDB, Oracle.
- ARIES: Analysis → Redo (repeat history) → Undo (rollback uncommitted) — the gold-standard crash recovery algorithm.
Quick Review & Summary
| Property | Core Guarantee | Key Mechanism |
|---|---|---|
| Atomicity | All or Nothing | Undo Log / Rollback |
| Consistency | Valid State → Valid State | Constraints, Triggers, FK rules |
| Isolation | No Intermediate Visibility | Two-Phase Locking / MVCC |
| Durability | Committed Data Survives Crashes | WAL + Checkpoints + Redo Log |
| Read Uncommitted | Dirty Reads allowed | No read locks |
| Read Committed | No Dirty Reads | Short-duration read locks |
| Repeatable Read | No Non-repeatable Reads | Long-duration read locks / Snapshot |
| Serializable | No Phantoms / Write Skew | Predicate locks / SSI |
| 2PC | Distributed Atomicity | Prepare → Commit/Abort |
| ARIES | Correct Crash Recovery | Analysis → Redo → Undo + CLRs |
Frequently Asked Questions
Q. How many ACID Properties MCQs are available on this page?
Q. What specific topics do these ACID MCQs cover?
Q. Are these MCQs suitable for GATE (CS) and university DBMS exam preparation?
Q. What is the difference between the four main ACID properties?
Q. What is the difference between Study Mode and Exam Mode?
Q. What are the four ANSI SQL isolation levels and what anomalies does each prevent?
Q. What is Two-Phase Commit (2PC) and what is its main weakness?
Struggling with some questions? Re-read the full Theory Guide: ACID Properties in DBMS