Subject: Confusions regards serializable transaction

From: Hannah Huang <[email-masked]>
Date: Fri, 4 Sep 2020 10:20:27 +1000

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.

---

From: Peter Geoghegan <[email-masked]>
Date: Thu, 3 Sep 2020 21:21:57 -0700

> This is how you can produce the test table:

Serializable isolation level promises to emulate serial transaction
execution for all committed transactions. I believe that your example
does not show behavior that breaks that specific promise.

I haven't thought about it much, but maybe the confusion here has
something to do with the fact that the transaction snapshot is not
acquired during the "begin isolation level serializable" statement.
It's actually acquired lazily, during the first
non-transaction-control statement in the transaction.

-- 
Peter Geoghegan

---

From: Hannah Huang <[email-masked]>
Date: Fri, 4 Sep 2020 16:05:50 +1000

> On 4 Sep 2020, at 2:21 pm, Peter Geoghegan <[email-masked]> wrote:
> 

Hi Peter,
Thank you very much for replying my question.

My confusion is actually:

Transaction B updated the n value of  id 2, while transaction A needs to update n value for id 1 referencing the n value of id 2.

If the transaction is executed in a serialized way, then the n value of id 2 will be changed in transaction B first, and the change will be reflected in the value of id 1in transaction A. However, the two transactions are not executed concurrently - transaction A executed successfully without seeing changes made in transaction B. Is that a break of serializable isolation level?

Thanks,
Hannah

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 04 Sep 2020 14:07:01 +0200

> 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