How is REPEATABLE_READ implementation different in MySQL and PostgreSQL?

Language Help SQL
1

How is REPEATABLE_READ implementation different in MySQL and PostgreSQL? Does MySQL lock the read rows (read data is up-to-date since no other transaction can write to the rows, but you have a lock overhead) whereas PostgreSQL doesn’t lock them but makes a snapshot at the beginning of a transaction (read data may not be up-to-date since the snapshot may fall behind more recent updates, but the performance doesn’t take a hit from locking)?

1 Like
2

This is a very nuanced question so I just recommend referring to documentation. That is close to the source of truth (outside of looking at the source code).

According to the documentation MySQL has 2 scenarios for REPEATABLE_READ (more details on the definition of consistent and locking reads in the docs)

  • does not lock for consistent reads
  • does lock for locking reads (well, yes haha)

Postgres yes, uses snapshot isolation.

The documentation for these is excellent and I even recommend bookmarking some of their footnotes if this is stuff that interests you.

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html#isolation-level-repeatable-read

2 Likes
3
  • REPEATABLE READ

This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See Section 15.7.2.3, “Consistent Nonlocking Reads”.

For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition.

For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.

For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range. For information about gap locks and next-key locks, see Section 15.7.1, “InnoDB Locking”.

Do I understand correctly that it basically means for plain SELECTs MySQL uses the same snapshot isolation as PostgreSQL?

4

I want to say yes, but I’ll add in caveats.

  • I have only worked with MS SQL Server and very lightly with Postgres, my knowledge of the internals is very limited, and none for MySQL.
  • snapshot isolation as a term has varied in usage over time, so what might mean snapshot isolation to one person might mean a slightly different thing for another. I think if it’s a matter of making practical decisions this is a case where it’s better to just lay out the behavior and see what it is defined to do. Never assume x will offer y unless you know explicitly that it does. If it’s an academic venture, then there’s nothing better than either looking at the code or running some sort of tests to corroborate the concepts, or to read specific literature/papers that analyze the topic more in-depth.

My reason for not saying it outright is that for some purposes there might be stricter definitions necessary. If we loosen that strictness then it’s a yes.

(my point is that MySQL does use snapshots, but the term snapshot isolation might be overloaded and they never say they use it specifically, maybe for that reason)

1 Like