Postgres Pro
Downloads
Home   >   mailing lists

Thread: Question about the isolation level and visible

Question about the isolation level and visible

From
娄帅
Date:
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! 

Re: Question about the isolation level and visible

From
Xiang Gan
Date:

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of 娄帅
Sent: Friday, June 26, 2015 9:12 AM
To: pgsql-general@postgresql.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

 

Re: Question about the isolation level and visible

From
Kevin Grittner
Date:
娄帅 <louis.hust.ml@gmail.com> wrote:

> 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？

This was recently clarified in the documentation by changing:

| [...] as of the start of the transaction, [...]

to:

| [...] as of the start of the first non-transaction-control
| statement in the transaction, [...]

We don't acquire the snapshot at START TRANSACTION time because it
may be necessary to acquire locks or set transaction properties
(e.g., READ ONLY) before the snapshot is acquired.  You could force
acquisition of a snapshot by running a short query (e.g., SELECT
1;) in session 1 before starting the transaction in session 2.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company