Transactions and Isolation Levels
When multiple users interact with a database simultaneously, things can get messy. One user might read data that another user is in the middle of changing. Transactions solve this by grouping operations into atomic units — either everything succeeds, or nothing does.
But transactions alone don't tell the whole story. Isolation levels control what one transaction can see of another transaction's work. This is where the real complexity — and the real power — lives.
The Four Isolation Levels
Databases typically offer four isolation levels, each with different trade-offs between consistency and performance:
READ UNCOMMITTED → Dirty reads possible (rarely used)
READ COMMITTED → See only committed data (PostgreSQL default)
REPEATABLE READ → Consistent reads within transaction
SERIALIZABLE → Full isolation (slowest)
READ UNCOMMITTED lets you see uncommitted changes from other transactions. This is almost never what you want — imagine seeing a bank transfer halfway through.
READ COMMITTED is the most common default. You only see data that other transactions have committed. This prevents dirty reads but allows other anomalies.
REPEATABLE READ guarantees that if you read a row twice in the same transaction, you'll get the same result. The database takes a snapshot at transaction start.
SERIALIZABLE provides complete isolation. Transactions behave as if they ran one after another, never concurrently. It's the safest but slowest option.
Concurrency Problems to Know
Each isolation level prevents different concurrency anomalies:
- Dirty read: Seeing uncommitted changes that might be rolled back
- Non-repeatable read: A row changes between two reads in your transaction
- Phantom read: New rows appear between two queries in your transaction
Choosing the Right Level
For most web applications, READ COMMITTED works fine. Your queries see consistent, committed data, and performance stays good.
Use REPEATABLE READ when you need consistent snapshots — like generating reports where all data should reflect the same point in time.
Reserve SERIALIZABLE for critical operations where correctness trumps everything else, like financial transactions or inventory management.
-- Set isolation level for a transaction
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... your operations ...
COMMIT;
The key insight is that higher isolation levels aren't "better" — they're more restrictive. Choose the lowest level that meets your consistency requirements, and you'll get better performance.