Thread: out-of-order caution

out-of-order caution

From
"Kevin Grittner"
Date:
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.
-Kevin


Re: out-of-order caution

From
Simon Riggs
Date:
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.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Re: out-of-order caution

From
"Kevin Grittner"
Date:
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.  :-(
-Kevin


Re: out-of-order caution

From
Robert Haas
Date:
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


Re: out-of-order caution

From
"Kevin Grittner"
Date:
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


Re: out-of-order caution

From
Tom Lane
Date:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Simon Riggs <simon@2ndQuadrant.com> wrote:
>> On Thu, Oct 27, 2011 at 4:41 PM, Kevin Grittner
>> <Kevin.Grittner@wicourts.gov> wrote:
>>> | 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.

>> I think it should say that if this occurs with SERIALIZED
>> transactions it will result in a serialisation error.
> Hmm.  At first reading I thought this was related to the
> mixed-snapshot issue in READ COMMITTED, but now I'm not so sure. 

Simon's comment is correct.  If you do a SELECT FOR UPDATE/SHARE in a
non-READ-COMMITTED transaction, and it turns out that someone modified
the tuple before you could lock it, you'll get a serialization error
(cf ExecLockRows()), not updated data.  So out-of-order sorting is
not possible.
        regards, tom lane


Re: out-of-order caution

From
Tom Lane
Date:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> (2)  They *can* get a serialization failure involving just two
> transactions: a read and a write.

Only if you ignore the difference between SELECT FOR UPDATE/SHARE and
plain SELECT.  I think calling the former a "read" is a conceptual error
to start with.  It has the same locking and synchronization behavior as
a write.
        regards, tom lane


Re: out-of-order caution

From
"Kevin Grittner"
Date:
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.

OK, doc patch attached.

-Kevin


Attachment

Re: out-of-order caution

From
Robert Haas
Date:
On Fri, Oct 28, 2011 at 10:44 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> OK, doc patch attached.

Committed.

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