Database Transactions

Overview

A database transaction is a sequence of operations treated as a single logical unit of work. Transactions guarantee ACID properties: Atomicity, Consistency, Isolation, and Durability. They are essential for financial operations, inventory management, and any multi-step data mutation where partial completion would leave data in an invalid state.

When to Use

Use this recipe when:

• Transferring money between accounts. See Money and Currency for exact decimal arithmetic.
• Updating inventory after a purchase. See Batch Processing for bulk operations.
• Creating related records across multiple tables
• Ensuring read consistency for reporting queries
• Preventing race conditions in concurrent writes

Solution

Python (SQLAlchemy / psycopg2)

import psycopg2

conn = psycopg2.connect("dbname=mydb user=postgres")
cur = conn.cursor()

try:
    cur.execute("BEGIN")
    cur.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    cur.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
    conn.commit()
    print("Transfer committed")
except Exception as e:
    conn.rollback()
    print(f"Rolled back: {e}")
finally:
    cur.close()
    conn.close()

JavaScript (Node.js + pg)

const { Pool } = require('pg');
const pool = new Pool();

async function transfer(fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, fromId]);
    await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, toId]);
    await client.query('COMMIT');
    console.log('Transfer committed');
  } catch (e) {
    await client.query('ROLLBACK');
    console.error('Rolled back:', e);
  } finally {
    client.release();
  }
}

Java (JDBC)

import java.sql.*;

public class TransactionExample {
    public static void transfer(Connection conn, int fromId, int toId, double amount) throws SQLException {
        conn.setAutoCommit(false);
        try (PreparedStatement debit = conn.prepareStatement("UPDATE accounts SET balance = balance - ? WHERE id = ?");
             PreparedStatement credit = conn.prepareStatement("UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
            debit.setDouble(1, amount);
            debit.setInt(2, fromId);
            debit.executeUpdate();

            credit.setDouble(1, amount);
            credit.setInt(2, toId);
            credit.executeUpdate();

            conn.commit();
            System.out.println("Transfer committed");
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }
}

SQL Isolation Levels

-- PostgreSQL syntax
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- your operations
COMMIT;

Level	Dirty Read	Non-Repeatable Read	Phantom Read	Performance
READ UNCOMMITTED	Allowed	Allowed	Allowed	Fastest
READ COMMITTED	Prevented	Allowed	Allowed	Default (PG, Oracle)
REPEATABLE READ	Prevented	Prevented	Allowed	Default (MySQL)
SERIALIZABLE	Prevented	Prevented	Prevented	Slowest, safest

What Works

• Keep transactions short: Long transactions hold locks and block other queries
• Use the lowest isolation level that meets your correctness requirements
• Always handle rollback: Use try/catch/finally to ensure rollback on error
• Use optimistic locking for high-contention data (version columns). See Optimistic Locking for version-based concurrency.
• Test concurrent scenarios: Simulate race conditions in your test suite
• Avoid user input inside transactions: Collect data before starting the transaction

Common Mistakes

• Forgetting to call commit() or rollback(), leaving connections idle in transaction
• Running long queries inside transactions, causing lock contention
• Using SERIALIZABLE everywhere without understanding the performance cost
• Not handling deadlock exceptions (error code 40P01 in PostgreSQL)
• Nesting transactions without savepoints

Performance Tips

1. Keep transactions under 100ms when possible. Short transactions reduce lock contention and improve throughput.

2. Use COPY instead of INSERT for bulk loads. COPY is considerably faster and generates less WAL:

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

3. Set synchronous_commit = off for non-critical writes. Reduces latency by not waiting for WAL flush. Use only for data that can be regenerated:

SET LOCAL synchronous_commit = off;

4. Use advisory locks for application-level coordination. Avoid row-level locks when you need cross-transaction coordination:

-- Acquire advisory lock
SELECT pg_advisory_lock(12345);
-- ... application logic
SELECT pg_advisory_unlock(12345);

5. Monitor pg_locks for contention. Identify blocked transactions:

SELECT
    bl.pid AS blocked_pid,
    kl.pid AS blocking_pid,
    a.query AS blocked_query,
    ka.query AS blocking_query
FROM pg_locks bl
JOIN pg_stat_activity a ON bl.pid = a.pid
JOIN pg_locks kl ON bl.locktype = kl.locktype
    AND bl.database IS NOT DISTINCT FROM kl.database
    AND bl.relation IS NOT DISTINCT FROM kl.relation
    AND bl.pid != kl.pid
JOIN pg_stat_activity ka ON kl.pid = ka.pid
WHERE NOT bl.granted;