Re: Question about the isolation level and visible

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of 娄帅
Sent: Friday, June 26, 2015 9:12 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Question about the isolation level and visible

Hi, all,

I start two session with the following execute time order:

session1: START TRANSACTION ISOLATION LEVEL SERIALIZABLE;

session2: INSERT INTO t1 values(1);
session2: COMMIT;

session1: SELECT * FROM t1;

I found session1 got the value 1 which is inserted by session2.

Is there any way to make session1 not see the value session2 inserted？

For mysql, I know there is a syntax:
START TRANSACTION WITH CONSISTENT SNAPSHOT.

I search the PG START syntax, but found none.

Any idea will be appreciated!

From this:

http://dev.mysql.com/doc/refman/5.6/en/commit.html

It seems that the WITH CONSISTENT SNAPSHOT modifier is only valid on isolation level REPEATABLE READ. But session1 is specified as on isolation level SERIALIZABLE. Thus, the provided scenario will get value 1 from session1 as I tested in MySQL. Can you describe the session scenarios more precisely?

--
With regards,
Xiang