The Two-Seat Problem

Anna, customer 214 in the Marquee's database, wants two tickets for Friday's 8 p.m. showing of Night Bus: seats G7 and G8, side by side. It's a date night, and the whole point is the pair. One seat is not half a success; half a date night is worse than none.

In the database, that wish is two INSERTs into bookings — one row for G7, one for G8. And between those two statements sits a gap that nothing so far in this book protects. If the power dies in that gap, G7 is booked, G8 never happens, and no record anywhere says the two belonged together. The fix is called a transaction: a way to draw a bracket around several changes and make the bracket, not the statement, the unit that succeeds or fails.

The Gap Between Two Statements

Here is Anna's booking the way Chapter 5 would write it — two honest INSERTs, one after the other:

INSERT INTO bookings (customer_id, screening_id, seat)
VALUES (214, 88, 'G7');

INSERT INTO bookings (customer_id, screening_id, seat)
VALUES (214, 88, 'G8');

Each statement on its own is protected. Chapter 5 made that promise: a single INSERT either fully happens or leaves the table untouched, and there is no such thing as a half-inserted row. That promise still holds. The problem is that it holds per statement, and Anna's intention spans two.

Walk the failure in slow motion. The first INSERT lands: G7 is Anna's. Then, before the second statement arrives, the power dies. When the database comes back, every rule still holds and every saved row is intact. It is perfectly consistent — and perfectly wrong: G7 booked, G8 free, and Anna's plan torn down the middle. The database never knew the two rows were one decision, because nothing told it.

BEGIN and COMMIT: Drawing the Bracket

Telling it is exactly what a transaction does. You open the bracket with BEGIN;, put the statements inside, and close it with COMMIT; — three ordinary words, and the gap is gone:

BEGIN;

INSERT INTO bookings (customer_id, screening_id, seat)
VALUES (214, 88, 'G7');

INSERT INTO bookings (customer_id, screening_id, seat)
VALUES (214, 88, 'G8');

COMMIT;

Inside the bracket, changes are provisional. The database performs them, but it promises nothing until COMMIT. If a crash lands between the two INSERTs now, the database notices the unfinished bracket when it restarts and un-does the partial work: G7's booking un-happens along with everything else since BEGIN. Anna gets both seats, or she gets a clean "sorry" and picks another night. The torn middle state has been removed from the list of possible outcomes.

You already use this pattern every time you shop online. The basket is BEGIN: you can fill it, change it, or abandon it, and nothing has happened yet. The confirm-order button is COMMIT: one press, and everything in the basket ships together — nobody is ever charged for half a basket. That is the whole idea mapped; from here on we'll use the real words.

ROLLBACK: the Escape Hatch

COMMIT is one way out of the bracket. The other is ROLLBACK;, which abandons it: everything since BEGIN un-happens, deliberately. And that turns the bracket into a seatbelt for your own mistakes. Remember Chapter 5's horror story — the UPDATE that forgot its WHERE and changed every row in the table? Inside a transaction, that tragedy becomes a near-miss:

BEGIN;

UPDATE bookings SET seat = 'H7';
-- no WHERE: every booking in the table just changed

SELECT COUNT(*) FROM bookings WHERE seat = 'H7';
-- the count is horrifying. Good thing the bracket is still open.

ROLLBACK;
-- everything since BEGIN un-happens; the table is exactly as it was

Read what happened there: the mistake ran, a quick SELECT revealed the damage, and ROLLBACK erased it as if it had never been typed. This is a professional habit worth copying from day one — wrap a risky change in a bracket, run it, look at the result, and only then choose COMMIT or ROLLBACK. It costs two extra lines and removes the sweaty part of changing data.

One boundary to be clear about, because it matters for the rest of the chapter: ROLLBACK only works while the bracket is open. The moment COMMIT runs, the changes are real, and no command un-happens them. What saves you after COMMIT is a different tool entirely, and it gets the last topic of this chapter to itself.

Autocommit, Honestly

One confession, so your mental model is complete. Every bare statement you have run since Chapter 3 was secretly a transaction too. Databases run in autocommit mode by default: each statement that arrives on its own gets an invisible BEGIN before it and an invisible COMMIT after it. That is why a single INSERT could never half-happen — you have been protected all along, one statement at a time.

So BEGIN doesn't switch on new machinery; the machinery was always on. What the bracket gives you is the choice of size: instead of a stream of tiny one-statement transactions, you decide which statements form one decision. Anna's pair of seats is one decision. Now the database knows it too.