Обсуждение: [HACKERS] postgresql transactons not fully isolated
Michael Malis via: http://malisper.me/postgres-transactions-arent-fully-isolated/ has determined that postgresql transactions are not fully isolated even when using serializable isolationl level. If I prep a table, ints via: postgres=# create table ints (n int); CREATE TABLE postgres=# insert into ints values (1); INSERT 0 1 postgres=# insert into ints values (2); INSERT 0 1 and then run two concurrent in serializable isolation mode transactions like this: T1: BEGIN T1: UPDATE ints SET n = n + 1; T2: BEGIN T2: DELETE FROM ints where n = 2; -- blocks T1: COMMIT; -- T2 frees T2: SELECT * FROM ints; -- both rows 2 and 3 visible T2: COMMIT: My understanding is that for serializable transactions, the result is correct as long as you can play back transactions in either order, one after another, when they overlap and get that result. This is clearly not the case since when played in either order you'd end up with one row. I guess the failure occurs there is some kind of separation between when the row is initially looked up and the deletion is qualified. This is likely not a problem in practice, but is Micheal right in is assessment that we are not precisely following the spec? merlin
Merlin Moncure <mmoncure@gmail.com> writes: > Michael Malis via: > http://malisper.me/postgres-transactions-arent-fully-isolated/ has > determined that postgresql transactions are not fully isolated even > when using serializable isolationl level. > If I prep a table, ints via: > postgres=# create table ints (n int); > CREATE TABLE > postgres=# insert into ints values (1); > INSERT 0 1 > postgres=# insert into ints values (2); > INSERT 0 1 > and then run two concurrent in serializable isolation mode > transactions like this: > T1: BEGIN > T1: UPDATE ints SET n = n + 1; > T2: BEGIN > T2: DELETE FROM ints where n = 2; -- blocks > T1: COMMIT; -- T2 frees > T2: SELECT * FROM ints; -- both rows 2 and 3 visible > T2: COMMIT: Hm, I get ERROR: could not serialize access due to concurrent update in T2 immediately after T1 commits. What version are you testing? Are you sure you selected serializable mode for both xacts? (I think it would work like this even if only T2 is marked serializable, but didn't test.) regards, tom lane
On 06/20/2017 12:33 PM, Merlin Moncure wrote: > postgres=# create table ints (n int); > CREATE TABLE > postgres=# insert into ints values (1); > INSERT 0 1 > postgres=# insert into ints values (2); > INSERT 0 1 > > T1: BEGIN > T1: UPDATE ints SET n = n + 1; > T2: BEGIN > T2: DELETE FROM ints where n = 2; -- blocks > T1: COMMIT; -- T2 frees > T2: SELECT * FROM ints; -- both rows 2 and 3 visible > T2: COMMIT: For me (in PG 9.5 at $work), at the instant of the commit in T1, T2 says: ERROR: could not serialize access due to concurrent update Is it indicated what PG version Michael Malis is using? Is it clear that transaction_isolation was set to serializable? I don't actually see that claim in the linked post. I see the example (about halfway down, under "Skipped Modification"), but it doesn't claim that transaction_isolation was set to serializable at the time, unless I skimmed over it somehow. It seems more of a demonstration of what can happen under a different isolation setting. -Chap
On 06/20/2017 03:08 PM, Chapman Flack wrote: > For me (in PG 9.5 at $work), at the instant of the commit in T1, > T2 says: > ERROR: could not serialize access due to concurrent update I get that result in 'serializable' and in 'repeatable read'. I get the reported result (DELETE 0 and a table containing 2 and 3) in both 'read committed' and 'read uncommitted'. -Chap
On Tue, Jun 20, 2017 at 12:22 PM, Chapman Flack <chap@anastigmatix.net> wrote: > I get the reported result (DELETE 0 and a table containing 2 and 3) > in both 'read committed' and 'read uncommitted'. Practically speaking those are a single transaction isolation mode. https://www.postgresql.org/docs/10/static/transaction-iso.html I think Merlin has mis-read the article he linked to. The example being used there never claims to be done under serialization and seems to describe an example of the perils of relying on the default isolation level. David J.
On Tue, Jun 20, 2017 at 2:34 PM, David G. Johnston <david.g.johnston@gmail.com> wrote: > On Tue, Jun 20, 2017 at 12:22 PM, Chapman Flack <chap@anastigmatix.net> wrote: >> I get the reported result (DELETE 0 and a table containing 2 and 3) >> in both 'read committed' and 'read uncommitted'. > > Practically speaking those are a single transaction isolation mode. > > https://www.postgresql.org/docs/10/static/transaction-iso.html > > I think Merlin has mis-read the article he linked to. The example > being used there never claims to be done under serialization and seems > to describe an example of the perils of relying on the default > isolation level. oops -- could be operator error :-) merlin
On Tue, Jun 20, 2017 at 2:58 PM, Merlin Moncure <mmoncure@gmail.com> wrote: > On Tue, Jun 20, 2017 at 2:34 PM, David G. Johnston > <david.g.johnston@gmail.com> wrote: >> On Tue, Jun 20, 2017 at 12:22 PM, Chapman Flack <chap@anastigmatix.net> wrote: >>> I get the reported result (DELETE 0 and a table containing 2 and 3) >>> in both 'read committed' and 'read uncommitted'. >> >> Practically speaking those are a single transaction isolation mode. >> >> https://www.postgresql.org/docs/10/static/transaction-iso.html >> >> I think Merlin has mis-read the article he linked to. The example >> being used there never claims to be done under serialization and seems >> to describe an example of the perils of relying on the default >> isolation level. > > oops -- could be operator error :-) yep, I made the rookie mistake of setting transaction isolation level (which immediately evaporated since it wasn't bracketed by the transaction), but not for the default. Sorry for the noise, serialization failures are raised and that is acceptable behavior per spec AIUI. merlin