âš¡IntermediateTransactions
Transaction Isolation Levels
Control how concurrent transactions see each other's data with isolation levels.
Overview
PostgreSQL supports four transaction isolation levels defined by the SQL standard: READ COMMITTED, REPEATABLE READ, SERIALIZABLE, and (for completeness) READ UNCOMMITTED (which PostgreSQL treats as READ COMMITTED). Each level provides different trade-offs between consistency and concurrency.
READ COMMITTED (the default) sees only committed data at each statement. REPEATABLE READ sees the database as it was at transaction start no phantom reads. SERIALIZABLE is the strongest: transactions behave as if they ran serially. Choose the weakest level that meets your consistency needs stronger isolation means more lock contention.
Key Points
- •READ COMMITTED (default): each statement sees committed data at that moment
- •REPEATABLE READ: sees committed data from transaction start no phantom reads
- •SERIALIZABLE: strictest transactions appear to run one at a time
- •Set level: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
- •PostgreSQL uses MVCC reads never block writes and vice versa
- •Serializable Snapshot Isolation (SSI) in PostgreSQL: no write locks needed
Examples
Setting isolation levels
-- READ COMMITTED (default)
BEGIN;
SELECT * FROM accounts WHERE id = 1; -- sees latest committed data
COMMIT;
-- REPEATABLE READ: snapshot at transaction start
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts; -- snapshot taken here
-- ... other statements see the same snapshot
COMMIT;
-- SERIALIZABLE: strongest guarantee
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4