Managing SQL Transactions

1. Starting Transactions

Command	Use
BEGIN / START TRANSACTION	Start explicit tx
SET TRANSACTION ISOLATION LEVEL ...	Configure before first statement
READ ONLY / READ WRITE	Access mode hint
DEFERRABLE (PG)	Wait for serializable snapshot

2. Committing Changes

Command	Effect
COMMIT	Persist all changes; release locks
COMMIT AND CHAIN	Commit and start new tx immediately
2-Phase Commit	`PREPARE TRANSACTION 'id'; COMMIT PREPARED 'id';`

3. Rolling Back Changes

Command	Effect
ROLLBACK	Discard all changes since BEGIN
ROLLBACK TO SAVEPOINT s	Undo to savepoint, tx continues
ROLLBACK AND CHAIN	Rollback + new tx
Implicit rollback	On disconnect or fatal error

4. Creating Savepoints

Example: SAVEPOINT

BEGIN;
INSERT INTO orders(...) VALUES (...);
SAVEPOINT before_items;
INSERT INTO order_items(...) VALUES (...);
-- something goes wrong with items
ROLLBACK TO SAVEPOINT before_items;
COMMIT;  -- order committed, items not

Command	Detail
SAVEPOINT name	Mark point inside tx
RELEASE SAVEPOINT name	Discard the savepoint marker
ROLLBACK TO name	Undo to that point

5. Understanding ACID Properties

Property	Guarantee
Atomicity	All-or-nothing
Consistency	Valid → valid state per constraints
Isolation	Concurrent txs appear serial (per level)
Durability	Committed survives crash (via WAL)

6. Setting Isolation Levels

Level	Prevents	Allows
READ UNCOMMITTED	—	Dirty, non-repeatable, phantom reads
READ COMMITTED	Dirty reads	Non-repeatable, phantom
REPEATABLE READ	Dirty, non-repeatable	Phantom (PG: also phantom)
SERIALIZABLE	All anomalies	—

Note: Postgres default is READ COMMITTED; MySQL InnoDB default is REPEATABLE READ. Postgres RR provides snapshot isolation; SERIALIZABLE adds SSI (predicate detection).

7. Handling Concurrency Issues

Anomaly	Description
Dirty Read	Read uncommitted changes
Non-repeatable Read	Same row, different values on re-read
Phantom Read	Same query, different row set on re-read
Lost Update	Two writes, one overwrites the other
Write Skew	Two txs read overlapping set, write disjoint sets, violating invariant

8. Using Optimistic Locking

Mechanism	Detail
Version column	`WHERE id=? AND version=?` then bump
Timestamp	`WHERE updated_at=?`
Hash check	Compare row hash before write
Result	0 rows updated → retry / merge / fail

Example: Optimistic Lock

UPDATE accounts SET balance = ?, version = version + 1
WHERE id = ? AND version = ?;
-- if rowcount = 0, someone else updated → retry

9. Using Pessimistic Locking

Lock	Syntax
Row lock (exclusive)	`SELECT ... FOR UPDATE`
Row lock (shared)	`SELECT ... FOR SHARE`
Skip locked	`FOR UPDATE SKIP LOCKED` — queue patterns
No wait	`FOR UPDATE NOWAIT` — fail-fast
Table lock	`LOCK TABLE t IN MODE ...`
Advisory lock (PG)	`pg_advisory_lock(id)`

10. Managing Deadlocks

Strategy	Detail
Consistent lock order	Always acquire keys in same order (e.g., ascending id)
Short transactions	Reduce window for contention
Detect & retry	DB rolls back one victim; app catches SQLSTATE 40P01 / 1213
Lock timeout	`SET lock_timeout = '5s';`
Reduce isolation	READ COMMITTED has fewer lock conflicts

Warning: Deadlocks are inevitable in concurrent systems — always implement automatic retry with exponential backoff.

Related Cheat Sheets

Postgresql