Thread: BUG #11919: Serializable isolation issue: one session seeing writes from another session.

The following bug has been logged on the website:

Bug reference:      11919
Logged by:          Gavin Panella
Email address:      gavin.panella@canonical.com
PostgreSQL version: 9.3.5
Operating system:   Ubuntu 12.04
Description:

I think I might have found a bug in PostgreSQL 9.3.5 relating to
serializable isolation, where code running within a savepoint can see
data committed in a second session after the commencement of the first,
but that data then "disappears" when the savepoint is rolled-back.

Of course, my understanding may be the bug, but here's how to reproduce
the effect:

1. Open two psql sessions to the same database.

2. Create an example table:

     create table things (a int unique);

3. In the first session:

     begin isolation level serializable;
     insert into things (a) values (1);

   Don't commit yet.

4. In the second session:

     begin isolation level serializable;
     savepoint one;
     insert into things (a) values (1);

   This should hang.

5. In the first session:

     commit;

6. Go back to the second session. It will have failed with:

     ERROR:  duplicate key value violates unique constraint "things_a_key"
     DETAIL:  Key (a)=(1) already exists.

7. Continue in the second session:

     rollback to savepoint one;
     select * from things;

   You should see:

      a
     ---
     (0 rows)

"gavin.panella@canonical.com" <gavin.panella@canonical.com> wrote:

> I think I might have found a bug in PostgreSQL 9.3.5 relating to
> serializable isolation, where code running within a savepoint can see
> data committed in a second session after the commencement of the first,
> but that data then "disappears" when the savepoint is rolled-back.

> 1. Open two psql sessions to the same database.
>
> 2. Create an example table:
>
>     create table things (a int unique);
>
> 3. In the first session:
>
>     begin isolation level serializable;
>     insert into things (a) values (1);
>
>   Don't commit yet.
>
> 4. In the second session:
>
>     begin isolation level serializable;
>     savepoint one;
>     insert into things (a) values (1);
>
>   This should hang.
>
> 5. In the first session:
>
>     commit;
>
> 6. Go back to the second session. It will have failed with:
>
>     ERROR:  duplicate key value violates unique constraint "things_a_key"
>     DETAIL:  Key (a)=(1) already exists.
>
> 7. Continue in the second session:
>
>     rollback to savepoint one;
>     select * from things;
>
>   You should see:
>
>       a
>     ---
>     (0 rows)

Interesting.  I see your point, yet I'm not sure what other
behavior we could reasonably provide.  Once the snapshot is set for
the transaction, we can't use a different one which will show more
recent activity, so that SELECT at the end *has* to return what it
does.  That leaves the question of whether the enforcement of the
unique constraint within the savepoint should behave differently.
Of course, you can make it do so by declaring it to be DEFERRED.
There are several other weird things that can happen when they
aren't deferred, for example:

test=# create table things (a int unique);
CREATE TABLE
test=# insert into things values (1),(2);
INSERT 0 2
test=# update things set a = a + 1;
ERROR:  duplicate key value violates unique constraint "things_a_key"
DETAIL:  Key (a)=(2) already exists.

So I'm not too concerned that this is another thing that doesn't
really behave in an ACID manner if the constraint is not declared
as DEFERRABLE.  If you want truly logical behavior you should
always declare constraints that way and just put up with the
performance hit that brings.  Try playing around with your test
case if the table is defined this way:

create table things (a int unique deferrable initially deferred);

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Kevin Grittner-5 wrote
> "

> gavin.panella@

> " <

> gavin.panella@

> > wrote:
>
>> I think I might have found a bug in PostgreSQL 9.3.5 relating to
>> serializable isolation, where code running within a savepoint can see
>> data committed in a second session after the commencement of the first,
>> but that data then "disappears" when the savepoint is rolled-back.
>
> Interesting.  I see your point, yet I'm not sure what other
> behavior we could reasonably provide.  Once the snapshot is set for
> the transaction, we can't use a different one which will show more
> recent activity, so that SELECT at the end *has* to return what it
> does.  That leaves the question of whether the enforcement of the
> unique constraint within the savepoint should behave differently.
> Of course, you can make it do so by declaring it to be DEFERRED.
> There are several other weird things that can happen when they
> aren't deferred, for example:

Per the documentation (and, yes, it is not a protocol describing quote):

"This level emulates serial transaction execution for all committed
transactions; as if transactions had been executed one after another,
serially, rather than concurrently."

The concept of ROLLBACK TO SAVEPOINT seems like it would be undefined in a
serializable isolation level - in normal use unless the transaction is
self-flagellating concurrency errors causing rollbacks cannot occur.  If
they do rollback should not be possible but a serializing error should
simply occur and force you to retry your transaction in its entirety.

Though I guess you could also interpret this as the second transaction was
serially executed temporally first and so while the INSERT in that
make-believe scenario would have succeeded the rollback is still a valid
command which then undoes that insert so that when you get to the SELECT
statement the things table is empty.

I don't know if the above violates the rules but I thought that while it
enforces some serial order the specific order is not necessarily in commit
order...

David J.




--
View this message in context:
http://postgresql.nabble.com/BUG-11919-Serializable-isolation-issue-one-session-seeing-writes-from-another-session-tp5826436p5826447.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.