-- Transfer $100 from Alice to Bob
BEGIN;  -- Start transaction (like opening a package)

-- Step 1: Deduct from Alice
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Step 2: Add to Bob
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;  -- Complete transaction (close the package, make it permanent)

-- Step 1 succeeds
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- ✅ Works

-- Step 2 fails (maybe Bob's account doesn't exist)
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- ❌ Error!

-- Result: Alice lost $100, but Bob didn't get it! Money disappeared! 💸

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- ✅ Works
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- ❌ Error!
ROLLBACK;  -- Undoes step 1 automatically!

-- Result: Alice keeps her $100. No money lost! ✅

-- Classic example: Money transfer
BEGIN;  -- Start transaction

-- Deduct from sender
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Add to receiver  
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;  -- Make changes permanent
-- If anything fails, ROLLBACK undoes everything

-- Monitor bloat and dead tuples
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum 
FROM pg_stat_user_tables;

-- Constraints enforce consistency
CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    balance DECIMAL(12, 2) NOT NULL,
    CONSTRAINT positive_balance CHECK (balance >= 0)  -- Business rule!
);

BEGIN;
UPDATE accounts SET balance = balance - 150 WHERE id = 1;
-- If account 1 has $100, this would make balance = -$50
-- CHECK constraint prevents this! Transaction fails.
ROLLBACK;  -- Automatic rollback due to constraint violation

┌─────────────────────────────────────────────────────────────────────────────┐
│                          ISOLATION LEVELS                                   │
├────────────────────┬─────────────┬──────────────────┬─────────────────────┤
│ Isolation Level    │ Dirty Read  │ Non-Repeatable   │ Phantom Read        │
│                    │             │ Read             │                     │
├────────────────────┼─────────────┼──────────────────┼─────────────────────┤
│ READ UNCOMMITTED   │ Possible    │ Possible         │ Possible            │
│ READ COMMITTED     │ Prevented   │ Possible         │ Possible            │
│ REPEATABLE READ    │ Prevented   │ Prevented        │ Possible*           │
│ SERIALIZABLE       │ Prevented   │ Prevented        │ Prevented           │
├────────────────────┴─────────────┴──────────────────┴─────────────────────┤
│ * PostgreSQL's REPEATABLE READ also prevents phantoms (uses MVCC)         │
│                                                                            │
│ Higher isolation = More protection = More overhead/locking                 │
└────────────────────────────────────────────────────────────────────────────┘

-- Transaction 1
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT balance FROM accounts WHERE id = 1;  -- Returns 1000
-- (Transaction 2 updates balance to 1200 and COMMITs here)
SELECT balance FROM accounts WHERE id = 1;  -- Returns 1200 (changed!)
-- Same query, same transaction, different result.
-- This is NOT a bug -- it is the defined behavior of READ COMMITTED.
-- Each statement gets a NEW snapshot of the database.

COMMIT;

-- Transaction 1
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;  -- Returns 1000
-- (Transaction 2 updates and commits here)
SELECT balance FROM accounts WHERE id = 1;  -- Still returns 1000!
-- We see the snapshot from when our transaction started
COMMIT;

-- Write conflict example
-- Transaction 1                    -- Transaction 2
BEGIN;                              BEGIN;
SET TRANSACTION ISOLATION           SET TRANSACTION ISOLATION
  LEVEL REPEATABLE READ;              LEVEL REPEATABLE READ;
  
UPDATE accounts                     
SET balance = balance + 100         
WHERE id = 1;                       
                                    UPDATE accounts
                                    SET balance = balance + 50
                                    WHERE id = 1;
                                    -- ERROR: could not serialize access
                                    -- due to concurrent update
COMMIT;
                                    ROLLBACK;  -- Must retry

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Transactions behave as if run one after another.
-- PostgreSQL does NOT actually run them serially (that would be slow).
-- Instead, it uses SSI to detect when concurrent execution would produce
-- a result different from some serial ordering, and aborts one transaction.
-- Your application MUST be prepared to retry aborted transactions.
COMMIT;

-- FOR UPDATE: Lock rows for modification
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Other transactions block on UPDATE to this row
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- FOR SHARE: Lock rows for reading (prevents updates)
BEGIN;
SELECT * FROM products WHERE id = 10 FOR SHARE;
-- Allows other SELECTs, blocks UPDATEs
COMMIT;

-- SKIP LOCKED: Don't wait, skip locked rows
SELECT * FROM jobs 
WHERE status = 'pending' 
ORDER BY created_at 
LIMIT 1 
FOR UPDATE SKIP LOCKED;
-- Perfect for job queues: take next available job.
-- Each worker grabs a different row without blocking.
-- This pattern eliminates the need for external queue systems
-- (like Redis or RabbitMQ) for simple task distribution.
-- Combined with a status column update and COMMIT, this gives you
-- exactly-once processing semantics within a single database.

-- NOWAIT: Fail immediately if locked
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row

-- Session-level advisory lock (held until session ends or explicitly released)
SELECT pg_advisory_lock(12345);  -- Acquire lock on key 12345
-- ... do work ...
SELECT pg_advisory_unlock(12345);

-- Transaction-level advisory lock (released at COMMIT/ROLLBACK)
SELECT pg_advisory_xact_lock(12345);
-- No explicit unlock needed

