SQL Transactions and Isolation Levels

Transactions are the mechanism databases use to keep data consistent when multiple operations happen at the same time. Without transactions, a bank transfer could debit one account without crediting the other. Without proper isolation, two users reading the same data could see different states mid-update. This post covers how transactions work, what isolation levels control, and how to handle the concurrency problems that arise in real applications.

ACID properties

Every transaction in a relational database is supposed to guarantee four properties:

Atomicity — All statements in a transaction succeed or all fail. There is no partial execution. If a transfer debits $100 but the credit fails, the debit is rolled back.

Consistency — The database moves from one valid state to another. Constraints (foreign keys, unique, check) are enforced. An invalid state is never visible to other transactions.

Isolation — Concurrent transactions do not interfere with each other. The isolation level determines exactly how much interference is allowed.

Durability — Once a transaction commits, its changes survive crashes. The data is written to durable storage before the commit returns.

BEGIN;

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

-- If either UPDATE fails, both are rolled back
COMMIT;

Transaction basics

-- Explicit transaction
BEGIN;
INSERT INTO orders (customer_id, total) VALUES (42, 99.99);
INSERT INTO order_items (order_id, product_id, quantity)
    VALUES (currval('orders_id_seq'), 101, 2);
COMMIT;

-- Rollback on error
BEGIN;
UPDATE inventory SET stock = stock - 5 WHERE product_id = 101;
-- Oh wait, not enough stock
ROLLBACK;

-- Savepoints for partial rollback
BEGIN;
INSERT INTO orders (customer_id, total) VALUES (42, 99.99);
SAVEPOINT before_items;
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 999, 1);
-- Product 999 does not exist — foreign key violation
ROLLBACK TO SAVEPOINT before_items;
-- The order INSERT is still intact
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 101, 1);
COMMIT;

Read phenomena

Isolation levels are defined by which “read phenomena” they allow.

Dirty read

A transaction reads data written by another transaction that has not yet committed. If that transaction rolls back, the first transaction read data that never actually existed.

Transaction A:                    Transaction B:
BEGIN;
UPDATE products SET price = 50
  WHERE id = 1;
                                  BEGIN;
                                  SELECT price FROM products WHERE id = 1;
                                  -- Reads 50 (dirty read!)
ROLLBACK;
-- Price is back to original
                                  -- Transaction B used price=50, which
                                  -- was never committed

Non-repeatable read

A transaction reads the same row twice and gets different values because another transaction modified and committed the row between the two reads.

Transaction A:                    Transaction B:
BEGIN;
SELECT price FROM products
  WHERE id = 1;
-- Returns 100
                                  BEGIN;
                                  UPDATE products SET price = 80
                                    WHERE id = 1;
                                  COMMIT;
SELECT price FROM products
  WHERE id = 1;
-- Returns 80 (different!)
COMMIT;

Phantom read

A transaction runs the same query twice and gets a different set of rows because another transaction inserted or deleted rows that match the query condition.

Transaction A:                    Transaction B:
BEGIN;
SELECT COUNT(*) FROM orders
  WHERE customer_id = 42;
-- Returns 5
                                  BEGIN;
                                  INSERT INTO orders (customer_id, total)
                                    VALUES (42, 29.99);
                                  COMMIT;
SELECT COUNT(*) FROM orders
  WHERE customer_id = 42;
-- Returns 6 (phantom row!)
COMMIT;

Isolation levels

The SQL standard defines four isolation levels, each preventing more phenomena:

Level	Dirty Read	Non-repeatable Read	Phantom 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 phantom reads in practice, going beyond the SQL standard.

Read Uncommitted

The weakest level. Rarely used in practice. PostgreSQL does not even implement it — it silently upgrades to Read Committed.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Read Committed

The default in PostgreSQL and Oracle. Each statement sees only data committed before that statement began. Different statements within the same transaction can see different committed data.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;
-- Statement 1: sees all data committed before this statement
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Returns 10

-- Meanwhile, another transaction commits 2 new pending orders

-- Statement 2: sees the newly committed data
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Returns 12 (non-repeatable read)
COMMIT;

Read Committed is sufficient for most applications. It prevents dirty reads and keeps each statement’s view consistent, even if the transaction’s view changes between statements.

Repeatable Read

Each transaction sees a snapshot of the database as of the transaction’s start time. All reads within the transaction return the same data, regardless of other transactions committing changes.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Returns 10

-- Another transaction commits 2 new pending orders

SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Still returns 10 (snapshot from transaction start)
COMMIT;

The tradeoff: if two transactions try to update the same row, the second one gets a serialization error and must retry.

-- Transaction A:
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Transaction B (concurrent):
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- ERROR: could not serialize access due to concurrent update
-- Transaction B must ROLLBACK and retry

Serializable

The strongest level. Transactions execute as if they ran one at a time, in some serial order. Any execution that would produce a result different from some serial ordering is rejected.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;
-- Check if a username is available
SELECT id FROM users WHERE username = 'alice';
-- Returns empty

-- Another serializable transaction also checks and finds it empty
-- Both try to insert

INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
COMMIT;
-- One transaction succeeds, the other gets a serialization error

Serializable is the safest but most restrictive level. Applications must be prepared to retry transactions that fail with serialization errors.

# Application-level retry pattern
import time
from psycopg2 import errors

MAX_RETRIES = 3

def transfer_funds(conn, from_id, to_id, amount):
    for attempt in range(MAX_RETRIES):
        try:
            with conn.cursor() as cur:
                cur.execute("BEGIN ISOLATION LEVEL SERIALIZABLE")
                cur.execute(
                    "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                    (amount, from_id),
                )
                cur.execute(
                    "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                    (amount, to_id),
                )
                conn.commit()
                return True
        except errors.SerializationFailure:
            conn.rollback()
            if attempt < MAX_RETRIES - 1:
                time.sleep(0.1 * (2 ** attempt))  # Exponential backoff
            continue
    raise Exception("Transfer failed after max retries")

Deadlocks

A deadlock occurs when two transactions each hold a lock that the other needs. Neither can proceed.

Transaction A:                    Transaction B:
BEGIN;                           BEGIN;
UPDATE accounts SET balance = 0  UPDATE accounts SET balance = 0
  WHERE id = 1;                    WHERE id = 2;
-- Holds lock on row 1           -- Holds lock on row 2

UPDATE accounts SET balance = 0  UPDATE accounts SET balance = 0
  WHERE id = 2;                    WHERE id = 1;
-- Waits for lock on row 2       -- Waits for lock on row 1
-- DEADLOCK!

The database detects deadlocks and kills one transaction (the “victim”) with an error. The other transaction can proceed.

Preventing deadlocks

Access rows in a consistent order. If all transactions update accounts in ascending ID order, deadlocks become impossible.

-- Always lock rows in a consistent order (by id ASC)
BEGIN;
-- Lock both rows in order
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- Now safely update both
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Use SELECT … FOR UPDATE to lock rows upfront. This makes the locking explicit and predictable.

BEGIN;
-- Lock the row immediately
SELECT * FROM inventory WHERE product_id = 101 FOR UPDATE;

-- Check stock
-- If sufficient, decrement
UPDATE inventory SET stock = stock - 5 WHERE product_id = 101;
COMMIT;

Keep transactions short. The longer a transaction holds locks, the higher the chance of deadlock. Do as much computation as possible outside the transaction.

Practical patterns

Optimistic locking

Instead of locking rows, use a version counter. The update succeeds only if the version has not changed.

-- Read the current version
SELECT id, name, price, version FROM products WHERE id = 1;
-- Returns: id=1, name="Widget", price=100, version=3

-- Update only if version matches
UPDATE products
SET price = 110, version = version + 1
WHERE id = 1 AND version = 3;

-- If 0 rows affected, someone else updated — retry

def update_product_price(conn, product_id, new_price):
    for _ in range(3):
        with conn.cursor() as cur:
            cur.execute(
                "SELECT version FROM products WHERE id = %s",
                (product_id,),
            )
            version = cur.fetchone()[0]

            cur.execute(
                """UPDATE products
                   SET price = %s, version = version + 1
                   WHERE id = %s AND version = %s""",
                (new_price, product_id, version),
            )
            if cur.rowcount == 1:
                conn.commit()
                return True
            conn.rollback()
    raise Exception("Concurrent modification — retry exhausted")

Advisory locks

Application-level locks that do not lock any actual rows. Useful for coordinating processes.

-- Acquire an advisory lock (blocks until available)
SELECT pg_advisory_lock(12345);

-- Do exclusive work (e.g., batch processing)
-- ...

-- Release the lock
SELECT pg_advisory_unlock(12345);

-- Try to acquire without blocking
SELECT pg_try_advisory_lock(12345);
-- Returns true if acquired, false if already held

Idempotent operations

Design operations to be safely retried. This is critical when transactions can fail and need to be retried.

-- Idempotent insert: use ON CONFLICT
INSERT INTO daily_stats (date, page, views)
VALUES ('2025-06-30', '/home', 1)
ON CONFLICT (date, page)
DO UPDATE SET views = daily_stats.views + 1;

-- Idempotent with a unique request ID
INSERT INTO payments (request_id, amount, customer_id)
VALUES ('req-abc-123', 99.99, 42)
ON CONFLICT (request_id) DO NOTHING;

Choosing an isolation level

Read Committed (the default in most databases) is right for the majority of applications. It prevents dirty reads, and non-repeatable reads rarely cause real problems in practice.

Repeatable Read is useful for reports and analytics that need a consistent snapshot of data. It is also good for transactions that read data, compute something, and write based on what they read.

Serializable is for operations that must be absolutely correct with respect to concurrent access — financial transactions, inventory management, booking systems. Be prepared to implement retry logic.

The key insight is that higher isolation levels do not automatically make your application “safer.” They make certain anomalies impossible but require your application to handle serialization failures. The safest approach is usually Read Committed with application-level patterns (optimistic locking, idempotent operations) for the cases that need stronger guarantees.