PostgreSQL — Topic 6: Transactions

6.1

ACID Properties

The four guarantees every transaction must uphold

▾

The Four Properties

ACID is not just theory — it's what makes PostgreSQL safe for money, orders, and any critical data. Every transaction in PostgreSQL guarantees all four.

A — Atomicity

All-or-nothing. Either every statement in the transaction succeeds, or none of them do. No half-done state ever reaches the database.

C — Consistency

The database moves from one valid state to another valid state. All constraints, foreign keys, and rules are enforced after each transaction.

I — Isolation

Concurrent transactions don't see each other's in-progress work. Each transaction behaves as if it runs alone — until committed.

D — Durability

Once committed, the data is safe — even if the server crashes immediately after. PostgreSQL writes to the WAL (Write-Ahead Log) before confirming.

ACID in Action — Bank Transfer Example

A money transfer is the classic ACID example. Debit one account, credit another — both must happen or neither should.

❌ Without a transaction

UPDATE accounts
  SET balance = balance - 500
  WHERE id = 1;
-- 💥 server crashes here!

UPDATE accounts
  SET balance = balance + 500
  WHERE id = 2;
-- ← never runs
-- Alice lost ₹500, Bob got nothing

✅ With a transaction

BEGIN;

UPDATE accounts
  SET balance = balance - 500
  WHERE id = 1;

UPDATE accounts
  SET balance = balance + 500
  WHERE id = 2;

COMMIT;
-- Both happen, or neither do ✅

🏦

Atomicity ensures if the crash happens mid-transaction, PostgreSQL automatically rolls back both statements on restart. Money is never lost.

6.2

Transaction Control

BEGIN · COMMIT · ROLLBACK · SAVEPOINT

▾

BEGIN

BEGIN (or START TRANSACTION) opens a transaction block. Everything after it is part of the same transaction until you COMMIT or ROLLBACK.

📌

Auto-commit: In PostgreSQL, every single SQL statement outside a BEGIN block is automatically wrapped in its own transaction and committed. So INSERT INTO ... without a BEGIN is already a transaction of one.

            
-- Both are valid ways to start a transaction
BEGIN;
START TRANSACTION;  -- same thing

-- Every statement after BEGIN is part of this transaction
INSERT INTO orders (user_id, status) VALUES (1, 'pending');
INSERT INTO order_items (order_id, product_id, qty) VALUES (lastval(), 7, 2);
UPDATE  inventory SET stock = stock - 2 WHERE product_id = 7;
          

COMMIT

COMMIT makes all changes in the transaction permanent. After COMMIT, other sessions can see the new data, and the changes survive a crash.

            
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;
-- ✅ Changes are now permanent and visible to all connections
          

ROLLBACK

ROLLBACK undoes everything done since BEGIN. The database goes back to exactly the state it was in before the transaction started.

            
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;

-- Oops! balance went negative — abort everything
SELECT balance FROM accounts WHERE id = 1;
-- returns: -200 ← not allowed!

ROLLBACK;
-- ✅ The UPDATE is completely undone. Balance is restored.

-- In application code (Python example):
-- try:
--     conn.execute("BEGIN")
--     conn.execute("UPDATE ...")
--     conn.execute("COMMIT")
-- except Exception:
--     conn.execute("ROLLBACK")  ← always rollback on error
          

SAVEPOINT

A SAVEPOINT is a checkpoint inside a transaction. You can roll back to a savepoint without abandoning the entire transaction. Think of it as a partial undo.

SAVEPOINT flow — partial rollback

BEGIN

Transaction starts

Step 1 — INSERT order

Order row created

SAVEPOINT after_order

Checkpoint saved here

Step 2 — UPDATE inventory FAILS

Stock went negative — error!

ROLLBACK TO after_order

Back to savepoint — order row is still there!

COMMIT

Order saved, inventory untouched

            
BEGIN;

-- Step 1: create the order
INSERT INTO orders (user_id, status) VALUES (1, 'pending')
RETURNING order_id;  -- returns: 9001

-- Save a checkpoint here
SAVEPOINT after_order;

-- Step 2: try to reduce inventory
UPDATE inventory SET stock = stock - 5 WHERE product_id = 7;
-- stock = -3 → violates a CHECK constraint, raises error

-- Roll back only to the savepoint (order is preserved!)
ROLLBACK TO SAVEPOINT after_order;

-- Try a smaller quantity
UPDATE inventory SET stock = stock - 2 WHERE product_id = 7;

-- Done — release savepoint (optional) and commit
RELEASE SAVEPOINT after_order;
COMMIT;
-- ✅ order 9001 exists, inventory reduced by 2
          

💡

Production use: SAVEPOINTs are heavily used in ORMs (like SQLAlchemy, Django ORM) to handle partial failures in batch operations without restarting the whole transaction.

Interactive — E-commerce Order Transaction

Simulate an order placement transaction step by step. Click each SQL statement in order.

