Transactions and Concurrency

Transactions and Concurrency

A transaction is a sequence of SQL statements that are treated as a single unit of work. Either all statements succeed and the changes are permanently saved (commit), or any failure causes all changes to be undone (rollback). This all-or-nothing behavior is the Atomicity property of ACID.

Concurrency adds complexity: multiple transactions running simultaneously must not interfere with each other. PostgreSQL uses MVCC (Multi-Version Concurrency Control) to let readers and writers coexist without blocking each other.

BEGIN, COMMIT, and ROLLBACK

Every SQL statement in PostgreSQL runs inside a transaction. By default, each statement is its own transaction (autocommit mode). Use BEGIN to start an explicit multi-statement transaction:

-- Explicit transaction
BEGIN;

UPDATE products SET stock = stock - 1 WHERE product_id = 5;
INSERT INTO inventory_log (product_id, change, changed_at) VALUES (5, -1, NOW());

COMMIT;  -- both changes saved permanently

If any statement fails, or if you decide to cancel, use ROLLBACK:

BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;

-- Discover that account 2 doesn't exist, cancel everything
ROLLBACK;

-- Neither UPDATE was saved; account 1's balance is unchanged

Error Handling

If an error occurs inside an explicit transaction, all subsequent statements fail with "current transaction is aborted, commands ignored until end of transaction block." You must issue a ROLLBACK before starting a new transaction:

BEGIN;

INSERT INTO products (name, price) VALUES ('Test', 10.00);
-- Simulate error: INSERT INTO non_existent_table VALUES (1);
-- ERROR: relation "non_existent_table" does not exist

-- The transaction is now in an error state
-- This SELECT will fail: SELECT * FROM products;
-- ERROR: current transaction is aborted

ROLLBACK;  -- reset the transaction state

-- Now you can start fresh
BEGIN;
-- ...

SAVEPOINT — Partial Rollback

A savepoint marks a point within a transaction that you can roll back to without abandoning the entire transaction:

BEGIN;

-- Step 1: create the order
INSERT INTO orders (customer_id) VALUES (7) RETURNING order_id;
-- Returns order_id = 201

SAVEPOINT order_created;

-- Step 2: try to apply a coupon
UPDATE coupons SET used_count = used_count + 1 WHERE code = 'SAVE10' AND active = TRUE;

-- Check if coupon was found (0 rows updated means invalid coupon)
-- If no rows updated, rollback to savepoint and continue without coupon
ROLLBACK TO SAVEPOINT order_created;

-- Step 3: finalize order without coupon
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (201, 5, 2, 29.99);

COMMIT;
-- order 201 exists; coupon update was rolled back; order item was saved

Savepoints enable complex workflows where partial failures should not cancel the entire operation.

How MVCC Works

PostgreSQL's Multi-Version Concurrency Control (MVCC) is the foundation of its concurrency model. Instead of locking rows to prevent conflicting reads and writes, PostgreSQL keeps multiple versions of each row:

• When a row is updated, PostgreSQL creates a new version of the row while keeping the old version
• Each transaction sees a snapshot of the database as it was at a specific point in time
• Old row versions are cleaned up by VACUUM once no transaction needs them anymore

The key insight: readers never block writers, and writers never block readers. A SELECT query never waits for an UPDATE on the same rows, and an UPDATE never waits for a SELECT. This is a huge advantage over databases that use shared and exclusive locks for reads and writes.

Isolation Levels

SQL defines four isolation levels, each offering a different trade-off between performance and consistency. Each level prevents certain types of anomalies:

*PostgreSQL's REPEATABLE READ also prevents phantom reads due to MVCC.

READ COMMITTED (Default)

Each statement sees the latest committed data. If another transaction commits between two statements in your transaction, the second statement sees the updated data:

-- Session A
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- sees 1000

-- Session B commits: UPDATE accounts SET balance = 500 WHERE id = 1;

SELECT balance FROM accounts WHERE id = 1;  -- now sees 500!
COMMIT;

This is the correct behavior for most application queries.

REPEATABLE READ

All statements in the transaction see the same snapshot — the one taken when the transaction started:

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT balance FROM accounts WHERE id = 1;  -- sees 1000

-- Session B commits: UPDATE accounts SET balance = 500 WHERE id = 1;

SELECT balance FROM accounts WHERE id = 1;  -- still sees 1000 (snapshot is fixed)

COMMIT;

Use REPEATABLE READ for:

• Multi-statement reports that must be consistent across all statements
• Complex calculations based on multiple related reads

SERIALIZABLE

The strongest isolation level. Transactions are executed as if they ran one at a time (serially), even though they actually run concurrently. PostgreSQL uses Serializable Snapshot Isolation (SSI), which detects and aborts transactions that would produce non-serializable results:

BEGIN ISOLATION LEVEL SERIALIZABLE;

-- Your complex multi-statement transaction here
-- PostgreSQL will abort with ERROR: could not serialize access
-- if another transaction conflicts; your application should retry

COMMIT;

Use SERIALIZABLE for financial transactions, inventory management, and any use case where correctness is more important than performance.

Locking

While MVCC handles read/write concurrency, write/write conflicts still require locks:

Row-Level Locks

-- SELECT FOR UPDATE: lock rows for subsequent UPDATE
BEGIN;

SELECT * FROM inventory
WHERE product_id = 5 AND quantity > 0
FOR UPDATE;  -- prevents other transactions from modifying these rows

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 5;

COMMIT;

-- SKIP LOCKED: skip locked rows instead of waiting
SELECT product_id, quantity
FROM inventory
WHERE status = 'available'
FOR UPDATE SKIP LOCKED
LIMIT 10;

FOR UPDATE is essential for the "check-then-act" pattern to prevent race conditions.

Advisory Locks

Advisory locks are application-level locks with no automatic semantic — you define what they protect:

-- Acquire a session-level advisory lock (released when session ends or explicitly)
SELECT pg_advisory_lock(12345);

-- Try to acquire; returns TRUE if acquired, FALSE if already held
SELECT pg_try_advisory_lock(12345);

-- Release explicitly
SELECT pg_advisory_unlock(12345);

-- Transaction-level advisory lock (released automatically at commit/rollback)
SELECT pg_advisory_xact_lock(12345);

Advisory locks are useful for coordinating distributed processes — for example, ensuring only one worker processes a batch job at a time.

Detecting and Handling Deadlocks

PostgreSQL automatically detects deadlocks and aborts one of the conflicting transactions. Your application should catch and retry:

-- Pattern for retryable transactions in PostgreSQL
-- (application code handles retry logic)

-- Always acquire locks in a consistent order to prevent deadlocks:
-- WRONG: Session 1 locks account 1 then 2; Session 2 locks account 2 then 1
-- RIGHT: Both sessions always lock the lower-numbered account first

BEGIN;
-- Lock in ascending order by account_id
SELECT * FROM accounts WHERE account_id IN (1, 2) ORDER BY account_id FOR UPDATE;
-- Now update both safely
UPDATE accounts SET balance = balance - 250 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 250 WHERE account_id = 2;
COMMIT;

Transactions and concurrency control are what separate a production-grade database from a simple file store. Understanding MVCC, isolation levels, and locking gives you the tools to build systems that are both fast and correct. The final production lesson covers security, roles, and backups.