Database Transactions Reference

Transaction semantics, isolation levels, locking strategies, and concurrency control.

Transaction Basics

A transaction groups multiple statements into one atomic operation. Either all succeed (COMMIT) or all are undone (ROLLBACK).

Basic transaction structure
BEGIN;

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

COMMIT;

If any statement fails between BEGIN and COMMIT, the changes are not applied. Use ROLLBACK to explicitly abort.

Explicit ROLLBACK on error
BEGIN;

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

-- Check: did we go negative?
SELECT quantity FROM inventory WHERE product_id = 42;
-- If quantity < 0, rollback
ROLLBACK;

ACID Properties

  • Atomicity — all or nothing. Partial execution is impossible.

  • Consistency — constraints (NOT NULL, UNIQUE, FK) are enforced at COMMIT.

  • Isolation — concurrent transactions do not see each other’s uncommitted changes (by default).

  • Durability — once COMMIT returns, the data survives a crash.

Isolation Levels

Set isolation level (PostgreSQL)
BEGIN ISOLATION LEVEL READ COMMITTED;    -- default in PostgreSQL
BEGIN ISOLATION LEVEL REPEATABLE READ;   -- snapshot at transaction start
BEGIN ISOLATION LEVEL SERIALIZABLE;      -- strictest: equivalent to serial execution
Level Dirty Read Non-Repeatable Read Phantom Read Use Case

READ UNCOMMITTED

Possible

Possible

Possible

Almost never appropriate

READ COMMITTED

No

Possible

Possible

Default (PostgreSQL, Oracle)

REPEATABLE READ

No

No

Possible

Default (MySQL InnoDB)

SERIALIZABLE

No

No

No

Financial, inventory

READ COMMITTED (PostgreSQL default): each statement sees a snapshot of committed data at statement start. Two identical SELECTs in the same transaction may return different results if another transaction commits between them.

REPEATABLE READ (MySQL default): snapshot is taken at transaction start. All reads within the transaction see the same data.

Savepoints

Savepoints let you partially rollback without aborting the entire transaction.

SAVEPOINT — partial rollback
BEGIN;

INSERT INTO orders (customer_id, total) VALUES (1, 250.00);
SAVEPOINT before_items;

INSERT INTO order_items (order_id, product_id, quantity) VALUES (100, 42, 5);
-- Oops, wrong product
ROLLBACK TO before_items;

-- Try again
INSERT INTO order_items (order_id, product_id, quantity) VALUES (100, 43, 5);

COMMIT;

Locking

Row-level locking — SELECT FOR UPDATE
BEGIN;

-- Lock the row so no other transaction can modify it
SELECT * FROM inventory WHERE product_id = 42 FOR UPDATE;

-- Safe to modify: we hold the lock
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;

COMMIT;

FOR UPDATE blocks other transactions from modifying or locking the same rows until this transaction commits. Prevents race conditions in read-then-write patterns.

SKIP LOCKED — non-blocking queue pattern (PostgreSQL)
BEGIN;

-- Pick one unprocessed job, skip any locked by other workers
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- Process it
UPDATE jobs SET status = 'processing' WHERE id = 42;

COMMIT;

This is the database-backed job queue pattern. Multiple workers can poll the same table without contention.

Deadlocks

A deadlock occurs when two transactions each hold a lock the other needs. The database detects this and kills one transaction.

Transaction A: locks row 1, then tries to lock row 2
Transaction B: locks row 2, then tries to lock row 1
→ Deadlock: one transaction is aborted with an error

Prevention:

  • Always lock rows in the same order (e.g., by primary key ascending).

  • Keep transactions short — less time holding locks means less chance of conflict.

  • Use NOWAIT to fail immediately rather than wait for a lock.

NOWAIT — fail immediately if row is locked
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row (instead of waiting)

SQLite Transactions

SQLite uses file-level locking, not row-level. Only one writer at a time.

SQLite transaction with WAL mode
PRAGMA journal_mode = WAL;   -- enables concurrent reads during writes

BEGIN IMMEDIATE;             -- acquire write lock immediately (not on first write)
INSERT INTO events (type, data) VALUES ('login', '{"user": "evan"}');
COMMIT;

BEGIN IMMEDIATE is preferred over plain BEGIN in SQLite. Plain BEGIN acquires a shared lock, then upgrades to exclusive on the first write — which can fail with SQLITE_BUSY if another connection holds a shared lock.

Handle SQLITE_BUSY in Python
import sqlite3

conn = sqlite3.connect("myapp.db", timeout=10)  # wait up to 10 seconds
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA busy_timeout=5000")

try:
    with conn:  # context manager = auto-commit/rollback
        conn.execute("INSERT INTO events (type) VALUES (?)", ("login",))
except sqlite3.OperationalError as e:
    if "database is locked" in str(e):
        print("Concurrent write conflict -- retry or queue")
    raise

Transaction Patterns

Try/catch with rollback (application code pattern)
conn = get_db_connection()
try:
    conn.execute("BEGIN")
    conn.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    conn.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
    conn.execute("COMMIT")
except Exception:
    conn.execute("ROLLBACK")
    raise
Autocommit mode — each statement is its own transaction
-- Without explicit BEGIN, each statement auto-commits
INSERT INTO logs (message) VALUES ('this commits immediately');

In most applications, use explicit transactions for any operation involving multiple statements. Single-statement operations can rely on autocommit.