Confusions regards serializable transaction

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Confusions regards serializable transaction

Hannah Huang
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.

Reply | Threaded
Open this post in threaded view
|

Re: Confusions regards serializable transaction

Peter Geoghegan-4
On Thu, Sep 3, 2020 at 5:20 PM Hannah Huang <[hidden email]> 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:

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


Reply | Threaded
Open this post in threaded view
|

Re: Confusions regards serializable transaction

Hannah Huang


> On 4 Sep 2020, at 2:21 pm, Peter Geoghegan <[hidden email]> wrote:
>
> On Thu, Sep 3, 2020 at 5:20 PM Hannah Huang <[hidden email]> 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:
>
> 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.
>
> --
> Peter Geoghegan

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

Reply | Threaded
Open this post in threaded view
|

Re: Confusions regards serializable transaction

Laurenz Albe
In reply to this post by Hannah Huang
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