Re: postgres 9.3.6, serialize error with two independent, serially consistent transactions.. - Mailing list pgsql-bugs

Thanks for this clarification.

If you look at the documentation here:
  http://www.postgresql.org/docs/9.4/static/transaction-iso.html

The Serialization example (with the sum being 330/300 based on execution
order) implies that the opposite would work, when there were serially
consistent updates.

However, the second transaction could still fail (unless retried).

 > There is no guarantee that there will be
 > no false positives (where a serialization failure is generated
 > where with infinite time and resources to analyze everything it
 > could have been avoided).

It would be very helpful to have the above paragraph (and in fact the
larger exposition you typed below) in the documentation itself! The
current example on that page implies that serialization *only* fails
because of order related inconsistencies, however, it can also fail
because of false positives and this should really be mentioned as well.

Currently, this is only vaguely alluded to, via 1 sentence (the last
line) on that page:
   "A sequential scan will always necessitate a relation-level predicate
lock"
which doesn't really mean too much to a lot of people who are getting
started..

Best,
--Hursh

Kevin Grittner wrote:
> Hursh Jain<hurshjain@beesell.com>  wrote:
>
>> In the example below, client #1 and client #2 are not doing
>> anything that can possibly cause a inconsistent serial order of
>> execution. You can pick and choose whichever one you want to run
>> first, the final result will be 100% consistent and exactly the
>> same, either way.
>>
>> So why does postgres 9.3.6 give an error to the second client ?
>> Unless I am reading this docs wrong, this looks like a major bug.
>
> This is not a bug.  What use of serializable transactions
> guarantees is that the behavior of any set of successfully
> committed serializable transactions is consistent with some serial
> order of execution of those transactions.  If you retry the second
> transaction (which got the serialization failure) it will succeed
> and you have behavior consistent with either order of execution, so
> the conditions are met.  There is no guarantee that there will be
> no false positives (where a serialization failure is generated
> where with infinite time and resources to analyze everything it
> could have been avoided).
>
> Note that if you add a primary key on the id column, and use values
> that do not cause duplicate keys, you do not get any error.  Without
> an index a full table scan is needed, which causes a predicate lock
> which has a rw-conflict with any insert to the table.  Two
> transactions doing this concurrently will cause a serialization
> failure.  If we tracked enough information to avoid this case,
> resource usage and performance would not be in a range most would
> consider acceptable; heuristics are needed, which inevitably allows
> false positives in some circumstances.
>
> If you try your example on any database which uses strict two phase
> locking (S2PL) to implement serializable transactions (e.g., MS SQL
> Server, DB2, MySQL, Berkeley DB, etc.), I think you will find that
> you also get a false positive serialization failure in the form of
> a deadlock.  In databases which do not actually provide
> serializable behavior when you ask for it (e.g., Oracle), you will
> not get an error.  If you want that level of transaction isolation
> in PostgreSQL you can request REPEATABLE READ, and you will
> likewise not get an error.  For a description of the differences
> between these isolation levels see the docs:
>
> http://www.postgresql.org/docs/current/interactive/mvcc.html
>
> For some examples of cases where you get different results between
> these two isolation levels, see:
>
> https://wiki.postgresql.org/wiki/SSI
>
> PostgreSQL gives you a choice of which behavior you prefer.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company