Database Transactions: Python, JavaScript & Java Examples

Overview

A database transaction treats a sequence of operations as a single unit of work: either all of them commit, or none does. That guarantee — summarized by the ACID properties — is what keeps a failed transfer from debiting one account without crediting the other.

This recipe shows the core pattern in Python, JavaScript, and Java, plus the production techniques that surround it: isolation levels, savepoints, deadlock retries, and lock monitoring. PostgreSQL-only extras like advisory locks, LISTEN/NOTIFY, and async drivers get their own section further down.

When to Use

Typical triggers:

• Money has to move between accounts without half-failures — Money and Currency covers the decimal arithmetic side.
• A purchase should update inventory atomically — Batch Processing handles the bulk version.
• Creating related records across several tables in one logical step
• Ensuring read consistency for reporting queries
• Preventing race conditions in concurrent writes

Don’t wrap a transaction around a single UPDATE or INSERT — one statement is already atomic, and an explicit BEGIN/COMMIT only adds round-trips and lock overhead.

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 - %s WHERE id = %s", (100, 1))
    cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (100, 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');
    // Fold the balance check into the UPDATE so the guard is atomic
    const debit = await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1 RETURNING balance',
      [amount, fromId]
    );
    if (debit.rowCount === 0) {
      throw new Error('Insufficient funds or account not found');
    }
    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 {
    // Use BigDecimal instead of double for money in production code.
    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);
        }
    }
}

How It Works

BEGIN opens a transactional block. From that point on, every row you write is locked until the transaction ends — other sessions trying to update the same rows wait. Your changes stay invisible to other sessions (at the default isolation level) until COMMIT.

COMMIT does two things: it flushes the Write-Ahead Log (WAL) so the changes survive a crash, and it releases every lock the transaction was holding. ROLLBACK discards all changes made since BEGIN and releases the same locks. If the connection drops before COMMIT, the database rolls back automatically — a half-finished transaction can never persist.

The four ACID guarantees map to this flow:

• Atomicity: all statements commit or none do. The transfer above can’t debit account 1 without crediting account 2.
• Consistency: constraints (foreign keys, check constraints) are enforced at commit, so a transaction can’t leave the schema in an invalid state.
• Isolation: two transactions running at once can’t peek at each other’s uncommitted writes — how much they can see is the isolation level’s job, covered in the next section.
• Durability: once COMMIT returns, the data is on disk — a power failure loses nothing committed.

Because writes hold locks until the end, transaction length directly controls contention. A transaction that waits on an HTTP call or user input while holding locks can stall every session that touches the same rows.

SQL Isolation Levels

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

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

The anomalies in the table describe what a second session can observe while your transaction is still open. A dirty read returns uncommitted data that may later be rolled back, so the value you saw never officially existed. A non-repeatable read means your second SELECT on the same row returns a different value — another transaction sneaked a commit between the two reads. A phantom read is the same idea applied to a set: a repeated WHERE query returns new rows that match the filter.

Higher isolation levels trade throughput for correctness guarantees. SERIALIZABLE aborts transactions that can’t be proven serializable, so it requires retry logic at the application level — most workloads stay on READ COMMITTED and handle anomalies with explicit locking. The PostgreSQL isolation documentation lists the exact anomalies each level prevents.

Variants

The patterns below cover the situations you hit once the basic BEGIN/COMMIT flow is running: partial rollbacks, deadlocks, framework-managed transactions, runaway sessions, and lock-free concurrency control.

Savepoints for Nested Operations

A savepoint marks a position inside a transaction you can roll back to without aborting the whole unit — useful when one optional step might fail.

try:
    cur.execute("BEGIN")
    cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (100, 1))

    # Savepoint before risky operation
    cur.execute("SAVEPOINT before_insert")
    try:
        cur.execute("INSERT INTO audit_log (action) VALUES ('transfer')")
    except Exception:
        cur.execute("ROLLBACK TO SAVEPOINT before_insert")
        # Continue with main transaction

    cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (100, 2))
    conn.commit()
except Exception as e:
    conn.rollback()
    print(f"Rolled back: {e}")

Retry Logic for Deadlocks (Python)

PostgreSQL aborts one of two deadlocked transactions with error 40P01. Retry with exponential backoff instead of failing the request.

import time
from psycopg2 import errors

def with_retry(fn, max_retries=3, base_delay=0.1):
    for attempt in range(max_retries):
        try:
            return fn()
        except errors.DeadlockDetected:
            if attempt == max_retries - 1:
                raise
            delay = base_delay * (2 ** attempt)
            print(f"Deadlock detected, retrying in {delay}s...")
            time.sleep(delay)

