public inbox for [email protected]  

help / color / mirror / Atom feed

From: Hannah Huang <[email protected]>
To: [email protected]
Subject: Confusions regards serializable transaction
Date: Fri, 4 Sep 2020 10:20:27 +1000
Message-ID: <[email protected]> (raw)

Hi,

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…


Thanks a lot!
Hannah.

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Confusions regards serializable transaction
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox