Queries, Indexes and Transactions | IT Foundations for Beginners

In this chapter

We'll learn the four everyday operations (CRUD) and how to write them in SQL, how an index makes searches fast (like the index at the back of a book), and transactions and ACID — the all-or-nothing rule that finally makes a double-sold seat impossible.

16–18 min

The Problem in Real Life

The kiosk now talks to the database, not a file. But John isn't done. "Even with one database, two sales arriving in the same millisecond can still both see C14 as free, unless we tell the database to treat checking and selling as one single step."

Anna remembers writing it down back in Act 08, as a known risk: two fans, same seat, same second — needs a database transaction. "So this is the transaction," she says. "Finally."

"Check, then sell" is two steps. A transaction makes it one — so nothing can happen in between.

John

Separate Steps vs. One Unbreakable Step

Four everyday operations

Almost everything an app does to data is create, read, update or delete.

Searching millions of rows

Without help, finding one booking means reading every row in the table.

The gap between check and change

If two sales both check before either changes, both see "free" — the race behind the double booking.

Queries, CRUD, Indexes and Transactions

CRUD — the four things you do to data: almost every feature in every app is made of four operations, known as CRUD: Create (add new data), Read (look at data), Update (change data) and Delete (remove data). In SQL they are INSERT, SELECT, UPDATE and DELETE. Notice they match the REST verbs from Act 12: POST, GET, PATCH/PUT and DELETE. A REST API is very often a thin layer over CRUD on database tables.

Index — the index at the back of a book: to find every mention of "TLS" in a 600-page book, you don't read all 600 pages; you look it up in the index at the back, which lists the page numbers. A database index does the same for a column: it keeps a sorted lookup structure (usually a tree, from Act 09) that points straight to the matching rows. Searching bookings by fan_id across 5 million rows goes from reading every row (O(n)) to a handful of steps (O(log n)). The catch: every index takes space and makes writes a little slower, because the index must be updated too — so you add indexes for the searches you actually do often.

Transaction — a bank transfer: when you send money to a friend, two things must happen: money leaves your account and arrives in theirs. If the system crashed after the first step, the money would vanish. So banks wrap both steps in a transaction: a group of changes that happens completely or not at all. If anything fails, everything is undone (rolled back). If everything succeeds, it's made permanent (committed).

A — Atomic (all or nothing): every step in the transaction happens, or none does. No half-sold seats, no money that left one account without arriving in the other.
C — Consistent (the rules always hold): a transaction can only take the database from one valid state to another. If a step would break a rule — like a booking for a seat that doesn't exist — the whole transaction is refused.
I — Isolated (no peeking at half-done work): transactions running at the same time don't see each other's unfinished changes, and can't interfere — as if they ran one after another. This is the one that solves the double booking.
D — Durable (once done, it stays done): once a transaction is committed, it survives crashes and power cuts. The database writes it safely to disk before saying "done."

Table — CRUD in SQL and REST

Operation	SQL	REST (Act 12)	Example
Create	INSERT	POST	Create a booking
Read	SELECT	GET	Show a fan's bookings
Update	UPDATE	PATCH / PUT	Change a seat's state to SOLD
Delete	DELETE	DELETE	Remove an expired hold

Table — ACID in plain English

Letter	Name	Promise	Bank transfer version
A	Atomic	All steps happen, or none	Money leaves AND arrives, or nothing moves
C	Consistent	The rules always hold	No account goes below its limit
I	Isolated	Concurrent transactions don't interfere	Two transfers at once can't both spend the same money
D	Durable	Committed means permanent	A power cut can't undo a finished transfer

Table — The race condition, without a transaction

Step	Sale A (online)	Sale B (kiosk)	Seat C14
1	Reads C14		FREE
2		Reads C14	FREE
3	Marks SOLD		SOLD
4		Marks SOLD	SOLD — two tickets!

This whole line is a row — one record

Two sales of seat C14 in the same millisecond — with a transaction

Sale A: BEGIN, lock C14

reads FREE

Sale B: BEGIN, wants C14

must WAIT — row is locked

A finishes first

Sale A marks C14 SOLD, inserts booking, COMMIT

lock released

B continues

Sale B now reads C14: SOLD

refused: "this seat was just taken" — ROLLBACK

Exactly one booking

plus a unique constraint as a second safety net

CRUD in SQL

-- Create
INSERT INTO bookings (seat_id, fan_id, price_paid_cents) VALUES (5021, 12, 4999);

-- Read
SELECT * FROM bookings WHERE fan_id = 12;

-- Update
UPDATE seats SET state = 'SOLD' WHERE id = 5021;

-- Delete
DELETE FROM holds WHERE held_until < NOW();

Everything between BEGIN and COMMIT happens as one unbreakable step. FOR UPDATE locks the seat row so a second sale has to wait.

Selling a seat safely: a transaction, a lock and a rule

-- One-time rule: a seat can only ever have one booking
ALTER TABLE bookings ADD CONSTRAINT one_booking_per_seat UNIQUE (seat_id);

-- Every sale
BEGIN;
SELECT state FROM seats WHERE id = 5021 FOR UPDATE;   -- lock the row, check it
-- (the app checks the result: if it isn't FREE or HELD by this fan, ROLLBACK)
UPDATE seats SET state = 'SOLD' WHERE id = 5021;
INSERT INTO bookings (seat_id, fan_id, price_paid_cents) VALUES (5021, 12, 4999);
COMMIT;

-- And an index for a common search
CREATE INDEX bookings_by_fan ON bookings (fan_id);

If anything fails between BEGIN and COMMIT, the database undoes all of it — no half-sold seats.

The race, step by step: without a transaction, two sales run as separate steps. Sale A reads C14: FREE. Sale B reads C14: FREE. Sale A marks it SOLD. Sale B marks it SOLD. Both fans get a ticket. This is called a race condition: the result depends on who runs first, and both "win."

The fix — one unbreakable step: Anna wraps "check and sell" in a transaction and asks the database to lock the seat row while she checks it (SELECT ... FOR UPDATE). Now, if Sale B arrives a millisecond later, it has to wait until Sale A's transaction finishes. When it finally reads C14, the seat is already SOLD, so Sale B is refused cleanly: "Sorry, this seat was just taken."

A second safety net — a rule the database enforces: John adds one more line: a unique constraint on bookings, so the same seat at the same event can only ever have one booking row. Even if some other code forgets the transaction one day, the database itself will refuse the second booking. Two locks on the same door: careful code, and a rule the database guarantees.

They replay the Friday evening in a test: online and kiosk sales of C14 at exactly the same moment, a thousand times over. Every single time, one sale succeeds and the other is refused. Double-booking isn't unlikely any more — it's impossible.

Key Takeaway

CRUD — Create, Read, Update, Delete (INSERT, SELECT, UPDATE, DELETE in SQL) — covers almost everything apps do to data. An index is like a book's index: a sorted lookup that makes searches fast at a small cost to writes. A transaction is like a bank transfer — all or nothing — and ACID (Atomic, Consistent, Isolated, Durable) is its promise. Wrapping "check and change" in a transaction, plus a unique constraint, makes race conditions like double-booking impossible.

Why This Matters

Transactions are what make databases trustworthy for money, tickets, stock and anything else people pay for. Race conditions like the one at the venue are among the most common — and most expensive — bugs in real systems, and on Sale Day thousands of them would happen every minute without this protection. Indexes, meanwhile, are often the single biggest speed-up for a slow app.

Double-booking is solved. Anna has one question left from all this: everyone keeps calling PostgreSQL "a SQL database." Are there databases that aren't? Yes — and BlueTicket already uses one, for something she built herself in Act 12.