Re: out-of-order caution

On Thu, Oct 27, 2011 at 1:51 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Simon Riggs <simon@2ndQuadrant.com> wrote:
>> On Thu, Oct 27, 2011 at 4:41 PM, Kevin Grittner
>> <Kevin.Grittner@wicourts.gov> wrote:
>>> On the docs page for the SELECT statement, there is a caution
>>> which starts with:
>>>
>>> | It is possible for a SELECT command using ORDER BY and FOR
>>> | UPDATE/SHARE to return rows out of order. This is because ORDER
>>> | BY is applied first.
>>>
>>> Is this risk limited to queries running in READ COMMITTED
>>> transactions?  If so, I think that should be mentioned in the
>>> caution.
>>
>> I think it should say that if this occurs with SERIALIZED
>> transactions it will result in a serialisation error.
>>
>> Just to say there is no effect in serializable mode wouldn't be
>> helpful.
>
> Hmm.  At first reading I thought this was related to the
> mixed-snapshot issue in READ COMMITTED, but now I'm not so sure.
> Does anyone know which isolation levels are affected?  Barring that,
> can anyone point to an existing test which demonstrates the problem?
>
> If this can happen in snapshot isolation with just one reader and
> one writer, I doubt that SSI helps with it.  :-(

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
---1534
(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;

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company