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

On Jan6, 2014, at 20:41 , AK <alkuzo@gmail.com> wrote:
> If two transactions both read and write, I can easily reproduce the
> following: "could not serialize access due to read/write dependencies among
> transactions". However, the 9.3 documentation says that "When relying on
> Serializable transactions to prevent anomalies, it is important that any
> data read from a permanent user table not be considered valid until the
> transaction which read it has successfully committed. This is true even for
> read-only transactions".
>
> I cannot have a read-only transaction fail because of serialization
> anomalies. Can someone show me a working example please?

A read-only transaction will abort due to a serialization failure if
observes a state of the database which doesn't exist in any serial transaction
schedule. Here's an example (default isolation level is assumed to be
serializable, of course)

W1: START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
W1: UPDATE t SET count=count+1 WHERE id=1; -- (*2)
W1: SELECT data FROM t WHERE id=2;         -- (*1)
W2: START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
W2: UPDATE t SET count=count+1 WHERE id=2; -- (*1, *2)
W2: COMMIT;
R : START TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
R : SELECT data FROM t WHERE id IN (1,2);  -- (*2)
W1: COMMIT; -- R will now report a serialization error!

Due to (*1), W1 must execute before W2 in any serial schedule, since W1
reads record 2 which is later modified by W2.

Due to (*2), R must execute after W2 but before W1 since it reads record
2 previously modified by W2 and record 1 later modified by W1. (Note that
W1 hasn't committed at time R acquires its snapshot)

The dependencies induced by (*1) or (*2) alone are satisfyable by a serial
schedule, but both together aren't - if W1 must execute before W2 as required
by (*1), then surely every transaction that runs after W2 in such a schedule
also runs after W1, thus contradicting (*2).

Now since (*1) alone isn't contradictory, committing W1 succeeds. That leaves
only the last line, the COMMIT of R, to fail, which it does.

The gist of this example is that whether the state observed by R exists in
any serial transaction schedule or not is only certain after all concurrent
read-write transactions (W1 and W2) have committed. You can avoid the error
above by specifying DEFERRABLE in R's START TRANSACTION command. The session
will then acquire a snapshot and wait for all possibly interfering read-write
transactions to commit. If the snapshot turns out to be observable in some
serial schedule, the session will continue, otherwise the database will
acquire a new snapshot and wait again. Thus, once the START TRANSACTION
with the DEFERRABLE flag has committed, you can be sure that the transaction
won't later be aborted due to a serialization error.

BTW, since this is a question about how to use postgres rather than
how to extend it, it actually belongs on pgsql-general, not on the hackers list.

best regards,
Florian Pflug