Re: out-of-order caution - Mailing list pgsql-hackers

From Kevin Grittner
Subject Re: out-of-order caution
Date
Msg-id 4EA967320200002500042791@gw.wicourts.gov
Whole thread Raw
In response to Re: out-of-order caution  (Robert Haas <robertmhaas@gmail.com>)
Responses Re: out-of-order caution
List pgsql-hackers
Robert Haas <robertmhaas@gmail.com> wrote:
> Simple test case:
> 
> rhaas=# create table oops (a int);
> CREATE TABLE
> rhaas=# insert into oops values (1), (2), (3), (4);
> INSERT 0 4
> rhaas=# begin;
> BEGIN
> rhaas=# update oops set a = 5 where a = 2;
> UPDATE 1
> 
> In another session:
> 
> rhaas=# select * from oops order by 1 for update;
> <this blocks>
> 
> Back to the first session:
> 
> rhaas=# commit;
> COMMIT
> 
> Second session now returns:
> 
>  a
> ---
>  1
>  5
>  3
>  4
> (4 rows)
> 
> But if you do the same thing at REPEATABLE READ, you get:
> 
> ERROR:  could not serialize access due to concurrent update
> STATEMENT:  select * from oops order by 1 for update;
So it seems to me that the caution about this issue is only
half-right.  Below REPEATABLE READ isolation it behaves as currently
described; REPEATABLE READ or SERIALIZABLE will throw that error. 
That is probably worth noting, since:
(1)  People should understand that they can't get incorrect results
at either of the stricter isolation levels.
(2)  They *can* get a serialization failure involving just two
transactions: a read and a write.  This is not something which
normally happens at any level, so it might tend to surprise people.
No words leap to mind for me.  Anyone else?
-Kevin


pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: out-of-order caution
Next
From: Kerem Kat
Date:
Subject: Re: (PATCH) Adding CORRESPONDING (NULL error)