🔒 Transactions & Concurrency

-- Start a transaction
BEGIN;

-- Do some work
INSERT INTO orders (customer_id, amount) VALUES (42, 299.99);
UPDATE inventory SET stock = stock - 1 WHERE product_id = 101;

-- Everything looks good? Save it!
COMMIT;

BEGIN;

UPDATE employees SET salary = 0;
-- OH NO! I forgot the WHERE clause! Everyone's salary is now 0!

ROLLBACK;
-- Phew! Nothing happened. All salaries are back to normal.

BEGIN;

INSERT INTO orders (customer_id, amount) VALUES (1, 100);
-- ✔ Order created successfully

SAVEPOINT before_discount;

UPDATE orders SET amount = amount * 0.5 WHERE customer_id = 1;
-- Hmm, 50% discount is too much. Let's undo just THIS part.

ROLLBACK TO before_discount;
-- The order INSERT is still intact! Only the discount was undone.

UPDATE orders SET amount = amount * 0.9 WHERE customer_id = 1;
-- 10% discount instead. Much better.

COMMIT;
-- Both the order and the 10% discount are now permanent.

-- Set for the current transaction only
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ... your queries here ...
COMMIT;

-- Set for the entire session
SET default_transaction_isolation = 'serializable';

-- You can actually SEE these hidden columns!
SELECT xmin, xmax, id, name, salary
FROM employees
LIMIT 3;

-- Manual vacuum (rarely needed — autovacuum handles it)
VACUUM employees;

-- Vacuum with extra info
VACUUM VERBOSE employees;

-- VACUUM FULL reclaims disk space (locks the table — use sparingly!)
VACUUM FULL employees;

-- SELECT FOR UPDATE: Lock specific rows for modification
BEGIN;

SELECT * FROM products
WHERE id = 42
FOR UPDATE;
-- This row is now LOCKED. Nobody else can UPDATE or DELETE it
-- until we COMMIT or ROLLBACK.

-- Check stock and process the order safely
UPDATE products SET stock = stock - 1 WHERE id = 42;

COMMIT;
-- Lock released!

-- Job queue pattern: grab the next available task, skip locked ones
BEGIN;

SELECT * FROM tasks
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- If the first pending task is locked by another worker, skip it
-- and grab the next one. No waiting!

UPDATE tasks SET status = 'processing' WHERE id = 123;

COMMIT;

-- Explicitly lock an entire table (very rare)
BEGIN;
LOCK TABLE accounts IN EXCLUSIVE MODE;
-- Nobody else can write to accounts until we COMMIT
-- Use extremely sparingly!
COMMIT;

-- Advisory locks are custom, application-defined locks
-- They don't lock any table or row — they lock a number/key

-- Acquire an advisory lock (blocks until available)
SELECT pg_advisory_lock(12345);
-- Do critical work that only one process should do at a time
SELECT pg_advisory_unlock(12345);

-- Try to acquire without blocking (returns true/false)
SELECT pg_try_advisory_lock(12345);
-- Returns: true (got the lock) or false (someone else has it)

-- Transaction A (running in one session)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Locks row 1
-- ... pause ...
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- WAITS for row 2!

-- Transaction B (running in another session at the same time)
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- Locks row 2
-- ... pause ...
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- WAITS for row 1!

-- DEADLOCK! A waits for B, B waits for A.
-- PostgreSQL detects this within ~1 second and kills one transaction:
-- ERROR: deadlock detected

-- SAFE: Always lock the lower ID first
CREATE PROCEDURE safe_transfer(
    from_id INTEGER,
    to_id INTEGER,
    amount NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    first_id  INTEGER := LEAST(from_id, to_id);
    second_id INTEGER := GREATEST(from_id, to_id);
BEGIN
    -- Lock both rows in a consistent order (lowest ID first)
    PERFORM * FROM accounts WHERE id IN (first_id, second_id)
    ORDER BY id
    FOR UPDATE;

    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
END;
$$;

-- Insert a new user, or update their email if they already exist
INSERT INTO users (username, email, last_login)
VALUES ('john_doe', 'john@example.com', NOW())
ON CONFLICT (username)
DO UPDATE SET
    email = EXCLUDED.email,
    last_login = EXCLUDED.last_login;

-- BAD: Not idempotent — each run adds another $100
UPDATE accounts SET balance = balance + 100 WHERE id = 1;

-- GOOD: Idempotent — uses a unique transaction reference
INSERT INTO transactions (id, account_id, amount)
VALUES ('txn-abc-123', 1, 100)
ON CONFLICT (id) DO NOTHING;
-- If this transaction was already processed, it's silently skipped

-- Application-side retry pattern (Python-like pseudocode)
-- max_retries = 3
-- for attempt in range(max_retries):
--     try:
--         BEGIN
--         ... do work ...
--         COMMIT
--         break  # Success! Exit the loop
--     except SerializationError:
--         ROLLBACK
--         sleep(random(0.01, 0.1))  # Brief random delay
--         continue  # Try again

-- Check stock AND decrement in one atomic query
UPDATE products
SET stock = stock - 1
WHERE id = 42
  AND stock > 0
RETURNING id, name, stock;

-- If stock was already 0, no rows are updated
-- RETURNING tells you what happened — empty = out of stock

BEGIN;

-- Process each order in a batch, skipping failures
SAVEPOINT order_1;
INSERT INTO orders VALUES (1, 100, NOW());
-- success!

SAVEPOINT order_2;
INSERT INTO orders VALUES (2, -50, NOW());
-- fails! (maybe CHECK constraint on positive amount)
ROLLBACK TO order_2;

SAVEPOINT order_3;
INSERT INTO orders VALUES (3, 200, NOW());
-- success!

COMMIT;
-- Orders 1 and 3 are saved. Order 2 was rolled back.