🛒 Place Order: User buys 2x Product #7 (stock: 10)

📋 Transaction Steps

📊 Database State

                  orders: []

                  order_items: []

                  inventory #7: 10

                  payments: []

                  ─────────────────

                  txn status: none

Transaction Log

-- Click BEGIN to start the transaction

6.3

Isolation Levels

How much one transaction sees of another's in-progress work

▾

What is Isolation?

In a real system, hundreds of transactions run at the same time. Isolation determines what each transaction can see from other concurrent transactions. More isolation = safer but slower. Less isolation = faster but riskier.

Isolation Level	Dirty Read	Non-repeatable Read	Phantom Read	Performance
Read Uncommitted	Possible	Possible	Possible	Fastest
Read Committed ← PG default	Safe	Possible	Possible	Fast
Repeatable Read	Safe	Safe	PG: Safe*	Medium
Serializable	Safe	Safe	Safe	Slowest

* PostgreSQL's REPEATABLE READ uses snapshot isolation which also prevents phantom reads — stronger than the SQL standard requires.

Setting Isolation Levels

            
-- Set for the current transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;   -- default
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Or at the BEGIN statement (shorthand)
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- Set for the session (all future transactions)
SET default_transaction_isolation = 'repeatable read';

-- Check current level
SHOW transaction_isolation;
          

Explore Each Isolation Level

Click each level to see what it means, what problems it prevents, and when to use it in production.

6.4

Concurrency Problems

Dirty reads · Non-repeatable reads · Phantom reads

▾

Dirty Read

Transaction A reads data that Transaction B has written but not yet committed. If B rolls back, A has read data that never officially existed.

Transaction A (reader)

BEGIN;

