Module 57: Transactions and Indexes

Why each option is right or wrong

• “The order is kept and only the stock update is retried later” (Incorrect): Nothing is kept or retried automatically; it all rolls back.
• “The database saves whichever step happened to finish first” (Incorrect): A transaction doesn't keep partial work.
• “Both changes are kept, and an error is logged for later” (Incorrect): On failure, the database undoes the changes, so neither is kept.
• “Everything rolls back: neither change is kept” is correct.

  A transaction is all or nothing. If any step fails, the database undoes the steps already done, so you don't end up with an order whose stock wasn't reduced.

  • Code pattern: BEGIN, do the steps, COMMIT; on error, ROLLBACK.
  • Atomicity: the A in ACID.

📖 Revise: Lesson 1: Transactions

Why each option is right or wrong

• “Reading a single customer's name to show on a page” (Incorrect): One read on its own doesn't need a transaction.
• “Counting the rows in one table for a report” (Incorrect): A single query is already consistent on its own.
• “Adding one new customer with a single INSERT” (Incorrect): A single statement is already all or nothing on its own.
• “Recording a payment and marking the order paid” is correct.

  These two changes must happen together. A payment without the order marked paid, or the reverse, leaves the data contradicting itself. A transaction makes them succeed or fail as one.

  • Ask: would it be wrong if only some of these steps happened?
  • Single statements: are already atomic on their own.

📖 Revise: Lesson 1: Transactions

Why each option is right or wrong

• “Duplication: every change is copied to a second server” (Incorrect): Copying to other servers is replication, a separate feature.
• “Deletion: removed rows can always be recovered later” (Incorrect): Durability is about committed changes surviving crashes, not undoing deletes.
• “Distribution: data is spread across many machines” (Incorrect): That's sharding or distribution, not part of ACID.
• “Durability: committed changes survive crashes” is correct.

  Once the database says a transaction is committed, the change is written safely, so a power cut or crash a second later won't lose it.

  • A: atomicity. C: consistency. I: isolation.
  • Still need backups: durability doesn't protect against a lost disk.

📖 Revise: Lesson 2: ACID

Why each option is right or wrong

• “A deadlock” (Incorrect): A deadlock is two transactions waiting on each other; here both finished, but one change was lost.
• “A foreign key violation” (Incorrect): No link between tables was broken.
• “Normalisation” (Incorrect): Normalisation is about table design, not simultaneous updates.
• “A lost update” is correct.

  Each read 5, subtracted one and wrote 4. One sale's change overwrote the other's, so stock shows 4 when it should be 3. Writing SET stock = stock - 1 lets the database apply both safely, or a row lock can be used.

  • Fix: update relative to the current value, in one statement.
  • Or: SELECT ... FOR UPDATE inside a transaction.

📖 Revise: Lesson 2: ACID

Why each option is right or wrong

• “Columns that are shown on the screen most often” (Incorrect): Showing a column doesn't need an index; finding rows by it does.
• “The largest text columns, such as notes and messages” (Incorrect): Big text columns are rarely good index candidates.
• “Every column, so all queries become fast” (Incorrect): Each index has a cost on writes and storage.
• “Foreign keys and columns often used in WHERE or ORDER BY” is correct.

  Indexes help the database find rows without reading the whole table. Columns used to join, filter or sort in frequent queries benefit most. Primary keys are indexed automatically.

  • Check with: EXPLAIN, before and after.
  • Many databases: don't index foreign keys automatically.

📖 Revise: Lesson 3: Indexes

Why each option is right or wrong

• “Databases allow only one index per table” (Incorrect): Tables can have many indexes.
• “Indexes delete old rows to make room” (Incorrect): Indexes don't delete data.
• “Indexes make SELECT queries slower for everyone” (Incorrect): Used well, indexes make reads faster; the cost is on writes.
• “Indexes use space and slow down inserts and updates” is correct.

  Every index is a separate structure that must be updated on each insert, update and delete. Indexes nobody uses add cost without benefit. Add them for real queries, and measure.

  • Look for: slow queries in logs or monitoring.
  • Remove: indexes no query uses.

📖 Revise: Lesson 3: Indexes

Why each option is right or wrong

• “Restarting the database server to clear its memory” (Incorrect): Restarting doesn't change how the query is planned.
• “Adding more columns to the table” (Incorrect): More columns don't help find rows faster.
• “Running the query twice in a row” (Incorrect): The plan stays the same; caching may help a little, but not the full scan.
• “An index on the columns in the WHERE or JOIN” is correct.

  A full scan reads every row to find the few that match. If the query filters or joins on a column, an index lets the database jump to the matching rows instead. Check the plan again afterwards.

  • Small tables: a full scan can be the fastest option.
  • Function on the column? May stop an index being used.

📖 Revise: Lesson 4: Reading query plans

Why each option is right or wrong

• “Indexes don't work on date columns at all” (Incorrect): They work well on dates; the function is the problem.
• “The year 2026 must be written as a number, not text” (Incorrect): The type of the literal isn't the main issue; the function on the column is.
• “strftime is too slow to ever finish on large tables” (Incorrect): It finishes, but it has to read every row.
• “The column is wrapped in a function; use a range on the raw column” is correct.

  The index stores placed_at values in order, not the year extracted from them. Applying a function to every row means the database can't use that order. placed_at >= '2026-01-01' AND placed_at < '2027-01-01' can use the index.

  • General rule: keep indexed columns bare in conditions.
  • Alternative: an index on the expression itself, in databases that support it.

📖 Revise: Lesson 4: Reading query plans

Why each option is right or wrong

• “A query that returns one more row than expected” (Incorrect): That's an off-by-one bug; N+1 is about the number of queries.
• “Adding one index more than the table needs” (Incorrect): It's about repeated queries, not indexes.
• “A table with one more column than it needs” (Incorrect): Column count isn't the issue.
• “One query for a list, then one more per item” is correct.

  Loading 50 orders, then running a separate query for each order's customer, makes 51 round trips. Each is quick, but together they're slow. A join or one WHERE id IN (...) query fetches everything at once.

  • Common with: ORMs and loops in code.
  • Spot it: in query logs showing many similar queries.

📖 Revise: Lesson 5: Performance habits

Why each option is right or wrong

• “Databases cancel any transaction after one second” (Incorrect): There's no fixed one-second limit; the problem is blocking.
• “Short transactions are encrypted; long ones aren't” (Incorrect): Length doesn't affect encryption.
• “Long transactions are rolled back automatically” (Incorrect): They aren't rolled back for being long; they block others.
• “Long transactions hold locks that block others” is correct.

  While a transaction is open, rows it changed may be locked. If it waits on a slow network call or a person, other requests touching those rows wait too. Do slow work before or after, and keep the transaction to the database steps.

  • Don't: call external APIs inside a transaction.
  • Do: gather data first, then begin, write and commit quickly.

📖 Revise: Lesson 5: Performance habits