On Jan6, 2014, at 23:28 , AK <alk...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers