CSE Roadmap

Core tier · about 2 hours · 4 parts

Part 4 of 4 · 3 min read

Exercises

Easy

Easy 1: Name the guarantee (15 minutes)

For each statement, name which ACID property it describes: "the transfer either fully happens or not at all," "a crash right after confirming a payment does not undo it."

Done when: both are correctly matched to atomicity and durability.

Hint

"Either fully happens or not at all" is atomicity; "a crash does not undo it" is durability.

Easy 2: Why one statement isn't enough (15 minutes)

Explain in two sentences why the bank transfer needs two separate UPDATE statements, not one.

Done when: the explanation correctly names that two different accounts, each a separate row, need separate writes.

Hint

Each account is a separate row, and SQL statements write to rows individually.

Easy 3: Spot the missing COMMIT (15 minutes)

A transaction runs BEGIN, then both UPDATE statements, but the application crashes before COMMIT. State what should happen to the two updates.

Done when: the answer correctly states both updates are discarded, due to atomicity.

Hint

Atomicity discards every write from an uncommitted transaction.

Medium

Medium 1: Write the full transaction (25 minutes)

Write a transaction that moves 500 from account 'A' to account 'B', including BEGIN and COMMIT.

Done when: a correct three-statement transaction (two updates between BEGIN and COMMIT) is given.

Hint

BEGIN; UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A'; UPDATE accounts SET balance = balance + 500 WHERE account_id = 'B'; COMMIT;

Medium 2: A consistency rule in action (25 minutes)

accounts has a CHECK (balance >= 0) constraint. Explain what happens if a transfer would make an account's balance negative.

Done when: the explanation correctly states the transaction is rejected, since it would violate a declared constraint.

Hint

The database rejects the transaction, since the resulting balance would violate the CHECK constraint.

Medium 3: Isolation, described without jargon (25 minutes)

Describe, in plain language and without using the word "isolation," what could go wrong if a second transaction read the bookstore's balance in the middle of the transfer, before the second UPDATE ran.

Done when: the description correctly identifies a reader seeing a momentarily incorrect, half-finished value.

Hint

A reader could see the bookstore's balance already reduced, but the supplier's balance not yet increased, making the total look like money vanished.

Hard

Hard 1: Atomicity across more than two writes (30 minutes)

A single order places three writes: inserting the order, decreasing the book's stock count, and increasing the customer's loyalty points. Explain what atomicity guarantees if the system crashes after the first write but before the third.

Done when: the explanation correctly states all three writes are undone together, not just the ones that already happened.

Hint

All three writes are rolled back together, including the insert and the stock decrease that already happened before the crash.

Hard 2: Durability's actual boundary (35 minutes)

Explain precisely when durability's guarantee begins. Specifically, does durability protect a transaction's effect before COMMIT returns, or only from the moment COMMIT returns successfully?

Done when: the answer correctly places the guarantee at the moment COMMIT succeeds, not before.

Hint

Durability's guarantee starts exactly when COMMIT returns successfully, not at any point before.

Hard 3: A transaction that is atomic but not consistent (40 minutes)

Describe a transaction that successfully commits all its writes (fully atomic) but still leaves the database in a state that violates a business rule not enforced as a database constraint. Explain why atomicity alone did not prevent this.

Done when: a concrete example is given where an unenforced rule, not a database constraint, is violated despite full atomicity.

Hint

A rule like "a customer cannot place more than 10 orders a day," if never written as a CHECK constraint or trigger, can be violated even though every individual transaction that led there was fully atomic.