SQL Transactions & ACID Properties Explained — Internals, Isolation Levels and Production Pitfalls

-- Scenario: Transfer $500 from Alice's account to Bob's account.
-- Both the debit and credit must succeed together, or neither applies.

BEGIN; -- Start the transaction envelope

-- Step 1: Deduct $500 from Alice. If her balance is insufficient,
-- we want the whole operation to roll back, not just skip this step.
UPDATE accounts
   SET balance = balance - 500
 WHERE account_id = 'alice_001'
   AND balance >= 500; -- Guard against overdraft at the SQL level

-- ROW_COUNT() / GET DIAGNOSTICS lets us check whether the UPDATE
-- actually matched a row. If Alice had insufficient funds, 0 rows
-- were affected and we need to abort manually.
DO $$
BEGIN
    IF NOT FOUND THEN  -- PostgreSQL sets FOUND after each DML statement
        RAISE EXCEPTION 'Insufficient funds or account not found for alice_001';
    END IF;
END;
$$;

-- Step 2: Credit $500 to Bob. This only runs if Step 1 succeeded.
UPDATE accounts
   SET balance = balance + 500
 WHERE account_id = 'bob_002';

-- Step 3: Log the transfer for audit purposes.
INSERT INTO transfer_audit (from_account, to_account, amount, transferred_at)
VALUES ('alice_001', 'bob_002', 500.00, NOW());

-- Only if ALL three steps succeeded do we make the changes permanent.
-- The WAL flush to disk happens here — this is what makes it durable.
COMMIT;

-- If ANY step raises an exception, PostgreSQL automatically rolls back
-- the entire transaction. You can also trigger this explicitly:
-- ROLLBACK;

-- This demo shows a classic non-repeatable read at READ COMMITTED
-- and how REPEATABLE READ prevents it.
-- Run Session A and Session B in two separate database connections.

-- ============================================================
-- SETUP (run once before the demo)
-- ============================================================
CREATE TABLE product_inventory (
    product_id   SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    stock_count  INT NOT NULL
);

INSERT INTO product_inventory (product_name, stock_count)
VALUES ('Mechanical Keyboard', 10);

-- ============================================================
-- SESSION A  (e.g. your application processing an order)
-- ============================================================

-- Default isolation in PostgreSQL is READ COMMITTED.
-- Switch to REPEATABLE READ to prevent non-repeatable reads.
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- First read: sees stock_count = 10
SELECT product_name, stock_count
  FROM product_inventory
 WHERE product_id = 1;
-- Output: Mechanical Keyboard | 10

-- *** PAUSE HERE. Switch to Session B and run its block. ***

-- Second read: at READ COMMITTED this returns 3 (another transaction
-- sold 7 units and committed between our two SELECTs — non-repeatable!).
-- At REPEATABLE READ this still returns 10, because PostgreSQL took
-- a snapshot at the start of our transaction. Session B's commit
-- is invisible to us until we start a new transaction.
SELECT product_name, stock_count
  FROM product_inventory
 WHERE product_id = 1;
-- READ COMMITTED output:  Mechanical Keyboard | 3  (data changed!)
-- REPEATABLE READ output: Mechanical Keyboard | 10 (stable snapshot)

COMMIT;

-- ============================================================
-- SESSION B  (another user buying 7 keyboards simultaneously)
-- ============================================================
BEGIN;

UPDATE product_inventory
   SET stock_count = stock_count - 7  -- Sell 7 units
 WHERE product_id = 1;

COMMIT; -- This commits WHILE Session A is still running

-- ============================================================
-- SERIALIZABLE — Preventing Phantom Reads
-- ============================================================
-- A phantom read happens when a new ROW appears between two SELECTs
-- in the same transaction. Example: an ORDER audit that totals
-- all orders, while another transaction inserts a new order mid-audit.

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Count all high-value orders
SELECT COUNT(*), SUM(order_total)
  FROM orders
 WHERE order_total > 1000;

-- Without SERIALIZABLE, a new order > $1000 inserted and committed
-- by another session between this and the next SELECT would show up,
-- making your report inconsistent.

SELECT order_id, order_total
  FROM orders
 WHERE order_total > 1000
 ORDER BY order_total DESC;

COMMIT;

-- Demonstrates SAVEPOINTs for partial rollback in a bulk import scenario,
-- and shows consistency enforcement via foreign key constraints.

-- ============================================================
-- SCHEMA (demonstrates consistency enforcement)
-- ============================================================
CREATE TABLE departments (
    dept_id   SERIAL PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE employees (
    emp_id    SERIAL PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    dept_id   INT NOT NULL REFERENCES departments(dept_id), -- FK enforces consistency
    salary    NUMERIC(10,2) CHECK (salary > 0)              -- CHECK constraint
);

INSERT INTO departments (dept_name) VALUES ('Engineering'), ('Marketing');

-- ============================================================
-- SAVEPOINT DEMO — Bulk import with recoverable per-row errors
-- ============================================================
BEGIN;

-- Insert first employee successfully
SAVEPOINT before_emp_1;
INSERT INTO employees (full_name, dept_id, salary)
VALUES ('Priya Sharma', 1, 95000.00); -- dept_id 1 = Engineering, valid
-- Success — keep this savepoint active

-- Attempt to insert an employee with an invalid dept_id (FK violation)
SAVEPOINT before_emp_2;
INSERT INTO employees (full_name, dept_id, salary)
VALUES ('Carlos Mendez', 999, 80000.00); -- dept_id 999 does NOT exist!
-- This will ERROR. We catch it at the application layer and roll back
-- ONLY to before_emp_2, not the entire transaction.
ROLLBACK TO SAVEPOINT before_emp_2;
-- Priya's insert is still alive. Carlos's failed insert is undone.
-- Log the failure for the import report:
INSERT INTO import_error_log (attempted_name, reason, logged_at)
VALUES ('Carlos Mendez', 'dept_id 999 not found', NOW());

-- Insert third employee successfully
SAVEPOINT before_emp_3;
INSERT INTO employees (full_name, dept_id, salary)
VALUES ('Aisha Okonkwo', 2, 72000.00); -- dept_id 2 = Marketing, valid

-- Attempt a negative salary — violates CHECK constraint
SAVEPOINT before_emp_4;
INSERT INTO employees (full_name, dept_id, salary)
VALUES ('Tom Briggs', 1, -5000.00); -- Negative salary, CHECK fails
ROLLBACK TO SAVEPOINT before_emp_4;
INSERT INTO import_error_log (attempted_name, reason, logged_at)
VALUES ('Tom Briggs', 'Salary must be positive', NOW());

-- Release savepoints we no longer need (frees internal resources)
RELEASE SAVEPOINT before_emp_1;
RELEASE SAVEPOINT before_emp_3;

-- Commit the partial success: Priya and Aisha are inserted,
-- Carlos and Tom are logged as errors but NOT in employees table.
COMMIT;

-- Verify results
SELECT e.full_name, d.dept_name, e.salary
  FROM employees e
  JOIN departments d ON e.dept_id = d.dept_id
 ORDER BY e.emp_id;

SELECT attempted_name, reason FROM import_error_log;