How SQL Transactions Work
IntermediateA database transaction is a sequence of operations that executes as a single unit: either all operations succeed and are committed to disk, or all are rolled back as if nothing happened. Transactions protect your data from partial failures (a server crash mid-transfer) and concurrent access problems (two users modifying the same row simultaneously). The ACID properties define exactly what guarantees a transaction provides, and isolation levels let you trade consistency for performance depending on what your application can tolerate.
Think of a transaction like a draft document
When you write a draft in Google Docs, your changes are private until you publish. Other people see the last published version — not your in-progress edits. If your browser crashes mid-edit, the draft is lost but the last published version is safe. A database transaction is your draft: changes are private and reversible until you COMMIT. Other sessions see the last committed state. If you crash before committing, the database ROLLBACKs your changes automatically, leaving the data exactly as it was before you started.
Step by Step
Key Concepts
ACID
The four properties that define a reliable transaction. Atomicity: all-or-nothing. Consistency: constraints hold before and after. Isolation: concurrent transactions see a consistent view. Durability: committed data survives crashes. Different databases and storage engines make different trade-offs — NoSQL databases often sacrifice Isolation and Consistency for throughput (BASE: Basically Available, Soft state, Eventually consistent).
Isolation Levels
Read Uncommitted: can see uncommitted changes from other transactions (dirty reads — almost never used). Read Committed (default PostgreSQL): only sees committed data, but a second read in the same transaction might return different results if another transaction commits in between (non-repeatable read). Repeatable Read (default MySQL): a transaction sees a snapshot taken at its start — re-reading the same row returns the same value. Serializable: transactions execute as if sequential — eliminates all anomalies including phantom reads.
Dirty Read
Reading data that was written by a transaction that has not yet committed. If that transaction rolls back, you read data that never officially existed. Only possible at Read Uncommitted isolation level. Example: you read a balance mid-transfer and see an intermediate (invalid) state. Nearly all production databases use at least Read Committed, eliminating dirty reads.
Phantom Read
A transaction runs the same query twice and gets different rows the second time because another transaction inserted or deleted rows in between. Example: "SELECT COUNT(*) FROM orders WHERE amount > 1000" returns 5 the first time and 6 the second time (another transaction inserted a row and committed). Prevented by Serializable isolation (via predicate locks or optimistic validation).
Deadlock
Transaction A holds a lock on Row 1 and waits for Row 2. Transaction B holds a lock on Row 2 and waits for Row 1. Neither can proceed — they are deadlocked. The database detects this cycle and rolls back one transaction (the "victim") so the other can proceed. The rolled-back transaction receives an error and should be retried. Prevent deadlocks by always acquiring locks in the same order across transactions.
Optimistic vs Pessimistic Locking
Pessimistic: lock the row before reading it (SELECT ... FOR UPDATE) so no other transaction can modify it until you commit. Good when conflicts are frequent. Optimistic: read without locking, modify in memory, then check at write time if anyone else changed the row (via a version column). If changed, abort and retry. Good when conflicts are rare — reads are fast and concurrent. Hibernate's @Version annotation implements optimistic locking automatically.
Key Facts
- PostgreSQL's default isolation level is Read Committed. MySQL InnoDB's default is Repeatable Read. Both support Serializable. The difference matters: a "non-repeatable read" is acceptable in Postgres by default but prevented in MySQL by default.
- Long-running transactions are expensive. They hold locks (blocking other writers), accumulate row versions (increasing MVCC overhead), and delay VACUUM in PostgreSQL. Keep transactions as short as possible — do computation and I/O outside the transaction; only use BEGIN/COMMIT to wrap the actual database writes.
- Two-phase commit (2PC) extends transactions across multiple databases. Coordinator asks all participants to PREPARE (vote), then if all vote yes, sends COMMIT to all. If any votes no or crashes, sends ROLLBACK to all. Used in distributed systems and XA transactions. High overhead; most modern systems prefer SAGA pattern (compensating transactions) instead.
- The WAL (Write-Ahead Log) is also how streaming replication works in PostgreSQL. The primary writes to WAL; replicas stream and apply WAL records. Logical replication (introduced in PG10) streams only the logical changes (row-level) instead of physical page changes, allowing replication to different PostgreSQL versions and change data capture.
- SELECT COUNT(*) on a large PostgreSQL table is slow because MVCC means Postgres cannot maintain a single authoritative row count — it depends on your transaction's snapshot which rows are visible. InnoDB has the same issue. For approximate counts, use pg_stat_user_tables.n_live_tup (updated by ANALYZE) instead.
Real-World Applications
Safe money transfers
Debit sender and credit receiver inside a single transaction. If anything fails after the debit but before the credit, the rollback undoes the debit — money is never lost. Without a transaction, a server crash between the two UPDATEs would debit the sender but never credit the receiver. This is the canonical use case that motivates ACID.
Handling concurrent inventory
When 100 users try to buy the last item simultaneously, use SELECT ... FOR UPDATE to lock the inventory row, check quantity, decrement if available, and COMMIT — or ROLLBACK if out of stock. Without locking, two transactions could both see quantity=1, both proceed, and sell the item twice (overselling). The lock serialises access to that row.
Optimistic locking for low-contention updates
In a CMS where users rarely edit the same article, use a version column. Read the article (including version=5). User edits offline. On save: UPDATE articles SET content=?, version=6 WHERE id=? AND version=5. If another user saved first (version is now 6), the WHERE version=5 matches nothing — 0 rows updated. Detect this, reject the save with a "conflict" error. No locks held during the editing session.
Frequently Asked Questions
What is the difference between a transaction and a savepoint?
A savepoint is a named marker within a transaction. SAVEPOINT my_save lets you ROLLBACK TO my_save (undoing changes since the savepoint) without rolling back the entire transaction. Useful when you want to attempt a risky operation (e.g., insert that might violate a constraint) and recover from it without abandoning all previous work. Hibernate uses savepoints internally for nested transaction management.
Does @Transactional in Spring always use COMMIT/ROLLBACK?
Spring @Transactional defaults to rolling back on unchecked exceptions (RuntimeException and Error) and committing on checked exceptions. This surprises many developers — if your service throws a checked exception like IOException, Spring will COMMIT the partial transaction by default. Override with @Transactional(rollbackFor = Exception.class) to roll back on any exception. Always test exception paths explicitly.
What is the N+1 query problem and how do transactions relate?
N+1 is when loading N entities fires N additional queries (one per entity) to load associated data. For example: load 100 orders (1 query), then load the customer for each order (100 queries) = 101 queries. It's not directly a transaction problem, but long transactions that load many entities lazily (Hibernate lazy loading) accumulate N+1 queries inside a single transaction, holding the connection for a long time. Fix: use JOIN FETCH in JPQL, @EntityGraph, or fetch join in Spring Data to load associations in a single query.
How do you handle transaction retries?
When a transaction fails due to a deadlock or serialization failure (SQLSTATE 40001/40P01), you should retry the entire transaction from the beginning — not just the failed statement. Spring does not retry automatically. Implement retry with exponential backoff, either manually or with Spring Retry (@Retryable). The key: read all data fresh inside the retry attempt, not cached from the previous attempt, since the data may have changed.