Code10x
Transactionshard5-8 years

PostgreSQL defaults to read committed and MySQL defaults to repeatable read. What does a plain SELECT actually see differently under each engine's default, and where does a service moved between them tend to break?

Under PostgreSQL's default (read committed), each statement inside a transaction takes a fresh snapshot, so two SELECTs of the same row several statements apart can return different values if another transaction committed a change in between. Under MySQL's default (repeatable read), the whole transaction takes one snapshot at its first read and every plain SELECT keeps returning that same snapshot's values for the rest of the transaction — but a locking read (SELECT ... FOR UPDATE, or an UPDATE/DELETE) reads the latest committed data instead, so a plain read and a locking read of the same row inside one MySQL transaction can legitimately disagree. A service that assumes 'repeated reads inside a transaction stay consistent' breaks when moved onto PostgreSQL at its default, and a service that assumes 'every read reflects concurrent commits immediately' breaks when moved onto MySQL at its default.

The lesson behind it →
PreviousWhy does a random UUID primary key hurt InnoDB in two distinct ways that it doesn't hurt PostgreSQL, and what does that say about choosing a primary key type?Next A refund endpoint sums existing refunds, checks the total against the order amount, and inserts a new refund if there's room — all inside one @Transactional method. Two requests for the same order arrive at once and both succeed, over-refunding the order. Why didn't the transaction prevent it, and what actually fixes it?
More on Transactions
A refund endpoint sums existing refunds, checks the total against the order amount, and inserts a new refund if there's room — all inside one @Transactional method. Two requests for the same order arrive at once and both succeed, over-refunding the order. Why didn't the transaction prevent it, and what actually fixes it?