Re: Re: How to reproduce serialization failure for a read only transaction.

Dan Ports <drkp(at)csail(dot)mit(dot)edu> wrote:
> On Mon, Jan 06, 2014 at 05:14:12PM -0800, AK wrote:

> If you try this, it'll cause a serialization failure on the line
> marked with a '!'. W1 saw (1,0) in the table, so W1 appears to
> have executed before W2. But R saw both (1,1) and (2,0) in the
> table, and that has to be a consistent snapshot of the database
> state, meaning W2 appears to have executed before W1. That's an
> inconsistency, so something has to be rolled back. This
> particular anomaly requires all three of the transactions, and so
> it can't be detected until W1 does its UPDATE. Postgres detects
> the conflict at that point and rolls back W1.

Yeah, neither of the provided examples rolled back the read only
transaction itself; the read only transaction caused a situation
where something needed to be rolled back, but since we try to roll
back a transaction which has a good chance of succeeding on retry,
the read only transaction is not usually a good candidate.  I
created a new example on the Wiki page where the read only
transaction itself must be rolled back because both of the other
transactions involved have already committed:

https://wiki.postgresql.org/wiki/SSI#Rollover

Regarding other questions on the thread:

I have no objections to moving the Wiki examples into the docs, but
it seemed like a lot to include, and I'm not sure where it belongs.
Ideas?

Regarding the different results AK got, I set
default_transaction_isolation = 'serializable' on my connections
before running these for two reasons.
(1)  It keeps the examples more concise.
(2)  I think most people using serializable transactions in
PostgreSQL set the default and don't set the transaction isolation
level on each transaction, since (unlike strategies which rely on
blocking, like S2PL) all transactions must be participating in the
stricter isolation level for it to be reliable.  In fact, given the
performance benefits of declaring transactions READ ONLY when
possible, I have seen shops that make *that* a default, too, and
override it for transactions which need to write.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company