A transaction is a sequence of database operations treated as a single unit: either everything succeeds, or nothing does. Transactions are how databases protect your data from crashes, bugs, and concurrent users stepping on each other.
What is a Transaction?#
If the server crashes between the two UPDATE statements, the partially-applied transfer would leave Alice's account debited but Bob's not credited. The money is simply gone. The transaction prevents this: either both updates apply, or neither does.
ACID Properties#
Atomicity#
The transaction is all-or-nothing. If any statement fails (or the server crashes), the entire transaction is rolled back as if it never happened.
Consistency#
A transaction takes the database from one valid state to another valid state. All constraints (NOT NULL, UNIQUE, foreign keys, CHECK constraints) must hold after every commit.
Isolation#
Concurrent transactions behave as if they run serially: they don't see each other's uncommitted changes.
Durability#
Once a transaction commits, the changes survive crashes. The DB writes a Write-Ahead Log (WAL) to disk before applying changes. On restart, uncommitted WAL entries are discarded; committed ones are replayed.
Concurrency Problems#
Without careful isolation, concurrent transactions cause anomalies:
Dirty Read#
Reading uncommitted changes from another transaction. If that transaction rolls back, you read data that never existed.
Non-Repeatable Read#
Reading the same row twice within a transaction and getting different values because another transaction committed a change in between.
Phantom Read#
Running the same query twice and getting different sets of rows because another transaction inserted or deleted rows in between.
Isolation Levels#
SQL defines four isolation levels, trading consistency for concurrency:
| Level | Dirty Read | Non-Repeatable Read | Phantom Read | How |
|---|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible | No locks |
| Read Committed | No | Possible | Possible | Write locks until commit, short read locks |
| Repeatable Read | No | No | Possible (MySQL: No) | Hold read locks until commit |
| Serializable | No | No | No | Full serial-equivalent execution |
Most production databases default to Read Committed (PostgreSQL, SQL Server) or Repeatable Read (MySQL InnoDB).
One Schedule, Three Answers#
The table above lists which anomalies each isolation level permits. This runs a single schedule of three transactions under three levels at once, so you can see the same sequence of operations produce three different answers.
Follow T2's reads across the three lanes. Under READ UNCOMMITTED it sees a value T1 later rolls back: a value that never existed. Under READ COMMITTED that is gone, but T2's two reads of the same row still disagree, because T3 committed between them. Under REPEATABLE READ both reads agree.
Nothing about the transactions changes between lanes. Only the level does.
MVCC (Multi-Version Concurrency Control)#
Modern databases (PostgreSQL, MySQL InnoDB, Oracle) implement isolation using MVCC rather than locking everything:
- Each row maintains multiple versions with timestamps
- Writers create new versions; old versions remain for existing readers
- Readers see a consistent snapshot, taken at transaction start under Repeatable Read/Snapshot, or refreshed per statement under Read Committed
- No read-write conflicts: readers never block writers
Savepoints#
You can create named checkpoints within a transaction and roll back to them partially:
Deadlocks in the Database#
Two transactions can deadlock if each holds a lock the other needs:
The database detects this and kills one transaction (the "victim"). The victim's transaction is rolled back automatically, and the application should retry.
Prevention: always lock rows in the same order across all transactions.
Python: transactions with psycopg2#
What to Remember#
- Transactions are all-or-nothing: COMMIT applies everything, ROLLBACK undoes everything
- ACID guarantees data integrity across failures and concurrent users
- Isolation levels trade performance for correctness: higher isolation = fewer anomalies = more locking
- MVCC lets readers and writers proceed concurrently without blocking each other
- Database deadlocks are detected automatically; design transactions to avoid them by locking in consistent order