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