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
---
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
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 1In another session:
rhaas=# select * from oops order by 1 for update;
<this blocks>Back to the first session:
rhaas=# commit;
COMMITSecond 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
Attachments:
select-order-by-caution.patchtext/plain; name=select-order-by-caution.patchDownload
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
***************
*** 1281,1287 **** ROLLBACK TO s;
<caution>
<para>
! It is possible for a <command>SELECT</> command using <literal>ORDER
BY</literal> and <literal>FOR UPDATE/SHARE</literal> to return rows out of
order. This is because <literal>ORDER BY</> is applied first.
The command sorts the result, but might then block trying to obtain a lock
--- 1281,1288 ----
<caution>
<para>
! It is possible for a <command>SELECT</> command running at the <literal>READ
! COMMITTED</literal> transaction isolation level and using <literal>ORDER
BY</literal> and <literal>FOR UPDATE/SHARE</literal> to return rows out of
order. This is because <literal>ORDER BY</> is applied first.
The command sorts the result, but might then block trying to obtain a lock
***************
*** 1302,1307 **** SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY column1;
--- 1303,1315 ----
only if concurrent updates of the ordering columns are expected and a
strictly sorted result is required.
</para>
+
+ <para>
+ At the <literal>REPEATABLE READ</literal> or <literal>SERIALIZABLE</literal>
+ transaction isolation level this would cause a serialization failure (with
+ a <literal>SQLSTATE</literal> of <literal>'40001'</literal>), so there is
+ no possibility of receiving rows out of order under these isolation levels.
+ </para>
</caution>
</refsect2>
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