Re: out-of-order caution

On Thu, Oct 27, 2011 at 1:51 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
>> On Thu, Oct 27, 2011 at 4:41 PM, Kevin Grittner
>> <Kevin(dot)Grittner(at)wicourts(dot)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
---
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;

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