--- title: "13 — Transactions, ACID, and Window Functions: Correct Writes, Per-Row Analytics" uid: transactions-acid-window-functions tags: ["sql", "begin", "lead", "roadmap:sql", "commit", "lag", "rank", "savepoint", "rollback", "window-functions", "row-number", "acid", "transactions"] excerpt: "Transactions make a group of writes all-or-nothing; window functions compute across rows without collapsing them. The step where SQL graduates from rows to correctness." date: 2026-08-13T03:27:36+0000 source: https://www.aveshina.my.id/en/blog/transactions-acid-window-functions --- 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. ```figure ``` ## 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](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](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](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](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/](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](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/](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/](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](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/](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](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](https://www.codecademy.com/resources/docs/sql/window-functions/lead) ```quiz Q: What does the A in ACID guarantee for a transaction? - Atomicity: every operation in the transaction happens, or none do - Availability: the database is always reachable correct: 0 explain: Atomicity means the transaction is indivisible — if any part fails, the whole thing rolls back, so partial writes never persist. Q: What does ROLLBACK do? - Undoes all changes made since BEGIN (or back to a SAVEPOINT) - Saves all changes permanently correct: 0 explain: ROLLBACK reverts the transaction's changes, restoring the prior state. COMMIT is what persists them. Q: What distinguishes a window function from an aggregate with GROUP BY? - A window function collapses rows to one per group - A window function keeps every row and adds a computed column correct: 1 explain: Aggregates with GROUP BY collapse rows into summaries. Window functions compute over a frame of related rows but keep every original row in the output. Q: Two rows tie for rank 1. What rank does the next row get under RANK() vs DENSE_RANK()? - RANK() gives 3, DENSE_RANK() gives 2 - Both give 2 correct: 0 explain: RANK skips on ties (1, 1, 3); DENSE_RANK does not (1, 1, 2). ROW_NUMBER would give 1, 2, 3 with no ties. Q: You want each order's difference from the previous order's total. The clean tool is… - LAG(total) OVER (ORDER BY placed_at) - a self-join with GROUP BY correct: 0 explain: LAG reaches to the previous row in the ordered partition without a self-join, expressing the delta directly. Self-joins work but read worse and often optimize worse. ```