Isolation Repeatable Read in PostgreSQL versus MySQL
To avoid having concurrent transactions interfere with each other, SQL engines implement isolation as a feature. This property corresponds to the I letter in the well known ACID acronym, the other properties being Atomicity, Consistency and Durability.
Isolation happens to be configurable, with different levels that correspond to different behaviors when executing concurrent transactions.
The SQL-1992 standard defines four level of isolation, from the weakest to the strongest:
-
Read Uncommitted: a transaction sees the changes of other transactions before they are committed. PostgreSQL does not implement this mode.
-
Read Committed: a transaction sees changes from others as soon as they have committed.
-
Repeatable Read: when a transaction reads back a row that has been already read by a previous query, it must read the same values, even if the row has been changed by another transaction that has committed in the meantime.
-
Serializable: a transaction cannot see or produce results that could not have occurred if other transactions were not concurrently changing the data. PostgreSQL implements true serializability since version 9.1
If you’re porting code from MySQL or writing code targeting both PostgreSQL and MySQL/MariaDB, you might want to care about the default level being Read Committed in PostgreSQL and Repeatable Read in MySQL or MariaDB (with the InnoDB engine). By the way, the SQL standard says that Serializable should be used by default, so both engines don’t follow this recommendation (just like Oracle, MS SQL Server, DB2,Sybase ASE… which ignore it as well).
Anyway, let’s see first a very simple example to illustrate the difference between MySQL and PostgreSQL in their default isolation levels:
Example 1
Say we have a single-column table with 4 values:
CREATE TABLE a(x int);
INSERT INTO a VALUES (1),(2),(3),(4);
A transaction Tx1 computes the sum and average of the values in two distinct queries, while a transaction Tx2 inserts a new value, with an execution schedule such that the insertion is committed between the queries in Tx1.
With PostgreSQL in its default isolation Read Committed:
-- Tx1 -- Tx2
=# BEGIN; =# BEGIN;
BEGIN BEGIN
=# SELECT SUM(x) FROM a;
sum
-----
10
(1 row)
=# INSERT INTO a VALUES(50);
INSERT 0 1
=# COMMIT;
COMMIT
=# SELECT AVG(x) FROM a;
avg
---------------------
11.6666666666666667
(1 row)
=# COMMIT;
COMMIT
The value 50 has been ignored by the SUM but taken in by the AVG
query.
If we look only at Tx1, its two results are mathematically
inconsistent: of course you can’t have the average greater than the
sum.
This is because in Read Committed, each new SQL statement starts
with a state of the database that includes all the changes
of the other transactions that already committed. In this case
that’s the insertion by Tx2 of 50.
With MySQL/MariaDB and its default isolation Repeatable Read, the result is different:
-- Tx1 -- Tx2
mysql> BEGIN; mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
> SELECT SUM(x) FROM a;
+--------+
| SUM(x) |
+--------+
| 10 |
+--------+
1 row in set (0.00 sec)
> INSERT INTO a VALUES(50);
Query OK, 1 row affected (0.00 sec)
> COMMIT;
Query OK, 0 rows affected (0.02 sec)
> SELECT AVG(x) FROM a;
+--------+
| AVG(x) |
+--------+
| 2.5000 |
+--------+
1 row in set (0.00 sec)
> COMMIT;
Query OK, 0 rows affected (0.00 sec)
With MySQL/MariaDB, the row with 50 inserted by Tx2 is ignored by Tx1,
due to the Repeatable Read isolation: the values previously read for
the SUM operation must be reused for the AVG operation.
To get the same behavior with PostgreSQL in this example, we’d need to set the transaction to a higher isolation level (Repeatable Read ou Serializable)
The point is that simple concurrent queries can produce different results across different engines in their default configurations.
Now we could think that in order to get the same results with MySQL and PostgreSQL, we should just use them at the same isolation levels. That sounds reasonable, but that’s not true in general. In fact, behaviors can differ quite a bit across SQL engines at the same isolation levels. Let’s see another example to illustrate this.
Example 2
Now let’s use Repeatable Read in both engines. To switch the session to this isolation level independently of the default value, we want to execute the following statements:
for MySQL/MariaDB:
> SET SESSION transaction isolation level Repeatable Read;
for PostgreSQL:
=# SET default_transaction_isolation TO 'Repeatable Read';
Let’s create two empty tables:
CREATE TABLE a(xa int);
CREATE TABLE b(xb int);
Now let’s execute two concurrent transactions that insert into each
table the count(*) of the other table.
Here’s the PostgreSQL transcript:
-- Tx1 -- Tx2
=# BEGIN; =# BEGIN;
BEGIN BEGIN
=# INSERT INTO a
SELECT count(*) FROM b;
INSERT 0 1
=# INSERT INTO b
SELECT count(*) FROM a;
INSERT 0 1
=# COMMIT;
COMMIT
=# SELECT COUNT(*) FROM a;
count
-------
0
(1 row)
=# COMMIT;
COMMIT
After Tx1 and Tx2 have finished, here are the results:
=# SELECT * FROM a;
xa
----
0
(1 row)
=# SELECT * FROM b;
xb
----
0
(1 row)
The value 0 ends up in the two tables because, for each of the two transactions,
the table from which it counts the rows is empty according to its visibility.
But with MySQL/MariaDB, the behavior and the final result are different, as shown in the transcript below. Tx2 waits for Tx1 and incorporates its results before continuing, instead of being entirely isolated from Tx1.
-- Tx1 -- Tx2
mysql> BEGIN; mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
> INSERT INTO a SELECT count(*) FROM b;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
> INSERT INTO b SELECT count(*)
FROM a;
-- (Tx2 gets blocked here)
> COMMIT;
Query OK, 0 rows affected (0.01 sec)
-- (Tx2 continues)
Query OK, 1 row affected (5.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
> -- this result differs from PG
> SELECT COUNT(*) FROM a;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
> COMMIT;
Query OK, 0 rows affected (0.01 sec)
-- Tx1 and Tx2 are done
> SELECT * FROM a;
+------+
| xa |
+------+
| 0 |
+------+
1 row in set (0.01 sec)
> -- this result differs from PG
> SELECT * FROM b;
+------+
| xb |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Despite this sequence of instructions being very simple and the transactions
being in Repeatable Read in the two engines, the result differs
between Postgres and MySQL/MariaDB, in the sense that the b table has
a row with 0 in PostgreSQL and 1 in MySQL.
Since Tx1 and Tx2 don’t write to the same row (in fact in this case they
don’t even write to the same table), for PostgreSQL the INSERT
of Tx1 does not interfere at all with Tx2.
Tx2 does not need to wait for Tx1 to finish, it can count the rows
in a through its snapshot.
Whereas with MySQL, Tx2 waits for Tx1 to end, and counts the
rows in a after incorporating what Tx1 has done (one insertion).
So Tx1 and Tx2 are less isolated. From the point of view of
a PostgreSQL user accustomed to its Repeatable Read mode,
this behavior is quite surprising (in fact this part seems like
Read Committed in PostgreSQL).
And there are more differences. Let’s see another one with a variant
of this example in which Tx2 queries the a table at the
start of the transaction.
Example 3
So this example is a variant of the previous one, in which Tx2 reads
and returns count(*) FROM a at the beginning.
For PostgreSQL, it doesn’t change anything: during all of Tx2,
count(*) FROM a is always 0, whether as a subquery
of an INSERT statement or as the main query.
But for MySQL, there’s a clear difference of behavior to avoid the “Non Repeatable Read” phenomenon, which the minimum expected for the Repeatable Read isolation level.
Let’s look at the following transcript with MySQL/MariaDB:
-- Tx1 -- Tx2
mysql> BEGIN; mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
> INSERT INTO a SELECT count(*) FROM b;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
> -- Initiate a repeatable read
> SELECT count(*) FROM a;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
> INSERT INTO b SELECT count(*)
FROM a;
-- (Tx2 gets blocked)
> COMMIT;
Query OK, 0 rows affected (0.01 sec)
-- (Tx2 gets unblocked)
Query OK, 1 row affected (3.13 sec)
Records: 1 Duplicates: 0 Warnings: 0
> SELECT * FROM b;
+------+
| xb |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
> -- repeat the read
> SELECT COUNT(*) FROM a;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
> COMMIT;
Query OK, 0 rows affected (0.01 sec)
The final results in a and b are the same than in the previous example with
table b containing 1, but this time at the end of Tx2, it turns out
that SELECT COUNT(*) FROM a returns 0, while in the previous example
it returned 1.
The difference is that a “Non Repeatable Read” phenomenon is now
forbidden because there was a SELECT count(*) FROM a
which returned 0 at the start of the transaction.
So any subsequent SELECT count(*) FROM a must produce 0.
But that is true only when it’s directly run, not when it’s
executed as a subquery through INSERT INTO b SELECT count(*) FROM a.
That’s why there this weird inconsistency between the 1 to be found
in b.xb and the 0 returned to the client, despite the fact
that it’s the result of the same expression in the same
Repeatable Read transaction.
Also this difference in result persists during the rest of Tx2. For instance before the COMMIT we could have this sequence of statements:
mysql> SELECT * FROM b;
+------+
| xb |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
mysql> INSERT INTO b SELECT COUNT(*) FROM a;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM b;
+------+
| xb |
+------+
| 1 |
| 1 |
+------+
2 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM a;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
Example 4
There’s another very significant difference in how Repeatable Read in PostgreSQL and MySQL/MariaDB deal with write conflicts, (the kind of conflict that can generate “Lost Updates”). PostgreSQL with its Snapshot Isolation technique will avoid a write conflict on the same row by aborting one of the transactions.
By contrast,MySQL/MariaDB at the same isolation level does not abort a transaction, but forbids the second write (a delete, in that case) without emitting any error.
Let’s have again a table with a single column with 4 integer numbers from 1 to 4. The two concurrent transactions are Tx1 which subtracts 1 from each value, and Tx2 which suppresses the row corresponding to the maximum value in the table. The idea is that Tx2 want to remove a row that Tx1 changes concurrently.
CREATE TABLE list(x int);
INSERT INTO list VALUES (1),(2),(3),(4);
PostgreSQL transcript:
-- Tx1 -- Tx2
=# BEGIN; =# BEGIN;
BEGIN BEGIN
=# UPDATE list SET x=x-1; =# SELECT * FROM list;
x
---
1
2
3
4
(4 rows)
=# DELETE FROM list WHERE x=4;
-- (Tx2 gets blocked)
=# COMMIT
COMMIT
-- Tx2 ends in error
ERROR: could not serialize access
due to concurrent update
=# \echo :SQLSTATE
40001
=# ROLLBACK;
ROLLBACK
At the Repeatable Read isolation level, the engine rejects the write by Tx2 (broadly speaking, a delete is a write) on a row that Tx1 has modified. That refusal consists of aborting the transaction with a specific error code (SQLSTATE 40001).
With MySQL/MariaDB as below, there is no transaction abort.
The DELETE does not error out but does not remove the row with x=4
that Tx1 changed,
even though this row stays visible from Tx2 until its end.
-- Tx1 -- Tx2
mysql> BEGIN; mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
> UPDATE list SET x=x-1; > SELECT * FROM list;
Query OK, 4 rows affected (0.00 sec) +------+
Rows matched: 4 Changed: 4 Warnings: 0 | x |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
> DELETE FROM list WHERE x=4;
-- (Tx2 gets blocked)
Query OK, 0 rows affected (5.73 sec)
> COMMIT;
Query OK, 0 rows affected (0.01 sec)
> SELECT * FROM list;
+------+
| x |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.01 sec)
> DELETE FROM list WHERE x=4;
Query OK, 0 rows affected (0.00 sec)
> SELECT * FROM list;
+------+
| x |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
> COMMIT;
Query OK, 0 rows affected (0.00 sec)
When repeating the SELECT * FROM list and the DELETE, we can see that the
row with x=4 is still visible, and that the DELETE doesn’t remove it.
The only indication that the engine does not want to delete it is the
0 rows affected in the information returned along with
the DELETE.
By comparison with PostgreSQL, Tx2 not deleting the row looks like the behavior of its Read Committed level, but the fact that this row persists to be visible looks like the behavior of the Repeatable Read level. In that sense the Repeatable Read level in MySQL feels like it sits somewhere between the Read Committed and the Repeatable Read of Postgres.
Conclusion
When porting applications from MySQL to PostgreSQL or vice-versa, or designing services that need to work on both, we should expect different behaviors with concurrent transactions, even when configured at the same isolation level.
The SQL standard says that certain isolation levels must avoid certain phenomena, but each SQL implementation has its own interpretation of this, with visible results that are clearly different for the same SQL code.
PostgreSQL uses Read Committed by default, whereas MySQL has chosen Repeatable Read, which is better isolated, but when PostgreSQL transactions use the Repeatable Read level, they’re more isolated than MySQL transactions.