Thread: out-of-order caution
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
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
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
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
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
"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
"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
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
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