PGCon2011 - Final (2011.06.11)

PGCon 2011
The PostgreSQL Conference

Dan Ports
Kevin Grittner
Day Talks - 2 - 2011-05-20
Room DMS 1120
Start time 16:30
Duration 01:00
ID 333
Event type Lecture
Track 9.1 Features
Language used for presentation English

Serializable Snapshot Isolation in Postgres

Making ISOLATION LEVEL SERIALIZABLE Provide Serializable Isolation

Serializable isolation makes it easier to reason about database behavior because concurrent transactions appear to have executed sequentially. However, Postgres has historically only provided snapshot isolation, even at the SERIALIZABLE isolation level, which can expose anomalous states to queries.

This talk will describe a new SERIALIZABLE isolation level using Serializable Snapshot Isolation (SSI). Unlike the typical two-phase locking approach to serializability, SSI uses the existing snapshot isolation mechanisms and retains most of their performance benefits. It adds a new type of lock that's used to track read dependencies and roll back transactions when dangerous structures appear in the serialization graph, indicating possible anomalies.

We'll describe how SSI works and how we've implemented it for Postgres 9.1, including a new predicate lock manager. We'll explain what users can expect when using the new SERIALIZABLE mode, and present some performance results.