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

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?

ACID Properties Cheat Sheet

Database Sharding Cheat Sheet

Explore Topics

#DatabaseTransactions#DatabaseTransactionsCheatSheet#Database#Intermediate#BasicTransactionSyntax#Savepoints#ApplicationLevelTransactions#LockingConcurrency#Databases#Concurrency#CheatSheet#SkillVeris

Sri Hayavadhana Info-Tech

Professional Web Designing Services