Our Blog

PostgreSQL Serializable and Repeatable Read Switcheroo

By Greg Sabino Mullane
September 28, 2011

PostgreSQL allows for different transaction isolation levels to be specified. Because Bucardo needs a consistent snapshot of each database involved in replication to perform its work, the first thing that the Bucardo daemon does when connecting to a remote PostgreSQL database is:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE;

The ‘READ WRITE’ bit sets us in read/write mode, just in case the entire database has been set to read only (a quick and easy way to make your slave databases non-writeable!). It also sets the transaction isolation level to ‘SERIALIZABLE’. At least, it used to. Now Bucardo uses ‘REPEATABLE READ’ like this:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ READ WRITE;

Why the change? In version 9.1 of PostgreSQL the concept of SSI (Serializable Snapshot Isolation) was introduced. How it actually works is a little complicated (follow the link for more detail), but before 9.1 PostgreSQL was only sort of doing serialized transactions when you asked for serializable mode. What it was really doing was repeatable read and not trying to really serialize the transactions. In 9.1, PostgreSQL is doing true serializable transactions. It also adds a new distinct ‘internal’ transaction mode, ‘repeatable read’, which does exactly what the old ‘serializable’ used to do. Finally, if you issue a ‘repeatable read’ on a pre-9.1 database, it silently upgrades it to the old ‘serializable’ mode.

So in summary, if your application was using ‘SERIALIZABLE’ before, you can now replace that with ‘REPEATABLE READ’ and get the exact same behavior as before, regardless of the version. Of course, if you want true serializable transactions, use SERIALIZABLE. It will continue to mean the same as ‘REPEATABLE READ’ in pre-9.1 databases, and provide true serializability in 9.1 and beyond. (I haven’t determined yet if Bucardo is going to use this new level, as it comes with a little bit of overhead)

Since this can be a little confusing, here’s a handy chart showing how version 9.1 changed the meaning of SERIALIZABLE, and added a new ‘internal’ isolation level:

Congratulations and thanks to Kevin Grittner and Dan Ports for making true serializability a reality!

bucardo database postgres