19-transactions-and-locks.sql

-- ============================================================
-- SQL Masterclass
-- Chapter 19: Transactions & Locks
-- ============================================================
-- Level: Expert 
-- Dependencies: PostgreSQL
-- 
-- Concepts Covered:
-- 1. ACID Properties (Atomicity, Consistency, Isolation, Durability)
-- 2. Explicit Transaction Blocks (BEGIN, COMMIT, ROLLBACK)
-- 3. Row-Level Locking (SELECT ... FOR UPDATE)
-- ============================================================

-- ============================================================
-- 1. Handling Failures with ROLLBACK
-- ============================================================
-- When processing E-Commerce orders, you must deduct inventory
-- AND create an invoice. If one succeeds but the other fails,
-- data is corrupted! Transactions guarantee all-or-nothing execution.

-- Create simple account balances for a theoretical refund:
DROP TABLE IF EXISTS user_balances;
CREATE TABLE user_balances (
    user_id INT PRIMARY KEY,
    name VARCHAR(50),
    balance NUMERIC(10,2) CHECK (balance >= 0)
);

INSERT INTO user_balances VALUES 
(1, 'Alice', 100.00),
(2, 'Bob', 50.00);

-- 1a. The Happy Path (COMMIT)
-- Bob sends $20 to Alice
BEGIN;

UPDATE user_balances SET balance = balance - 20.00 WHERE user_id = 2; -- Bob
UPDATE user_balances SET balance = balance + 20.00 WHERE user_id = 1; -- Alice

COMMIT;

-- Verify the money moved correctly
SELECT * FROM user_balances ORDER BY user_id;


-- 1b. The Failure Path (ROLLBACK)
-- Alice tries to send $200 back to Bob, but she only has $120.
-- Since we put a CHECK constraint `(balance >= 0)`, the query will fail!
BEGIN;

-- Try giving Bob $200
UPDATE user_balances SET balance = balance + 200.00 WHERE user_id = 2;

-- The next statement will ERROR, throwing an Exception!
-- UPDATE user_balances SET balance = balance - 200.00 WHERE user_id = 1;

-- If we simply try to SELECT now, Postgres will refuse because
-- the transaction block is "aborted" and requires a rollback.
ROLLBACK;

-- Verify Bob did NOT get the $200 for free! The DB stays consistent.
SELECT * FROM user_balances ORDER BY user_id;


-- ============================================================
-- 2. Concurrency and Row-Level Locks
-- ============================================================
-- Imagine Alice and Charlie both try to buy the last pair of shoes
-- at the EXACT same millisecond.
-- `SELECT inventory` returns `1` for both of them, they both check out,
-- and now your inventory is `-1`.

-- 2a. The SELECT FOR UPDATE Lock
-- When you `SELECT ... FOR UPDATE`, Postgres physically locks the row.
-- Any other transaction trying to read that row for an update is FORCED
-- to wait until you hit `COMMIT` or `ROLLBACK`.

BEGIN;

-- Grab the lock on Alice's row specifically. Nobody else can touch Alice.
SELECT balance FROM user_balances 
WHERE user_id = 1 
FOR UPDATE;

-- Simulate processing a payment, verifying stock...
-- If a second user runs this exact script right now, their query will hang!

UPDATE user_balances SET balance = balance - 5.00 WHERE user_id = 1;

COMMIT; 
-- The lock is released! Any waiting transactions instantly fire.


-- ============================================================
-- 3. Deadlocks
-- ============================================================
-- A deadlock occurs when Transaction A locks Row 1 and needs Row 2,
-- while Transaction B locks Row 2 and needs Row 1. Both wait forever.
-- Postgres detects this cycle automatically and kills one query to save the DB.

/* To reproduce this manually, you would open TWO separate terminal panes:

-- Terminal A
BEGIN;
UPDATE user_balances SET balance = balance - 1 WHERE user_id = 1;

-- Terminal B
BEGIN;
UPDATE user_balances SET balance = balance - 1 WHERE user_id = 2;

-- Terminal A
UPDATE user_balances SET balance = balance + 1 WHERE user_id = 2; -- HANGS, waiting for Terminal B

-- Terminal B
UPDATE user_balances SET balance = balance + 1 WHERE user_id = 1; -- DEADLOCK DETECTED!

Postgres will cancel Terminal B's query and let Terminal A finish.
*/