Grouping changes safely.
WHAT A TRANSACTION IS
A group of statements applied entirely or not at all.
WHAT THE GUARANTEES ARE
- Atomicity: all or nothing
- Consistency: constraints hold
- Isolation: concurrent transactions do not interfere improperly
- Durability: committed changes survive a crash
WHY ATOMICITY MATTERS MOST PRACTICALLY
A transfer that debits without crediting is the classic failure.
HOW TO USE ONE
Begin, issue statements, commit, or roll back on error.
WHAT AUTOCOMMIT MEANS
Each statement is its own transaction, unless you begin one.
WHAT ISOLATION LEVELS CONTROL
What one transaction sees of another's uncommitted or concurrent work.
WHAT THE COMMON PROBLEMS ARE
Reading uncommitted data The same query returning different results within one transaction Rows appearing that were not there before
WHAT THE DEFAULT USUALLY IS
A level preventing the worst, at reasonable cost.
WHAT TO AVOID
Long-running transactions.
WHY
They hold locks, block others, and grow the undo the database must keep.
WHAT A TRANSACTION SHOULD NOT CONTAIN
Waiting for a user Calling an external service Anything slow and unrelated
WHY
The lock is held throughout.
WHAT TO DO INSTEAD
Do the slow work first, then open the transaction.
