Pick a level for six workloads

import java.util.*;

/**
 * Challenge: six real workloads. Choose an isolation level for each, or argue
 * that the level is not the right tool.
 *
 * Two of the six are answered with a lock rather than a level. One is wrong at
 * every level including repeatable read. Getting those three right is the
 * exercise; the other three are there so the answers are not all the same.
 */
public class Starter {

    // ── 1 ─────────────────────────────────────────────────────────────────
    // A nightly reconciliation report. Reads eleven tables, takes 40 seconds,
    // writes nothing. Totals must agree with the rows they summarise.

    // ── 2 ─────────────────────────────────────────────────────────────────
    // Applying a promotional credit:
    //     balance = SELECT balance FROM account WHERE id = ?
    //     UPDATE account SET balance = <balance + 500> WHERE id = ?
    // Two agents can run this for the same customer at the same time.

    // ── 3 ─────────────────────────────────────────────────────────────────
    // An on-call roster. Going off duty is allowed only if at least one other
    // engineer is still on duty:
    //     SELECT count(*) FROM oncall WHERE on_duty = true
    //     -- if count >= 2 then
    //     UPDATE oncall SET on_duty = false WHERE engineer = ?
    // Two engineers click at the same instant.

    // ── 4 ─────────────────────────────────────────────────────────────────
    // Recording a page view: UPDATE page SET views = views + 1 WHERE id = ?
    // Thousands per second, exactness not critical.

    // ── 5 ─────────────────────────────────────────────────────────────────
    // Issuing the next invoice number:
    //     SELECT max(number) FROM invoice
    //     INSERT INTO invoice (number, ...) VALUES (<max + 1>, ...)
    // Numbers must be unique and gapless for the tax authority.

    // ── 6 ─────────────────────────────────────────────────────────────────
    // A customer-facing "your recent orders" screen. One query, fast, and a
    // slightly stale result is fine.

    public static void main(String[] args) {

        // TODO 1: for each workload, name the anomaly that would bite at read
        // committed. If you cannot name one, that is your answer — read
        // committed is fine and you should say so rather than reaching higher.

        // TODO 2: choose a level for each. Write one sentence of justification
        // that names the anomaly, not the vibe.

        // TODO 3: two of the six are read-modify-write. Find them. For each,
        // give the three fixes in order of preference — single statement,
        // pessimistic lock, optimistic version — and say which you would
        // actually use here and why.

        // TODO 4: one workload is broken at read committed AND at repeatable
        // read, even on PostgreSQL, because the two transactions read rows the
        // other is about to write and neither sees the other's write. Name the
        // anomaly, and say why a row lock on what you read does not save you.

        // TODO 5: for workload 5, does serializable actually give you gapless
        // numbering? Consider what happens when a transaction aborts after
        // taking number 41. Propose something better than an isolation level.

        // TODO 6: which of your six answers change if the database is MySQL
        // InnoDB rather than PostgreSQL? There are at least two.

        // TODO 7: workload 1 wants a 40-second snapshot. On PostgreSQL, what
        // does holding that snapshot open cost the tables it reads? Say
        // whether you would still do it.

        // TODO 8: someone proposes setting the whole application to
        // serializable so none of this needs thinking about. Write the two
        // sentences you would say in that review.
    }
}