Database Transactions Cheat Sheet
Transaction syntax, savepoints, application-level transaction handling, and locking strategies for writing safe, concurrent database operations reliably.
2 PagesIntermediateMar 15, 2026
Basic Transaction Syntax
Starting, committing, and rolling back.
sql
BEGIN TRANSACTION; -- or: START TRANSACTION; / BEGIN;UPDATE accounts SET balance = balance - 100 WHERE id = 1;UPDATE accounts SET balance = balance + 100 WHERE id = 2;COMMIT; -- persist changes-- ROLLBACK; -- undo everything since BEGIN
Savepoints
Rolling back part of a transaction.
sql
BEGIN;UPDATE inventory SET qty = qty - 1 WHERE sku = 'A1';SAVEPOINT before_shipping;UPDATE shipments SET status = 'failed' WHERE id = 99;ROLLBACK TO SAVEPOINT before_shipping; -- undoes only the shipment updateCOMMIT;
Application-Level Transactions
Wrapping statements in a transaction from code.
python
import psycopg2conn = psycopg2.connect(dsn)try: with conn: with conn.cursor() as cur: cur.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1") cur.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2") # 'with conn' commits on success, rolls back automatically on exceptionexcept Exception: conn.rollback() raise
Locking & Concurrency
Strategies for handling concurrent writers.
- Pessimistic locking- SELECT ... FOR UPDATE locks matching rows until the transaction ends
- Optimistic locking- check a version/timestamp column on UPDATE and retry on conflict
- Deadlock- two transactions wait on locks held by each other; the DB detects and aborts one
- Two-phase commit (2PC)- a protocol coordinating one atomic commit across multiple systems
- Autocommit- default mode where each statement runs as its own implicit transaction
Work-Queue Pattern with SKIP LOCKED
Let multiple workers pull jobs off a shared table without blocking each other.
sql
BEGIN;SELECT id, payloadFROM job_queueWHERE status = 'pending'ORDER BY created_atLIMIT 1FOR UPDATE SKIP LOCKED; -- skip rows another worker already lockedUPDATE job_queue SET status = 'processing', worker_id = 'w-7' WHERE id = 42;COMMIT;-- Without SKIP LOCKED, concurrent workers would queue up waiting on the-- same FOR UPDATE lock instead of grabbing a different row.
Retrying Serialization Failures
SERIALIZABLE and some SNAPSHOT transactions can abort with a conflict error — always retry them.
python
import psycopg2import timedef run_with_retry(conn, fn, max_attempts=5): for attempt in range(1, max_attempts + 1): try: with conn: with conn.cursor() as cur: cur.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE") return fn(cur) except psycopg2.errors.SerializationFailure: conn.rollback() if attempt == max_attempts: raise time.sleep(0.05 * (2 ** attempt)) # exponential backoff raise RuntimeError("unreachable")
Setting Isolation Level Per Engine
The SQL standard clause and where each engine deviates from it.
- Postgres / SQL Server / Oracle- SET TRANSACTION ISOLATION LEVEL {READ COMMITTED | REPEATABLE READ | SERIALIZABLE}; must run before or as the first statement in the transaction
- MySQL- SET SESSION TRANSACTION ISOLATION LEVEL ...; or set globally in my.cnf via transaction_isolation
- SQL Server SNAPSHOT- requires ALTER DATABASE ... SET ALLOW_SNAPSHOT_ISOLATION ON once, then SET TRANSACTION ISOLATION LEVEL SNAPSHOT per session
- Scoping- the isolation level applies only to the next transaction unless set at the session level; per-statement overrides (e.g. Postgres SET LOCAL) revert automatically at COMMIT/ROLLBACK
Two-Phase Commit Across Resources (XA)
Coordinating an atomic commit across two independent databases.
python
# Pseudocode for an XA-style 2PC coordinatordef transfer_across_databases(db_a, db_b, amount): tx_a = db_a.xa_start() tx_b = db_b.xa_start() try: db_a.execute(tx_a, "UPDATE accounts SET balance = balance - %s WHERE id = 1", amount) db_b.execute(tx_b, "UPDATE accounts SET balance = balance + %s WHERE id = 2", amount) # Phase 1: prepare — each participant durably records it CAN commit db_a.xa_prepare(tx_a) db_b.xa_prepare(tx_b) # Phase 2: commit — only issued once both prepares succeeded db_a.xa_commit(tx_a) db_b.xa_commit(tx_b) except Exception: db_a.xa_rollback(tx_a) db_b.xa_rollback(tx_b) raise # If the coordinator crashes between prepare and commit, a recovery # process must replay the outcome from its own durable log.
Transaction Anti-Patterns
Common mistakes that cause lock contention, timeouts, or subtle bugs in production.
- Network call inside a transaction- calling an external API or waiting on a queue while holding row locks stalls every other writer on those rows
- Non-idempotent retries- retrying a transaction that partially committed (e.g. connection dropped after COMMIT but before the ack) can double-apply the effect unless it's keyed by an idempotency token
- Implicit autocommit surprises- some drivers commit after every statement by default, silently defeating BEGIN/COMMIT blocks unless autocommit is explicitly disabled
- Long-lived read transactions- an open REPEATABLE READ transaction pins the MVCC snapshot, blocking VACUUM/garbage collection and causing table bloat even if it never writes
- Inconsistent lock ordering- updating tables/rows in a different order across code paths is the most common cause of deadlocks; always acquire locks in a fixed global order
Pro Tip
Keep transactions as short as possible and never wait on user input or an external API call inside one — long-running transactions hold locks that block other writers, and in Postgres they also prevent VACUUM from reclaiming dead rows.
Was this cheat sheet helpful?
Explore Topics
#DatabaseTransactions#DatabaseTransactionsCheatSheet#Database#Intermediate#BasicTransactionSyntax#Savepoints#ApplicationLevelTransactions#LockingConcurrency#Databases#Concurrency#CheatSheet#SkillVeris
