13 — Transactions, ACID, and Window Functions: Correct Writes, Per-Row Analytics

The part where SQL graduates from "read and write rows" to "guarantee writes are correct, and compute across rows without flattening them" is also where I used to mush two ideas together. The separation: a transaction groups writes so they apply all-or-nothing, and a window function computes over a sliding set of rows while keeping every row in the output. [1][6]

The framing that clicked is that these solve two different "many rows" problems. Transactions solve correctness across multiple writes — a transfer debits one account and credits another, and both must happen or neither. Window functions solve analytics across multiple reads — for each order, how does its total compare to the previous order's, and what's its rank within its region. Aggregates collapse rows into one summary; window functions keep the rows and add a computed column beside each.

Transactions and ACID

A transaction groups one or more operations into a single unit of work [1]. Its defining property is ACID [2][3]:

• Atomicity — all operations in the transaction happen, or none do. A failure mid-transaction rolls back everything.
• Consistency — a transaction moves the database from one valid state to another, respecting all constraints.
• Isolation — concurrent transactions don't see each other's intermediate states (depending on the isolation level).
• Durability — once committed, the change survives crashes.

The transfer example is the canonical case: debit one account, credit another. Without a transaction, a crash after the debit leaves money destroyed. Inside BEGIN ... COMMIT, both succeed or both roll back — atomicity guarantees it.

BEGIN, COMMIT, ROLLBACK, SAVEPOINT

The transaction control verbs [4][5][7]:

• BEGIN — starts a transaction. Subsequent statements are part of it until COMMIT or ROLLBACK.
• COMMIT — saves all changes made in the transaction, making them permanent and visible to others [5].
• ROLLBACK — undoes all changes made since BEGIN, restoring the prior state [4].
• SAVEPOINT — a named checkpoint inside a transaction. I can ROLLBACK TO savepoint to undo only part of the work without aborting the whole transaction [7].

BEGIN;
  UPDATE accounts SET bal = bal - 100 WHERE id = 1;
  SAVEPOINT after_debit;
  UPDATE accounts SET bal = bal + 100 WHERE id = 2;
  -- if something looks wrong:
  ROLLBACK TO after_debit;   -- undoes only the credit
COMMIT;

SAVEPOINT is the tool for long transactions where I want to recover from a mid-step error without throwing away everything before it.

Window functions — analytics without collapsing

This is the part I had to take slowly. A window function performs a calculation across a set of rows related to the current row, and crucially, it keeps every row in the output — it adds a computed column rather than collapsing rows the way GROUP BY does [6][8].

The syntax has two parts: the function, and an OVER (...) clause that defines the "window" — which rows, in what order:

SELECT name, region, total,
       RANK()       OVER (PARTITION BY region ORDER BY total DESC) AS region_rank,
       LAG(total)   OVER (PARTITION BY region ORDER BY placed_at)  AS prev_total,
       SUM(total)   OVER (PARTITION BY region)                    AS region_total
FROM orders;

• PARTITION BY region divides rows into buckets (one per region); the function runs within each.
• ORDER BY total DESC defines the ordering the function uses inside each partition.
• The result keeps all original columns and adds the computed column beside each row.

The contrast with aggregates is the whole point: SUM(total) GROUP BY region collapses to one row per region; SUM(total) OVER (PARTITION BY region) keeps every order and appends the region total next to each — so I can compute each order's share of its region in the same query.

The ranking and offset functions

The window functions the roadmap flags all operate within this OVER (...) frame:

• ROW_NUMBER() — a unique sequential integer per row within its partition (1, 2, 3…), no ties [10].
• RANK() — ranks with gaps on ties: two rows tied at 1 mean the next is 3 [7].
• DENSE_RANK() — ranks without gaps: two rows tied at 1 mean the next is 2 [8].
• LAG(col) / LEAD(col) — reach to the previous / next row in the partition without a self-join, for deltas and trend lines [11][12].

SELECT name, total,
       total - LAG(total) OVER (ORDER BY placed_at) AS delta_from_prev
FROM orders;

That single line — "the difference between this order and the previous one" — would otherwise need a self-join. Window functions express it directly.

How I use this

Two habits. For transactions: any write that must stay consistent with another write goes inside BEGIN ... COMMIT, and I reach for SAVEPOINT inside long transactions to recover from a recoverable step without aborting the whole thing. For window functions: when I catch myself about to self-join a table to itself to compare a row to its neighbor, or to compute a running total, I stop and write a window function instead — LAG/LEAD for offsets, SUM() OVER (...) for running totals, RANK()/ROW_NUMBER() for "top N per group." They read more clearly and the optimizer handles them better than the self-join they replace.

References

[1] TutorialsPoint, "SQL Transactions," tutorialspoint.com, 2024. [Online]. Available: https://www.tutorialspoint.com/sql/sql-transactions.htm

[2] MongoDB, "A Guide to ACID Properties in Database Management Systems," mongodb.com, 2024. [Online]. Available: https://www.mongodb.com/resources/basics/databases/acid-transactions

[3] YouTube, "ACID Explained: Atomic, Consistent, Isolated & Durable," 2023. [Online]. Available: https://www.youtube.com/watch?v=yaQ5YMWkxq4

[4] DigitalOcean, "SQL COMMIT and ROLLBACK," digitalocean.com, 2024. [Online]. Available: https://www.digitalocean.com/community/tutorials/sql-commit-sql-rollback

[5] Byjus, "Difference between COMMIT and ROLLBACK in SQL," byjus.com, 2024. [Online]. Available: https://byjus.com/gate/difference-between-commit-and-rollback-in-sql/

[6] Mode Analytics, "SQL Window Functions," mode.com, 2024. [Online]. Available: https://mode.com/sql-tutorial/sql-window-functions

[7] SQLShack, "Overview of SQL RANK Functions," sqlshack.com, 2024. [Online]. Available: https://www.sqlshack.com/overview-of-sql-rank-functions/

[8] SQLTutorial, "SQL DENSE_RANK," sqltutorial.org, 2024. [Online]. Available: https://www.sqltutorial.org/sql-window-functions/sql-dense_rank/

[9] YouTube, "SQL Window Functions in 10 Minutes," 2023. [Online]. Available: https://www.youtube.com/watch?v=y1KCM8vbYe4

[10] SQLTutorial, "SQL ROW_NUMBER," sqltutorial.org, 2024. [Online]. Available: https://www.sqltutorial.org/sql-window-functions/sql-row_number/

[11] DataCamp, "Understanding the LAG function in SQL," datacamp.com, 2024. [Online]. Available: https://www.datacamp.com/tutorial/sql-lag

[12] Codecademy, "SQL LEAD," codecademy.com, 2024. [Online]. Available: https://www.codecademy.com/resources/docs/sql/window-functions/lead