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

On Jan6, 2014, at 23:28 , AK <alkuzo(at)gmail(dot)com> wrote:
> can you explain why do you state that "default isolation level is assumed to
> be
> serializable, of course", when you explicitly specify isolation level for
> every session - why should he default matter at all?

Sorry, that was a leftover - I initially wrote just START TRANSACTION with
specifying an isolation level.

>
> When I am trying to reproduce the scenario which you have posted, I am
> observing different results.

Hm, yeah, I missed two things.

First, dependency tracking can produce false positives, i.e. assume that
dependencies exist between transactions which are actually independent.
In my example, postgres fails to realize that W2 can be executed after W1,
unless it uses an index scan for the UPDATE in W2. You can avoid that either
by creating an index on the id column, and forcing W2 to use that by setting
enable_seqscan to off, or by creating two tables t1 and t2 instead of one
table t with two records (You'll have to modify the SELECT to scan both tables
too).

Second, since R executes it's SELECT before W1 commits, postgres is already
aware that R poses a problem when W1 commits, and it chooses to cancel W1
instead of R. To avoid that, R needs to do the SELECT after W1 committed.
Yet still force R to acquire a snapshot *before* that commit (without that,
there's no serialization failure since R than simply executes after W1 and
W2), you'll need to do e.g. SELECT 1 after R's START TRANSACTION command.

I think the following should work (or, rather, fail)

CREATE TABLE t (id INT PRIMARY KEY, count INT);
INSERT INTO t (id, count) SELECT i, 0 FROM generate_series(1,2);

W1: START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
W1: UPDATE t SET count=count+1 WHERE id=1;
W1: SELECT count FROM t WHERE id=2;
W2: SET enable_seqscan=off;
W2: START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
W2: UPDATE t SET count=count+1 WHERE id=2;
W2: COMMIT;
R : START TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
R : SELECT 1;
W1: COMMIT;
R : SELECT data FROM t WHERE id IN (1,2); -- Should fail

best regards,
Florian Pflug