Sample questions
Transactions IsolationDifficulty 1
In the ACID acronym, what does Atomicity guarantee about a transaction?
- aIts changes survive a crash once it has committed
- bAll of its statements take effect together, or none of them do✓
- cConcurrent transactions cannot see each other's uncommitted changes
- dIt moves the database from one valid state to another valid state
Explanation:Atomicity is the all-or-nothing property: a transaction's operations either all commit or all roll back. Option (a) is Durability, (c) is Isolation, and (d) is Consistency — the other three ACID letters, each of which addresses a different concern.
Transactions IsolationDifficulty 1
In the ACID acronym, what does Durability guarantee?
- aA transaction sees a consistent snapshot for its entire duration
- bEach transaction behaves as if it were the only one running
- cA transaction either completes fully or is rolled back with no effect
- dAfter a transaction commits, its changes survive a later system crash✓
Explanation:Durability means committed data is not lost, even on power failure or crash — engines achieve this by flushing a write-ahead log to durable storage before acknowledging the commit. Option (c) describes Atomicity, and (a)/(b) describe aspects of Isolation.
Transactions IsolationDifficulty 2
What does the Isolation property in ACID primarily address?
- aKeeping concurrent transactions from seeing each other's incomplete work✓
- bRecovering already-committed data after a power failure
- cValidating each row against CHECK and foreign-key constraints
- dSplitting a large transaction into smaller units that commit on their own
Explanation:Isolation governs how concurrent transactions are prevented from interfering — ideally each behaves as if it ran alone. Recovering committed data (b) is Durability, and constraint validation (c) belongs to Consistency; (d) contradicts atomicity rather than describing isolation.
Transactions IsolationDifficulty 2
In ACID, the Consistency property means that a transaction...
- a...always reads the most recently committed value written by any other open session
- b...holds all of its locks until every other transaction has finished
- c...moves the database from one valid state to another, preserving its constraints✓
- d...runs faster when fewer other transactions are active
Explanation:Consistency means every committed transaction leaves the database satisfying all its rules (constraints, cascades, triggers) — it never lands in an invalid state. Option (a) describes read visibility (an isolation concern), and (b)/(d) are unrelated to the ACID Consistency guarantee.
Transactions IsolationDifficulty 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
ROLLBACK;
After this runs, what is account 1's balance?
- aReduced by 100 — the UPDATE already wrote the new value to the row
- bUnchanged — ROLLBACK undoes the UPDATE made inside the transaction✓
- cReduced by 100 until a later COMMIT is issued to overwrite it
- dNULL — ROLLBACK clears every column the transaction touched
Explanation:ROLLBACK discards every change made since BEGIN, so the balance returns to its pre-transaction value as if the UPDATE never happened. The UPDATE's effect was only visible inside the open transaction (a is wrong), and a rollback restores old values rather than nulling columns (d is wrong).
Transactions IsolationDifficulty 2
An order-placement routine inserts one row into orders, one row into order_items, then decrements products.stock. Why should these three statements run inside a single transaction?
- aSo a partial failure cannot leave orders without their items or wrong stock✓
- bSo each statement is automatically retried until it eventually succeeds
- cSo the three statements are executed in parallel and finish faster
- dSo the three statements can bypass foreign-key and CHECK constraints until commit
Explanation:Wrapping the three writes in one transaction makes them atomic: if any step fails, all are rolled back, so you never persist an order with no items or an unadjusted stock count. A transaction does not add automatic retries (b), does not parallelize statements (c), and does not disable constraints (d).