Robert Haas <robertmhaas(at)gmail(dot)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