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

On Mon, Jan 06, 2014 at 05:14:12PM -0800, AK wrote:
> Also I cannot reproduce a scenario when "applications must not depend on
> results read during a transaction that later aborted;". In this example the
> SELECT itself has failed.
> Can you show an example where a SELECT completes, but the COMMIT blows up?

Actually, no, not for a read-only transaction. It happens that the
final serialization failure check executed on COMMIT only affects
read/write transactions, not read-only ones. That's a pretty specific
implementation detail, though, so I wouldn't necessarily rely on it...

Here's an example of why applications must not depend on results read
during a transaction that later aborted:
  W2: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE  W2: UPDATE t SET count=1 WHERE id=1;  W1: BEGIN TRANSACTION
ISOLATIONLEVEL SERIALIZABLE  W1: SELECT * FROM t WHERE id=1;  W2: COMMIT;  R : BEGIN TRANSACTION ISOLATION LEVEL
SERIALIZABLEREAD ONLY  R : SELECT * FROM t;  R : COMMIT;! W1: UPDATE t SET count=1 WHERE id=2;  W1: COMMIT;
 

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.

So what does this have to do with relying on the results of read-only
transactions that abort? Well, what if you had instead had R ROLLBACK
instead of COMMIT -- maybe because you expected ROLLBACK and COMMIT to
be equivalent for transactions that don't modify the database, or maybe
because something else caused the transaction to abort? When W1 does
its update, it will be checked for serialization failures, but aborted
transactions are (intentionally) not included in those checks. W1 is
therefore allowed to commit; the apparent serial order of execution is
W1 followed by W2, and the results of the aborted transaction R aren't
consistent with that.

Dan

-- 
Dan R. K. Ports                UW CSE                http://drkp.net/