Implement ACID Transactions in PostgreSQL

ACID properties — Atomicity, Consistency, Isolation, Durability — are the foundation of reliable database operations. PostgreSQL provides full ACID compliance with multiple isolation levels, savepoints for nested transactions, and reliable error handling that ensures data integrity even in failure scenarios.

When to Use This

• Multiple related operations must succeed or fail together. See Database Transactions for language-specific patterns.
• Concurrent access to the same records requires predictable behavior. See Locks and Mutexes for coordination primitives.
• Financial, inventory, or booking operations must not leave data in an intermediate state. See Money and Currency for exact arithmetic.

Prerequisites

• PostgreSQL 14+ running locally or on a managed service
• Understanding of basic SQL and database connections

Solution

1. Basic Transaction with Commit and Rollback

-- Transfer funds between accounts
BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1 AND balance >= 100;

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

-- Check that both updates succeeded
IF NOT FOUND THEN
  ROLLBACK;
  RAISE EXCEPTION 'Insufficient funds or account not found';
END IF;

COMMIT;

// db/transfer.ts
import { Pool } from 'pg';

async function transferFunds(pool: Pool, fromId: number, toId: number, amount: number) {
  const client = await pool.connect();
  
  try {
    await client.query('BEGIN');
    
    const debitResult = await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1 RETURNING balance',
      [amount, fromId]
    );
    
    if (debitResult.rowCount === 0) {
      throw new Error('Insufficient funds');
    }
    
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, toId]
    );
    
    await client.query('COMMIT');
    return { success: true, newBalance: debitResult.rows[0].balance };
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

2. Isolation Levels

-- READ COMMITTED (default): prevents dirty reads
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- Another transaction commits a change here
SELECT balance FROM accounts WHERE id = 1; -- sees the committed change
COMMIT;

-- REPEATABLE READ: prevents non-repeatable reads
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- Another transaction commits a change here
SELECT balance FROM accounts WHERE id = 1; -- still sees the original value
COMMIT;

-- SERIALIZABLE: prevents phantom reads, strongest isolation
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Another transaction inserts a pending order
SELECT COUNT(*) FROM orders WHERE status = 'pending'; -- same count as before
COMMIT;

3. Savepoints for Nested Operations

BEGIN;

INSERT INTO orders (customer_id, total) VALUES (1, 250.00) RETURNING id;
-- order_id = 100

SAVEPOINT before_items;

INSERT INTO order_items (order_id, product_id, quantity) VALUES (100, 5, 2);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (100, 8, 1);

-- Partial rollback if inventory check fails
SAVEPOINT before_inventory;

UPDATE inventory SET stock = stock - 2 WHERE product_id = 5;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 8;

-- If any stock went negative
ROLLBACK TO SAVEPOINT before_inventory;
-- Items remain, but inventory update is undone

COMMIT;

4. Advisory Locks for Application-Level Coordination

// db/distributed-lock.ts
async function withAdvisoryLock(pool: Pool, lockId: number, task: () => Promise<void>) {
  const client = await pool.connect();
  
  try {
    // Obtain exclusive advisory lock
    await client.query('SELECT pg_advisory_lock($1)', [lockId]);
    await task();
  } finally {
    await client.query('SELECT pg_advisory_unlock($1)', [lockId]);
    client.release();
  }
}

// Usage: prevent duplicate order processing
await withAdvisoryLock(pool, orderId, async () => {
  await processOrder(orderId);
});

How It Works

1. Atomicity ensures all operations complete or none do via COMMIT/ROLLBACK
2. Consistency enforces constraints (foreign keys, check constraints) within transactions
3. Isolation prevents concurrent transactions from interfering via MVCC and locks
4. Durability guarantees committed data survives crashes through WAL (Write-Ahead Logging)

Production Considerations

• Use READ COMMITTED for most applications; upgrade to SERIALIZABLE only when necessary. See Deadlocks and Retries for concurrency safety.
• Keep transactions short to minimize lock contention
• Use advisory locks when you need application-level serialization across services. See Locks and Mutexes for lock patterns.
• Enable pg_stat_statements to identify long-running transactions

Common Mistakes

• Holding transactions open while calling external APIs
• Not handling serialization failures in SERIALIZABLE mode
• Forgetting to release connections back to the pool after ROLLBACK

Performance Tips

1. Use COPY for bulk data loading. It bypasses most SQL parsing overhead:

COPY products FROM '/path/to/products.csv' WITH (FORMAT csv, HEADER true);

2. Reduce round-trips with multi-statement queries. Send multiple statements in a single execute:

cur.execute("""
    INSERT INTO orders (customer_id, total) VALUES (42, 99.99) RETURNING id;
    INSERT INTO audit_log (action) VALUES ('order_created');
""")

3. Use UNLOGGED tables for temporary data. Skip WAL writes for ephemeral tables:

CREATE UNLOGGED TABLE temp_import (id INT, data TEXT);

4. Set synchronous_commit = off for non-critical writes. This reduces latency by not waiting for WAL flush:

SET LOCAL synchronous_commit = off;

5. Monitor transaction statistics. Track rollbacks and deadlocks:

SELECT
    datname,
    xact_commit,
    xact_rollback,
    deadlocks,
    blks_read,
    blks_hit
FROM pg_stat_database
WHERE datname = current_database();