Quiz

1. Is using a transaction always preferable to using locks? Are there moments we should use locks instead?

   Yes, pretty much.

   But transactions have trouble with "phantom rows" (rows inserted after the transaction starts), whereas if you lock the entire table, that can't happen. Example:

   select * from accounts where balance < 1000

   but then some other account goes below 1000; is that okay?

   But locks are heavy and should be avoided.

2. The reading explains that strict two-phase locking is called strict because locks are held until the transaction is done, not just until the lock is no longer needed. Can you give an example of what would happen if we released locks as soon as we didn't need them instead of waiting until commit/abort?

   Sure — here’s a concrete example that shows why strict 2-phase locking (strict 2PL) requires holding all write locks until commit/abort, rather than releasing them as soon as we’re “done” with that item.

   The short version: If locks were released early, another transaction might see uncommitted data, act on it, and then the first transaction might abort, leaving the second one in a logically impossible state.

   This is a cascading abort and violates serializability.

   For example, in transaction 1, the person is considering returning an item to the online store. They give up the lock and the inventory increases by 1.

   Transaction 2 begins and buys that item.

   But then Transaction 1 aborts; the person decides not to release the item after all.

   Now Transaction 2 is in a terrible state. Do we abort it? What?

3. How do databases balance the tradeoff between avoiding anomalies and avoiding deadlock?

   We always want to avoid deadlock. That's fairly straightforward: always make sure that either (1) all locks are released or (2) all transactions commit or abort.

   Anomalies happen at different isolation levels, so you consider the ones that are possible for your chosen isolation level, and decide whether they are acceptable.

   Example: dirty read, which is similar to the above: T1 uses current price to decide whether to buy something, but T2 modifies the price concurrently and writes the new value before committing. Does T1 have to go back and re-consider the purchase?

4. Generally how do you choose the right isolation level for different scenarios?

   Carefully and thoughtfully? You have to think about what might go wrong for different possible anomalies.

   Usually, err on the side of less concurrency and greater correctness, but not always.

5. I would love to see more examples showing how transactions work in Flask with real code.

   Transactions are more of a MySQL thing. Flask just delivers the start transaction; and commit; code.