-- Try lock (don't wait)
SELECT pg_try_advisory_lock(12345);  -- Returns true/false

-- Use case: Prevent duplicate processing
SELECT pg_try_advisory_lock(hashtext('process-order-' || order_id::text));

**Preventing Deadlocks**:

```sql
-- Strategy 1: Lock in consistent order
-- Always lock lower ID first
BEGIN;
UPDATE accounts SET balance = balance - 100 
WHERE id = LEAST(1, 2);  -- Lock id=1 first
UPDATE accounts SET balance = balance + 100 
WHERE id = GREATEST(1, 2);  -- Then id=2
COMMIT;

-- Strategy 2: Lock all rows at once
BEGIN;
SELECT * FROM accounts 
WHERE id IN (1, 2) 
ORDER BY id 
FOR UPDATE;  -- Lock both in consistent order

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Strategy 3: Use short transactions
-- Don't hold locks while doing slow operations

-- PostgreSQL function with retry logic
CREATE OR REPLACE FUNCTION transfer_money(
    p_from_account INTEGER,
    p_to_account INTEGER,
    p_amount DECIMAL
) RETURNS BOOLEAN AS $$
DECLARE
    retry_count INTEGER := 0;
    max_retries INTEGER := 3;
BEGIN
    LOOP
        BEGIN
            -- Check sufficient balance
            IF (SELECT balance FROM accounts WHERE id = p_from_account) < p_amount THEN
                RAISE EXCEPTION 'Insufficient funds';
            END IF;
            
            -- Perform transfer
            UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_account;
            UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_account;
            
            -- Log transfer
            INSERT INTO transfers (from_account, to_account, amount, transferred_at)
            VALUES (p_from_account, p_to_account, p_amount, NOW());
            
            RETURN TRUE;
            
        EXCEPTION 
            WHEN serialization_failure OR deadlock_detected THEN
                retry_count := retry_count + 1;
                IF retry_count > max_retries THEN
                    RAISE;
                END IF;
                -- Exponential backoff
                PERFORM pg_sleep(0.1 * power(2, retry_count));
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Use idempotency key to prevent duplicate processing
CREATE TABLE payments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    idempotency_key VARCHAR(255) UNIQUE,  -- Client-provided key
    user_id INTEGER NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Insert with conflict handling
INSERT INTO payments (idempotency_key, user_id, amount, status)
VALUES ('client-req-12345', 1, 100.00, 'completed')
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING *;

-- If duplicate, return existing record
-- Application checks if RETURNING gave a row or needs to SELECT

-- Add version column
ALTER TABLE products ADD COLUMN version INTEGER NOT NULL DEFAULT 1;

-- Read current state
SELECT id, name, price, stock, version FROM products WHERE id = 123;
-- Returns: version = 5

-- Update with version check
UPDATE products 
SET price = 99.99, 
    version = version + 1
WHERE id = 123 AND version = 5;  -- Expected version

-- Check if update succeeded
-- If 0 rows affected: someone else updated, retry
-- If 1 row affected: success

-- Job queue table
CREATE TABLE jobs (
    id SERIAL PRIMARY KEY,
    payload JSONB NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    attempts INTEGER NOT NULL DEFAULT 0,
    locked_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_jobs_pending ON jobs(created_at) WHERE status = 'pending';

-- Worker: Get next job (doesn't block other workers)
BEGIN;

UPDATE jobs 
SET status = 'processing', 
    locked_at = NOW(),
    attempts = attempts + 1
WHERE id = (
    SELECT id FROM jobs 
    WHERE status = 'pending'
    ORDER BY created_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED  -- Skip jobs other workers are processing
)
RETURNING *;

-- Process the job...

-- Mark complete
UPDATE jobs SET status = 'completed', completed_at = NOW() WHERE id = ?;

COMMIT;

# ❌ BAD: Long transaction holding locks
with db.transaction():
    order = db.query("SELECT * FROM orders WHERE id = 1 FOR UPDATE")
    
    # External API call - could take seconds!
    payment_result = stripe.charge(order.total)
    
    if payment_result.success:
        db.execute("UPDATE orders SET status = 'paid' WHERE id = 1")
    
# Locks held for entire Stripe call duration!

# ✅ GOOD: Short transactions
order = db.query("SELECT * FROM orders WHERE id = 1")

# External call outside transaction
payment_result = stripe.charge(order.total)

if payment_result.success:
    with db.transaction():
        db.execute("""
            UPDATE orders 
            SET status = 'paid', payment_id = %s 
            WHERE id = 1 AND status = 'pending'
        """, [payment_result.id])

# ❌ BAD: Assumes success
db.execute("UPDATE inventory SET stock = stock - 1 WHERE product_id = 1")

# ✅ GOOD: Check result and handle conflicts
result = db.execute("""
    UPDATE inventory 
    SET stock = stock - 1 
    WHERE product_id = 1 AND stock > 0
    RETURNING stock
""")

if result.rowcount == 0:
    raise OutOfStockError("Product is out of stock")

# ❌ BAD: Inconsistent read without proper isolation
with db.transaction():
    total = db.query("SELECT SUM(amount) FROM line_items WHERE order_id = 1")
    # Another transaction adds a line item here!
    items = db.query("SELECT * FROM line_items WHERE order_id = 1")
    # items and total don't match!

# ✅ GOOD: Use REPEATABLE READ for consistent snapshot
with db.transaction(isolation_level='REPEATABLE READ'):
    total = db.query("SELECT SUM(amount) FROM line_items WHERE order_id = 1")
    items = db.query("SELECT * FROM line_items WHERE order_id = 1")
    # Both queries see the same snapshot