def transfer(conn, from_id, to_id, amount):
    def _transfer():
        with conn.cursor() as cur:
            cur.execute("BEGIN")
            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 with_retry(_transfer)

JavaScript Transaction with Retry

async function withRetry(fn, maxRetries = 3, baseDelay = 100) {
    for (let attempt = 0; attempt < maxRetries; attempt++) {
        try {
            return await fn();
        } catch (err) {
            if (err.code === '40P01' && attempt < maxRetries - 1) {
                const delay = baseDelay * Math.pow(2, attempt);
                console.log(`Deadlock detected, retrying in ${delay}ms...`);
                await new Promise(resolve => setTimeout(resolve, delay));
                continue;
            }
            throw err;
        }
    }
}

async function safeTransfer(fromId, toId, amount) {
    return withRetry(async () => {
        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');
        } catch (err) {
            await client.query('ROLLBACK');
            throw err;
        } finally {
            client.release();
        }
    });
}

Java Transaction with Spring @Transactional

import org.springframework.transaction.annotation.Transactional;
import org.springframework.retry.annotation.Retryable;
import org.springframework.dao.DeadlockLoserDataAccessException;

@Service
public class TransferService {

    @Retryable(value = DeadlockLoserDataAccessException.class, maxAttempts = 3)
    @Transactional(isolation = Isolation.READ_COMMITTED)
    public void transfer(Long fromId, Long toId, BigDecimal amount) {
        Account from = accountRepository.findById(fromId)
            .orElseThrow(() -> new IllegalArgumentException("Account not found"));
        Account to = accountRepository.findById(toId)
            .orElseThrow(() -> new IllegalArgumentException("Account not found"));

        from.setBalance(from.getBalance().subtract(amount));
        to.setBalance(to.getBalance().add(amount));

        accountRepository.save(from);
        accountRepository.save(to);

        auditLogRepository.save(new AuditLog("transfer", fromId, toId, amount));
    }
}

Detecting Idle-in-Transaction Sessions

Connections that opened a transaction and never committed hold locks indefinitely. Find and terminate them in PostgreSQL:

-- PostgreSQL: find transactions that are idle and holding locks
SELECT
    pid,
    state,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY duration DESC;

-- Kill long idle-in-transaction sessions
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - query_start > interval '5 minutes';

Optimistic Locking with a Version Column

When contention is low, skip row locks and detect conflicts by version. A rowcount of 0 means another transaction won the race.

def update_with_optimistic_lock(conn, user_id, new_name, expected_version):
    with conn.cursor() as cur:
        cur.execute("BEGIN")
        cur.execute(
            "UPDATE users SET name = %s, version = version + 1 "
            "WHERE id = %s AND version = %s",
            (new_name, user_id, expected_version)
        )
        if cur.rowcount == 0:
            conn.rollback()
            raise ConcurrentModificationError("User was modified by another transaction")
        conn.commit()

PostgreSQL-Specific Techniques

The next group of variants only exists in PostgreSQL: advisory locks that coordinate work across transactions, LISTEN/NOTIFY for waking other sessions, and an async driver whose transaction API looks nothing like psycopg2’s. Runnable versions of these examples — plus the monitoring queries and a Java JDBC variant — live in the companion repo.

Advisory Locks

Advisory locks are application-defined locks stored in the database — they don’t protect any table, they only coordinate whoever bothers to check them. Pick the variant by scope: transaction-level locks release themselves on COMMIT or ROLLBACK, session-level locks stay until you release them, and try-lock returns immediately instead of waiting.

-- Transaction-level: released automatically on commit or rollback
SELECT pg_advisory_xact_lock(12345);

-- Session-level: you must release it yourself
SELECT pg_advisory_lock(12345);
-- ... critical section ...
SELECT pg_advisory_unlock(12345);

-- Non-blocking: returns true/false right away
SELECT pg_try_advisory_lock(12345);

cur.execute("SELECT pg_advisory_lock(%s)", (99999,))
try:
    cur.execute("DELETE FROM old_logs WHERE created_at < NOW() - INTERVAL '30 days'")
    conn.commit()
finally:
    cur.execute("SELECT pg_advisory_unlock(%s)", (99999,))
    conn.commit()

LISTEN/NOTIFY for Cross-Process Events

NOTIFY pushes a lightweight message to every session LISTENing on a channel — typically used to wake workers right after a commit lands. Delivery happens at commit time, so a rolled-back transaction never notifies anyone.

-- Process 1: subscribe to the channel
LISTEN order_created;

-- Process 2: fire inside or after a transaction
NOTIFY order_created, '{"order_id": 42, "customer": "alice"}';

import select
import psycopg2

