Difference in Repeatable Read Semantics in MySQL and PostgreSQL

Question

I understand that in both MySQL and PostgreSQL, the REPEATABLE READ isolation level will make the reads see the snapshot at the beginning of the transaction. But in the MySQL documentation at https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html following Note is mentioned with an example

The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements. If you insert or modify some rows and then commit that transaction, a DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session could not query them. If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction. For example, you might encounter a situation like the following:

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
-- Returns 0: no rows match.
DELETE FROM t1 WHERE c1 = 'xyz';
-- Deletes several rows recently committed by other transaction.

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
-- Returns 0: no rows match.
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
-- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- Returns 10: this txn can now see the rows it just updated.

Does the same examples hold true for PostgreSQL or it will not allow such behaviour?

It is difficult to give a general answer Yes or No, the easiest would be to test individual scenarios on both databases and compare them. The difficulty is that there may be a lot of these scenarios, there are three basic commands that change data in the table: INSERT UPDATE DELETE, additionally SELECT FOR UPDATE which place loks on rows, all of these commands can be used in various combinations. The behavoir can be also different depending on whether AUTOCOMMIT is turned on or off, and whether START TRANSACTION (BEGIN command) is used or not. This is a big topic. — krokodilko

Laurenz Albe Laurenz Albe · Accepted Answer · 2018-06-06T06:24:29

This cannot happen in PostgreSQL.

If a REPEATABLE READ transaction A tries to modify a row that has been modified by a concurrent transaction B after A's snapshot has been taken, A will receive a “serialization error”.

Difference in Repeatable Read Semantics in MySQL and PostgreSQL

1 Answers