Thread: [HACKERS] postgresql transactons not fully isolated

[HACKERS] postgresql transactons not fully isolated

From
Merlin Moncure
Date:
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



Re: [HACKERS] postgresql transactons not fully isolated

From
Tom Lane
Date:
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



Re: [HACKERS] postgresql transactons not fully isolated

From
J Chapman Flack
Date:
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



Re: [HACKERS] postgresql transactons not fully isolated

From
Chapman Flack
Date:
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



Re: [HACKERS] postgresql transactons not fully isolated

From
"David G. Johnston"
Date:
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.



Re: [HACKERS] postgresql transactons not fully isolated

From
Merlin Moncure
Date:
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



Re: [HACKERS] postgresql transactons not fully isolated

From
Merlin Moncure
Date:
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