PostgreSQL Reference Manual - Volume 1 - SQL Language Reference by The PostgreSQL Global Development Group Paperback (6"x9"), 716 pages ISBN 0954612027 RRP £32.00 ($49.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL Reference Manual Volume 1 Buy the book here! >>> only $49.95

10.2.2.1 Serializable Isolation versus True Serializability

The intuitive meaning (and mathematical definition) of “serializable” execution is that any two successfully committed concurrent transactions will appear to have executed strictly serially, one after the other--although which one appeared to occur first may not be predictable in advance. It is important to realize that forbidding the undesirable behaviors listed in Table 10-1 is not sufficient to guarantee true serializability, and in fact PostgreSQL's Serializable mode does not guarantee serializable execution in this sense. As an example, consider a table mytab, initially containing

 class | value
-------+-------
     1 |    10
     1 |    20
     2 |   100
     2 |   200

Suppose that serializable transaction A computes

SELECT SUM(value) FROM mytab WHERE class = 1;

and then inserts the result (30) as the value in a new row with class = 2. Concurrently, serializable transaction B computes

SELECT SUM(value) FROM mytab WHERE class = 2;

and obtains the result 300, which it inserts in a new row with class = 1. Then both transactions commit. None of the listed undesirable behaviors have occurred, yet we have a result that could not have occurred in either order serially. If A had executed before B, B would have computed the sum 330, not 300, and similarly the other order would have resulted in a different sum computed by A.

To guarantee true mathematical serializability, it is necessary for a database system to enforce predicate locking, which means that a transaction cannot insert or modify a row that would have matched the WHERE condition of a query in another concurrent transaction. For example, once transaction A has executed the query SELECT ... WHERE class = 1, a predicate-locking system would forbid transaction B from inserting any new row with class 1 until A has committed. ⁽⁴⁾ Such a locking system is complex to implement and extremely expensive in execution, since every session must be aware of the details of every query executed by every concurrent transaction. And this large expense is mostly wasted, since in practice most applications do not do the sorts of things that could result in problems. (Certainly the example above is rather contrived and unlikely to represent real software.) For these reasons, PostgreSQL does not implement predicate locking.

In those cases where the possibility of nonserializable execution is a real hazard, problems can be prevented by appropriate use of explicit locking. Further discussion appears in the following sections.