conn = psycopg2.connect("postgresql://user:pass@localhost/mydb")
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cur = conn.cursor()
cur.execute("LISTEN order_created")

while True:
    if select.select([conn], [], [], 1) == ([], [], []):
        continue
    conn.poll()
    while conn.notifies:
        notify = conn.notifies.pop(0)
        print(f"{notify.channel}: {notify.payload}")

Async Transactions with asyncpg

asyncpg wraps transaction handling in an async context manager — no explicit BEGIN/COMMIT, and the block rolls back on any exception. The FOR UPDATE read below pins the balance row so a concurrent transfer can’t slip in between the check and the debit.

import asyncio
import asyncpg

async def transfer_funds(conn, from_account, to_account, amount):
    async with conn.transaction():
        row = await conn.fetchrow(
            "SELECT balance FROM accounts WHERE id = $1 FOR UPDATE",
            from_account
        )
        if row['balance'] < amount:
            raise ValueError("Insufficient funds")

        await conn.execute(
            "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
            amount, from_account
        )
        await conn.execute(
            "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
            amount, to_account
        )
        # Audit log inside the same transaction
        await conn.execute(
            "INSERT INTO transfers (from_account, to_account, amount) VALUES ($1, $2, $3)",
            from_account, to_account, amount
        )

async def main():
    conn = await asyncpg.connect('postgresql://user:pass@localhost/mydb')
    try:
        await transfer_funds(conn, 1, 2, 100.00)
    finally:
        await conn.close()

asyncio.run(main())

Transaction Context Manager

When raw BEGIN/COMMIT calls keep repeating across a codebase, a small context manager puts isolation level, commit, and rollback in one place:

from contextlib import contextmanager
import psycopg2

@contextmanager
def transaction(conn_str, isolation_level='READ COMMITTED'):
    conn = psycopg2.connect(conn_str)
    conn.set_isolation_level(
        getattr(psycopg2.extensions, f'ISOLATION_LEVEL_{isolation_level.replace(" ", "_").upper()}')
    )
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

with transaction("postgresql://user:pass@localhost/mydb") as conn:
    with conn.cursor() as cur:
        cur.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 1")
        cur.execute("INSERT INTO audit_log (action, amount) VALUES ('deposit', 100)")

What Works

• Keep transactions short: every second inside a transaction is a second someone else waits on your locks
• Use the lowest isolation level that still gives you the correctness you need
• Always handle rollback: try/catch/finally so an error never leaves a transaction dangling
• Use optimistic locking for high-contention rows (version columns) — see Optimistic Locking
• Test under concurrency: a single-threaded test never catches a race condition
• Collect input before the transaction starts: don’t hold locks while waiting on a user

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
• Calling external APIs or waiting for user input while holding open a transaction

Production Tips

These five settings and techniques keep transactions healthy under real load — apply them before you need them, not after the first lock storm.

1. Set lock_timeout for write transactions. Without it, a transaction can queue on a lock indefinitely while requests pile up behind it; with it, the query aborts and fails fast:

SET lock_timeout = '5s';

2. Use SELECT ... FOR UPDATE for pessimistic locking. Lock the rows you’re about to update so concurrent transactions wait instead of conflicting later:

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Application logic here
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

3. Batch updates inside one transaction. Grouping writes reduces transaction overhead and WAL volume compared to committing each row separately:

cur.execute("BEGIN")
for item in items:
    cur.execute("UPDATE inventory SET stock = stock - %s WHERE id = %s", (item.qty, item.id))
conn.commit()

4. Export a snapshot for consistent cross-transaction reads. pg_export_snapshot lets two connections share the same point-in-time view — handy for parallel report generation:

-- Transaction 1
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();
-- Returns '00000003-00000001-1'

-- Transaction 2
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '00000003-00000001-1';

5. Log transaction duration. Slow transactions are invisible until you measure them:

import time
import logging

logger = logging.getLogger(__name__)

start = time.monotonic()
try:
    cur.execute("BEGIN")
    # ... operations
    conn.commit()
finally:
    elapsed = time.monotonic() - start
    if elapsed > 1.0:
        logger.warning(f"Slow transaction: {elapsed:.2f}s")

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 UNLOGGED tables for staging data. Skipping WAL writes speeds up bulk imports and scratch tables — just don’t keep anything there you can’t regenerate:

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

5. Monitor pg_locks and pg_stat_database for contention. The query below finds blocked transactions; pg_stat_database tracks commit, rollback, and deadlock counts per database, and pg_stat_statements adds per-query timing on top:

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;

-- Per-database transaction rates
SELECT datname, xact_commit, xact_rollback, deadlocks
FROM pg_stat_database
WHERE datname = current_database();