Обсуждение: Confusions regards serializable transaction
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.
On Thu, Sep 3, 2020 at 5:20 PM Hannah Huang <hannah.huang.y@gmail.com> 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
> On 4 Sep 2020, at 2:21 pm, Peter Geoghegan <pg@bowt.ie> wrote: > > On Thu, Sep 3, 2020 at 5:20 PM Hannah Huang <hannah.huang.y@gmail.com> 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 valueof 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, andthe 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 serializableisolation level? Thanks, Hannah