🔴 Transactions

Transactions: Think of a transaction as a wedding ceremony: nobody is married until the "I do" comes from both sides, and if one side walks out mid-way the ceremony isn't half-do

Think of a transaction as a wedding ceremony: nobody is married until the "I do" comes from both sides, and if one side walks out mid-way the ceremony isn't half-done — it is treated as never having happened. A bank transfer works the same way: money leaving account A and arriving in account B is a single indivisible whole; if the power dies in between, the database refuses the half-finished state and `ROLLBACK` restores the starting point. But if "undo it if it half-fails" is such an obvious idea, why can't we just do it in application code — catch the exception and issue a compensating UPDATE? You can't, and the reason is worth sitting with: the compensating code can crash too. If the process dies right after the money left, there is nobody left alive to run that reversal. A transaction's real power is that it moves the guarantee **out of your code** and into the database's own recovery log (WAL/redo) — where the thing keeping the promise is not the process that crashed. Java's `synchronized` block looks similar on the surface, and the difference matters: `synchronized` gives you **isolation** only (no two threads inside at once). It gives no atomicity and no durability — if the JVM dies, half-done work stays half-done. A transaction gives all four ACID properties: Atomic, Consistent, Isolated, Durable. In QA work transactions matter most when preparing test data, and that is exactly where they get forgotten: a session opened with `BEGIN` and never closed with `COMMIT`/`ROLLBACK` leaves the table locked, and every other test touching that table dies on timeout. That is the classic cause of "40 tests all failed at once last night" — none of the tests were broken; one of them never released a lock. The inverse is a technique in its own right: run the test inside a transaction and `ROLLBACK` at the end, and the database is restored byte-for-byte to its pre-test state.

Transactions — ACID Properties

Micro Lab: SQL — GROUP BY / CTE / Window

Replace the TODO line with the critical line from the expected solution. This is not a real runtime; the goal is to reinforce writing the correct structure in a controlled way.

Step by Step: SQL — GROUP BY / CTE / Window

Aggregation or Window?

Determine: aggregation (summarizing) or Window (row-by-row computing)?

For aggregation, select the summary column with GROUP BY

Apply condition on groups with HAVING — like WHERE but after aggregation

Make modular with CTE

Modularize query with WITH cte_name AS (...) SELECT ... FROM cte_name

Add OVER(PARTITION BY)

For Window, use SUM(col) OVER (PARTITION BY partition ORDER BY sort)

What is the SQL GROUP BY and Window Function query writing order?