• Home

  • Custom Ecommerce
  • Application Development
  • Database Consulting
  • Cloud Hosting
  • Systems Integration
  • Legacy Business Systems
  • Security & Compliance
  • GIS

  • Expertise

  • About Us
  • Our Team
  • Clients
  • Careers

  • Blog

  • VisionPort

  • Contact
  • Our Blog

    Ongoing observations by End Point Dev people

    PostgreSQL Serializable and Repeatable Read Switcheroo

    Greg Sabino Mullane

    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:

    Postgres version 9.0 and earlierPostgres version 9.1 and later
    Requested isolation levelActual internal isolation levelVersion comparisonActual internal isolation levelRequested isolation level
    READ UNCOMMITTEDRead committedExact sameRead committedREAD UNCOMMITTED
    READ COMMITTEDREAD COMMITTED
    REPEATABLE READSerializableFunctionally identicalRepeatable readREPEATABLE READ
    SERIALIZABLE
     9.1 only!Serializable (true)SERIALIZABLE

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

    bucardo database postgres


    Comments