SQL Fundamentals: Joins, NULL, Aggregate Functions, and Subqueries

Part 17 of 18 · ~3 min

Transactions and Why They Matter Here

Slide five, finally: the payroll correction log. Two things have been sitting unresolved since early in this reference, and they both get fixed today, in one motion. First, Dave's manager confirmed his offer — $65,000 — and payroll is ready to enter it. Second, someone actually went and looked into the pay-equity flag from chapter two, and part of it turned out to be explainable: when Bob's starting number got bumped by $1,000 during an offer negotiation, the matching adjustment for Alice was approved the same day and never made it into her row. That's not the whole ten-thousand-dollar gap — Bob still earns noticeably more than Alice once this lands, and that remainder is now a real question for a manager to look into, not a data bug — but the $1,000 piece was an unambiguous clerical miss, so it's the piece going into payroll today. Both fixes are going into the same batch.

Three separate UPDATEs, and payroll needs all three to land together or not at all — there's no acceptable middle state where Dave's number is set but Alice's correction never went through. That's what a transaction buys you: wrap a batch of statements between BEGIN and COMMIT, and the database guarantees the whole batch takes effect as a single indivisible unit, or none of it does. Anyone else querying the table mid-batch sees either the world before the batch started or the world after it finished — never a half-applied snapshot with statement two done and statement three still pending.

BEGIN TRANSACTION;
UPDATE Employees SET salary = 65000 WHERE id = 4;                  -- Dave, entered for the first time
UPDATE Employees SET salary = salary - 1000 WHERE id = 2;          -- Bob: reverse the un-mirrored bump
UPDATE Employees SET salary = salary + 1000 WHERE id = 1;          -- Alice: apply the correction she should have gotten
COMMIT;

id | name  | salary
---|-------|--------
1  | Alice | 51000
2  | Bob   | 59000
3  | Carol | 70000
4  | Dave  | 65000
5  | Eve   | 55000

Picture the alternative for a second. Say the connection to the database drops right after Bob's row updates but before Alice's does. Without a transaction wrapping the batch, you'd be left staring at a table where Bob's $1,000 has already vanished, Dave finally has a number, and Alice's matching correction simply never happened — a state nobody asked for and nobody would have chosen, sitting there indefinitely until someone notices and manually untangles it. Inside a transaction, that same dropped connection undoes Dave's entry and Bob's reduction right along with the Alice update that never got to run, snapping the table back to exactly where it stood before BEGIN. Either all three land, or the table looks like none of them were ever attempted — there's no third outcome available.

Step back and this ties everything else in this reference together: every one of the five slides only reports the truth if the write that produced its underlying data was itself trustworthy. You can get every join, every NULL check, and every aggregate exactly right and still read garbage, if whatever wrote that data got interrupted partway through. It's not hypothetical, either — payroll could easily be mid-edit the exact moment next Monday's report query fires. Exactly how much of that in-progress write a SELECT running at the same time gets to see is governed by the transaction's isolation level, and that's a big enough subject to earn its own dedicated treatment: the ACID guarantees article covers it in full.

And with that, Dave has a real salary and the report has a clean run. Next Monday, Employees will have a new row entirely NULL-free of surprises — until someone new starts.