public inbox for [email protected]
help / color / mirror / Atom feedConfusions regards serializable transaction
4+ messages / 3 participants
[nested] [flat]
* Confusions regards serializable transaction
@ 2020-09-04 00:20 Hannah Huang <[email protected]>
2020-09-04 04:21 ` Re: Confusions regards serializable transaction Peter Geoghegan <[email protected]>
2020-09-04 12:07 ` Re: Confusions regards serializable transaction Laurenz Albe <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: Hannah Huang @ 2020-09-04 00:20 UTC (permalink / raw)
To: [email protected]
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Confusions regards serializable transaction
2020-09-04 00:20 Confusions regards serializable transaction Hannah Huang <[email protected]>
@ 2020-09-04 04:21 ` Peter Geoghegan <[email protected]>
2020-09-04 06:05 ` Re: Confusions regards serializable transaction Hannah Huang <[email protected]>
1 sibling, 1 reply; 4+ messages in thread
From: Peter Geoghegan @ 2020-09-04 04:21 UTC (permalink / raw)
To: Hannah Huang <[email protected]>; +Cc: [email protected]
On Thu, Sep 3, 2020 at 5:20 PM Hannah Huang <[email protected]> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Confusions regards serializable transaction
2020-09-04 00:20 Confusions regards serializable transaction Hannah Huang <[email protected]>
2020-09-04 04:21 ` Re: Confusions regards serializable transaction Peter Geoghegan <[email protected]>
@ 2020-09-04 06:05 ` Hannah Huang <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Hannah Huang @ 2020-09-04 06:05 UTC (permalink / raw)
To: Peter Geoghegan <[email protected]>; +Cc: [email protected]
> On 4 Sep 2020, at 2:21 pm, Peter Geoghegan <[email protected]> wrote:
>
> On Thu, Sep 3, 2020 at 5:20 PM Hannah Huang <[email protected]> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Confusions regards serializable transaction
2020-09-04 00:20 Confusions regards serializable transaction Hannah Huang <[email protected]>
@ 2020-09-04 12:07 ` Laurenz Albe <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2020-09-04 12:07 UTC (permalink / raw)
To: Hannah Huang <[email protected]>; [email protected]
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-09-04 12:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-09-04 00:20 Confusions regards serializable transaction Hannah Huang <[email protected]>
2020-09-04 04:21 ` Peter Geoghegan <[email protected]>
2020-09-04 06:05 ` Hannah Huang <[email protected]>
2020-09-04 12:07 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox