What transactions see.
WHAT THE LEVELS ARE, BROADLY
Read uncommitted Read committed Repeatable read Serialisable
WHAT READ UNCOMMITTED PERMITS
Seeing changes that may be rolled back.
WHY IT IS ALMOST NEVER USED
The results may be based on data that never existed.
WHAT READ COMMITTED PROVIDES
Seeing only committed data, but a repeated query may see new results.
WHAT REPEATABLE READ ADDS
A consistent view for the whole transaction.
WHAT SERIALISABLE PROVIDES
Behaviour as though transactions ran one at a time.
WHAT IT COSTS
Concurrency, and more conflicts requiring retry.
WHAT MYSQL DEFAULTS TO
Repeatable read.
WHAT POSTGRESQL DEFAULTS TO
Read committed.
WHY THAT DIFFERENCE MATTERS
The same application behaves differently on each.
WHAT PROBLEM EACH LEVEL ADDRESSES
Dirty reads Non-repeatable reads Phantom rows
WHAT TO CHOOSE
The default, unless you have a demonstrated reason.
WHAT TO BE CAREFUL WITH
Long transactions at higher levels, holding a view open.
WHY
The database must retain old versions, which grows.
WHAT TO WATCH
Undo or version storage growing.
WHAT THAT INDICATES
A transaction left open.
