Predicate locking in PostgreSQL 9.2.1 with Serializable isolation

From the Transaction Isolation page:

The particular locks acquired during execution of a query will depend on the plan used by the query, and multiple finer-grained locks (e.g., tuple locks) may be combined into fewer coarser-grained locks (e.g., page locks) during the course of the transaction to prevent exhaustion of the memory used to track the locks.

...

• A sequential scan will always necessitate a relation-level predicate lock. This can result in an increased rate of serialization failures.

An EXPLAIN on that SELECT can tel you what the query plan is being taken, but if the table is small (or empty!), PostgreSQL will almost certainly pick a sequential scan instead of referencing the index. This will cause a predicate lock on the entire table, causing serialization failure whenever another transaction does anything to the table.

On my system:

isolation=# EXPLAIN SELECT * from mydevice where cid = 1;                        QUERY PLAN                        ---------------------------------------------------------- Seq Scan on mydevice  (cost=0.00..23.38 rows=5 width=46)   Filter: (cid = 1)(2 rows)

You could try adding an index and force it to use that:

isolation=# CREATE INDEX mydevice_cid_key ON mydevice (cid);CREATE INDEXisolation=# SET enable_seqscan = off;SETisolation=# EXPLAIN SELECT * from mydevice where cid = 1;                                    QUERY PLAN                                    ---------------------------------------------------------------------------------- Index Scan using mydevice_cid_key on mydevice  (cost=0.00..8.27 rows=1 width=46)   Index Cond: (cid = 1)(2 rows)

However, this is not the correct solution. Let's back up a little bit.

Serializable is meant to guarantee that transactions will have exactly the same effect as if they were run one after another, despite the fact that you're actually running these transactions concurrently. PostgreSQL does not have infinite resources, so while it's true that it puts predicate locks on data that your query actually accesses, "data" can mean more than "rows returned".

PostgreSQL chooses to flag serialization failures when it thinks there might be a problem, not when it's certain. (Hence how it generalizes row locks to page locks.) This design choice cause false positives, such as the one in your example. False positives are less than ideal, however, it doesn't affect correctness of the isolation semantics.

The error message is:

ERROR:  could not serialize access due to read/write dependencies among transactionsDETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.HINT:  The transaction might succeed if retried.

That hint is key. Your application needs to catch serialization failures and retry the whole operation. This is true whenever SERIALIZABLE is in play -- it guarantees serial correctness despite concurrency, but it can't do that without the help of your application. Put another way, if you're actually doing concurrent modifications, the only way PostgreSQL can satisfy the isolation requirements is to ask your application to serialize itself. Thus:

It is important that an environment which uses this technique have a generalized way of handling serialization failures (which always return with a SQLSTATE value of '40001'), because it will be very hard to predict exactly which transactions might contribute to the read/write dependencies and need to be rolled back to prevent serialization anomalies.

For those more curious,in Postgres 9.1 Source code, if you look at src/backend/storage/lmgr/README-SSI, there is lot of detailed description about Predicate locking and Serializable transactions

Here's a snippet of the same:

Serializable Snapshot Isolation (SSI) and Predicate Locking ===========================================================

This code is in the lmgr directory because about 90% of it is an implementation of predicate locking, which is required for SSI, rather than being directly related to SSI itself. When another use for predicate locking justifies the effort to tease these two things apart, this README file should probably be split.

Credits:

This feature was developed by Kevin Grittner and Dan R. K. Ports, with review and suggestions from Joe Conway, Heikki Linnakangas, and Jeff Davis. It is based on work published in these papers:

 Michael J. Cahill, Uwe Röhm, and Alan D. Fekete. 2008. Serializable isolation for snapshot databases. In SIGMOD '08: Proceedings of the 2008 ACM SIGMOD international conference on Management of data, pages 729-738, New York, NY, USA. ACM. http://doi.acm.org/10.1145/1376616.1376690 Michael James Cahill. 2009. Serializable Isolation for Snapshot Databases. Sydney Digital Theses. University of Sydney, School of Information Technologies. http://hdl.handle.net/2123/5353