Message-ID: <c66c17ccf7e482197f4542ac50f593544aaacfa3.camel@cybertec.at>
Subject: Re: Confusions regards serializable transaction
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Hannah Huang <hannah.huang.y@gmail.com>,
	pgsql-novice@lists.postgresql.org
Date: Fri, 04 Sep 2020 14:07:01 +0200
In-Reply-To: <AD1ECF70-71F3-404B-84A4-A074D7F08ED3@gmail.com>
References: <AD1ECF70-71F3-404B-84A4-A074D7F08ED3@gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-1.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Precedence: bulk

On Fri, 2020-09-04 at 10:20 +1000, Hannah Huang wrote:
> I’m confused about what will trigger the serializable error. My test environment is PG v12.
> 
> This is how you can produce the test table:
> create table rollover (id int primary key, n int not null);
> insert into rollover values (1,100), (2,10);
> I then 
> 1. start serializable transaction A 
> 2. do an update
> 3. start serializable transaction B
> 4. Do an update
> 5. Commit update of transaction B
> 6. Commit update of transaction A.
> 
> I would be expecting an error throw out from transaction A commit as the data has been changed in transaction B, however, both transactions are executed successfully.  
> 
> Session A:
> 
> [20:14:59] postgres@pgb : 285650 =# begin isolation level serializable;
> BEGIN
> [20:15:01] postgres@pgb : 285650 =# update rollover
>   set n = n + (select n from rollover where id = 2)
>   where id = 1;
> UPDATE 1
> [20:15:06] postgres@pgb : 285650 =#
> [20:15:23] postgres@pgb : 285650 =#
> [20:15:23] postgres@pgb : 285650 =# commit;
> COMMIT
> 
> 
> 
> Session B:
> 
> [20:14:57] postgres@pgb : 286411 =# begin isolation level serializable;
> BEGIN
> [20:15:14] postgres@pgb : 286411 =# update rollover set n = n + 1 where id = 2;
> UPDATE 1
> [20:15:17] postgres@pgb : 286411 =# commit;
> COMMIT
> 
> I then did a bit of change in the SQL statement executed in both transactions by add a select * from rollover table,the commit of transaction A failed as expected. I don’t know WHY…

Even though session A committed after session B, it happens "logically" before B.

You could first execute session A, then session B so that they don't overlap,
and the result would be the same.

That means that there is an equivalent serial execution, so the two transactions
are serializable.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com