Re: Question about the isolation level and visible

娄帅 <louis(dot)hust(dot)ml(at)gmail(dot)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