CODEWITHSUNDEEP
mysqlsqlpostgresqltransaction-isolation
Peeyush
Peeyush

Reputation: 726

Difference in Repeatable Read Semantics in MySQL and PostgreSQL

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?

Upvotes: 1

Views: 564

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246413

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”.

Upvotes: 3

Related Questions