-- reads balance = 5000
-- (Bob's tx not committed yet)
SELECT balance
FROM accounts WHERE id=1;
-- sees: 5000 ← DIRTY READ ❌
-- Bob's tx rolls back!
-- This 5000 never existed.

Transaction B (writer)

BEGIN;

UPDATE accounts
  SET balance = 5000
  WHERE id = 1;
-- not committed yet!


ROLLBACK;
-- ← balance is back to original

🛡️

PostgreSQL prevents dirty reads even at Read Committed (the default). Read Uncommitted in PG still behaves like Read Committed. You're safe by default.

Non-Repeatable Read

Transaction A reads the same row twice in one transaction and gets different values because Transaction B committed a change in between.

Transaction A (reporter)

BEGIN;

SELECT price FROM products
WHERE id = 42;
-- reads: ₹1000

-- (B commits its update here)

SELECT price FROM products
WHERE id = 42;
-- reads: ₹1200 ← DIFFERENT! ❌
-- Same query, same row,
-- different result.

Transaction B (price updater)

BEGIN;

UPDATE products
  SET price = 1200
  WHERE id = 42;

COMMIT;
-- ← A's second read
--    sees this change

🔧

Fix: Use REPEATABLE READ isolation. A's snapshot is frozen at the start — both reads return ₹1000 regardless of B.

Phantom Read

Transaction A runs the same query twice and gets different rows because Transaction B inserted (or deleted) rows that match A's WHERE clause.

Transaction A (auditor)

BEGIN;

SELECT COUNT(*) FROM orders
WHERE status = 'pending';
-- returns: 5

-- (B inserts and commits here)

SELECT COUNT(*) FROM orders
WHERE status = 'pending';
-- returns: 6 ← PHANTOM ❌
-- A new "phantom" row appeared

Transaction B (order service)

BEGIN;

INSERT INTO orders
  (user_id, status)
VALUES
  (99, 'pending');

COMMIT;
-- ← A's second COUNT
--    includes this row

🔧

Fix: Use REPEATABLE READ or SERIALIZABLE. PostgreSQL's snapshot isolation prevents phantom reads at REPEATABLE READ level — better than the SQL standard requires.

Summary — Which Problem, Which Fix

Problem	What happens	PostgreSQL default?	Fix with
Dirty Read	Read uncommitted data that may roll back	Already prevented	Read Committed (default)
Non-repeatable Read	Same row returns different values in same txn	Can happen	REPEATABLE READ
Phantom Read	Same query returns different row counts in same txn	Can happen at RC	REPEATABLE READ or SERIALIZABLE

6.5

Real-World Example: E-commerce Order Placement

A production-grade transaction with error handling

▾

Complete Order Placement Transaction

This is how Swiggy, Amazon, and Flipkart handle order placement — all steps in one atomic transaction.

            
-- ─── Complete order placement (production pattern) ─────────
BEGIN;

-- Step 1: Create the order header
INSERT INTO orders (user_id, status, created_at)
VALUES (42, 'pending', NOW())
RETURNING order_id INTO v_order_id;

-- Step 2: Add order items
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES
    (v_order_id, 7,  2, 499.00),
    (v_order_id, 12, 1, 999.00);

-- Step 3: Reduce inventory (with a check — can't go negative)
UPDATE inventory
SET   stock = stock - 2
WHERE product_id = 7
  AND   stock >= 2;       -- only deduct if enough stock

-- Step 4: Check the update actually happened
GET DIAGNOSTICS v_rows = ROW_COUNT;
IF v_rows = 0 THEN
    RAISE EXCEPTION 'Insufficient stock for product 7';
END IF;

-- Step 5: Create payment record
INSERT INTO payments (order_id, amount, status, method)
VALUES (v_order_id, 1997.00, 'pending', 'upi');

-- Step 6: Mark order as confirmed
UPDATE orders
SET status = 'confirmed', updated_at = NOW()
WHERE order_id = v_order_id;

COMMIT;
-- ✅ All 6 steps committed atomically
-- ✅ If any step fails, ROLLBACK fires automatically (in a function)
          

🏗️

In production, this entire block lives inside a PL/pgSQL function with an EXCEPTION handler that does the ROLLBACK automatically. Your Python/Node.js app calls the function — one round-trip, fully atomic.

Wrapped in a PL/pgSQL Function (production style)

            
CREATE OR REPLACE FUNCTION place_order(
    p_user_id    INTEGER,
    p_items      JSONB    -- [{product_id, quantity, price}]
)
RETURNS INTEGER  -- returns order_id
LANGUAGE plpgsql AS $$
DECLARE
    v_order_id  INTEGER;
    v_item      JSONB;
    v_rows      INTEGER;
    v_total     NUMERIC := 0;
BEGIN

    -- Create order
    INSERT INTO orders (user_id, status)
    VALUES (p_user_id, 'pending')
    RETURNING order_id INTO v_order_id;

    -- Loop over items in the JSON array
    FOR v_item IN SELECT * FROM jsonb_array_elements(p_items) LOOP

        -- Insert order item
        INSERT INTO order_items (order_id, product_id, quantity, price)
        VALUES (
            v_order_id,
            (v_item->>>'product_id')::INT,
            (v_item->>>'quantity')::INT,
            (v_item->>>'price')::NUMERIC
        );

        -- Deduct stock
        UPDATE inventory
        SET stock = stock - (v_item->>>'quantity')::INT
        WHERE product_id = (v_item->>>'product_id')::INT
          AND stock >= (v_item->>>'quantity')::INT;

        GET DIAGNOSTICS v_rows = ROW_COUNT;
        IF v_rows = 0 THEN
            RAISE EXCEPTION 'Out of stock: product %',
                (v_item->>>'product_id');
        END IF;

        v_total := v_total +
            (v_item->>>'price')::NUMERIC *
            (v_item->>>'quantity')::INT;
    END LOOP;

    -- Create payment record
    INSERT INTO payments (order_id, amount, status)
    VALUES (v_order_id, v_total, 'pending');

    RETURN v_order_id;

EXCEPTION
    WHEN OTHERS THEN
        -- RAISE automatically triggers a ROLLBACK of the whole txn
        RAISE;
END;
$$;

-- Call from your application (one network round-trip)
SELECT place_order(
    42,
    '[{"product_id":7,"quantity":2,"price":499},
       {"product_id":12,"quantity":1,"price":999}]'::JSONB
);
          

📋

Quick Reference Cheat Sheet

Everything at a glance

▾

            
── TRANSACTION CONTROL ─────────────────────────────────────
BEGIN;                               -- start transaction
COMMIT;                              -- save all changes
ROLLBACK;                            -- undo all changes

SAVEPOINT name;                      -- partial checkpoint
ROLLBACK TO SAVEPOINT name;          -- undo back to savepoint
RELEASE SAVEPOINT name;             -- remove savepoint

── ISOLATION LEVELS ────────────────────────────────────────
BEGIN ISOLATION LEVEL READ COMMITTED;     -- default
BEGIN ISOLATION LEVEL REPEATABLE READ;    -- no NR reads/phantoms
BEGIN ISOLATION LEVEL SERIALIZABLE;      -- fully sequential

── CONCURRENCY PROBLEMS ────────────────────────────────────
-- Dirty Read      → read uncommitted data → PG prevents always
-- NR Read         → same row, 2 values   → use REPEATABLE READ
-- Phantom Read    → same query, more rows → use REPEATABLE READ

── WHEN TO USE EACH LEVEL ──────────────────────────────────
-- READ COMMITTED   → 99% of OLTP (orders, inserts, updates)
-- REPEATABLE READ  → financial reports, analytics in a txn
-- SERIALIZABLE     → stock trading, seat booking (no conflicts)
          

Command	Effect	When
`BEGIN`	Open transaction	Before any grouped statements
`COMMIT`	Persist all changes	All steps succeeded
`ROLLBACK`	Undo everything	Any step failed
`SAVEPOINT`	Mark a partial checkpoint	Before risky sub-operations
`ROLLBACK TO`	Undo back to savepoint	Sub-operation failed