From: | Hannah Huang <hannah(dot)huang(dot)y(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)lists(dot)postgresql(dot)org |
Subject: | Confusions regards serializable transaction |
Date: | 2020-09-04 00:20:27 |
Message-ID: | AD1ECF70-71F3-404B-84A4-A074D7F08ED3@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(at)pgb : 285650 =# begin isolation level serializable;
BEGIN
[20:15:01] postgres(at)pgb : 285650 =# update rollover
set n = n + (select n from rollover where id = 2)
where id = 1;
UPDATE 1
[20:15:06] postgres(at)pgb : 285650 =#
[20:15:23] postgres(at)pgb : 285650 =#
[20:15:23] postgres(at)pgb : 285650 =# commit;
COMMIT
Session B:
[20:14:57] postgres(at)pgb : 286411 =# begin isolation level serializable;
BEGIN
[20:15:14] postgres(at)pgb : 286411 =# update rollover set n = n + 1 where id = 2;
UPDATE 1
[20:15:17] postgres(at)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 | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2020-09-04 04:21:57 | Re: Confusions regards serializable transaction |
Previous Message | Tom Lane | 2020-09-01 17:56:16 | Re: Implications of resetting Postgres service in Windows |