Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit46e3a16

Browse files
committed
When FOR UPDATE/SHARE is used with LIMIT, put the LockRows plan node
underneath the Limit node, not atop it. This fixes the old problem that sucha query might unexpectedly return fewer rows than the LIMIT says, due toLockRows discarding updated rows.There is a related problem that LockRows might destroy the sort orderingproduced by earlier steps; but fixing that by pushing LockRows below Sortwould create serious performance problems that are unjustified in manyreal-world applications, as well as potential deadlock problems from lockingmany more rows than expected. Instead, keep the present semantics of applyingFOR UPDATE after ORDER BY within a single query level; but allow the user tospecify the other way by writing FOR UPDATE in a sub-select. To make thatwork, track whether FOR UPDATE appeared explicitly in sub-selects or gotpushed down from the parent, and don't flatten a sub-select that contained anexplicit FOR UPDATE.
1 parent44956c5 commit46e3a16

File tree

13 files changed

+225
-126
lines changed

13 files changed

+225
-126
lines changed

‎doc/src/sgml/ref/select.sgml

Lines changed: 83 additions & 41 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.127 2009/10/27 17:11:18 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.128 2009/10/28 14:55:37 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -1092,22 +1092,12 @@ FOR SHARE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ]
10921092
has already locked a selected row or rows, <command>SELECT FOR
10931093
UPDATE</command> will wait for the other transaction to complete,
10941094
and will then lock and return the updated row (or no row, if the
1095-
row was deleted). For further discussion see <xref
1095+
row was deleted). Within a <literal>SERIALIZABLE</> transaction,
1096+
however, an error will be thrown if a row to be locked has changed
1097+
since the transaction started. For further discussion see <xref
10961098
linkend="mvcc">.
10971099
</para>
10981100

1099-
<para>
1100-
To prevent the operation from waiting for other transactions to commit,
1101-
use the <literal>NOWAIT</> option. <command>SELECT FOR UPDATE
1102-
NOWAIT</command> reports an error, rather than waiting, if a selected row
1103-
cannot be locked immediately. Note that <literal>NOWAIT</> applies only
1104-
to the row-level lock(s) &mdash; the required <literal>ROW SHARE</literal>
1105-
table-level lock is still taken in the ordinary way (see
1106-
<xref linkend="mvcc">). You can use the <literal>NOWAIT</> option of
1107-
<xref linkend="sql-lock" endterm="sql-lock-title">
1108-
if you need to acquire the table-level lock without waiting.
1109-
</para>
1110-
11111101
<para>
11121102
<literal>FOR SHARE</literal> behaves similarly, except that it
11131103
acquires a shared rather than exclusive lock on each retrieved
@@ -1117,13 +1107,26 @@ FOR SHARE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ]
11171107
from performing <command>SELECT FOR SHARE</command>.
11181108
</para>
11191109

1110+
<para>
1111+
To prevent the operation from waiting for other transactions to commit,
1112+
use the <literal>NOWAIT</> option. With <literal>NOWAIT</>, the statement
1113+
reports an error, rather than waiting, if a selected row
1114+
cannot be locked immediately. Note that <literal>NOWAIT</> applies only
1115+
to the row-level lock(s) &mdash; the required <literal>ROW SHARE</literal>
1116+
table-level lock is still taken in the ordinary way (see
1117+
<xref linkend="mvcc">). You can use
1118+
<xref linkend="sql-lock" endterm="sql-lock-title">
1119+
with the <literal>NOWAIT</> option first,
1120+
if you need to acquire the table-level lock without waiting.
1121+
</para>
1122+
11201123
<para>
11211124
If specific tables are named in <literal>FOR UPDATE</literal>
11221125
or <literal>FOR SHARE</literal>,
11231126
then only rows coming from those tables are locked; any other
11241127
tables used in the <command>SELECT</command> are simply read as
11251128
usual. A <literal>FOR UPDATE</literal> or <literal>FOR SHARE</literal>
1126-
clause without a table list affects all tables used in thecommand.
1129+
clause without a table list affects all tables used in thestatement.
11271130
If <literal>FOR UPDATE</literal> or <literal>FOR SHARE</literal> is
11281131
applied to a view or sub-query, it affects all tables used in
11291132
the view or sub-query.
@@ -1151,6 +1154,36 @@ FOR SHARE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ]
11511154
individual table rows; for example they cannot be used with aggregation.
11521155
</para>
11531156

1157+
<para>
1158+
When <literal>FOR UPDATE</literal> or <literal>FOR SHARE</literal>
1159+
appears at the top level of a <command>SELECT</> query, the rows that
1160+
are locked are exactly those that are returned by the query; in the
1161+
case of a join query, the rows locked are those that contribute to
1162+
returned join rows. In addition, rows that satisfied the query
1163+
conditions as of the query snapshot will be locked, although they
1164+
will not be returned if they have since been updated to not satisfy
1165+
the query conditions. If a <literal>LIMIT</> is used, locking stops
1166+
once enough rows have been returned to satisfy the limit (but note that
1167+
rows skipped over by <literal>OFFSET</> will get locked). Similarly,
1168+
if <literal>FOR UPDATE</literal> or <literal>FOR SHARE</literal>
1169+
is used in a cursor's query, only rows actually fetched or stepped past
1170+
by the cursor will be locked.
1171+
</para>
1172+
1173+
<para>
1174+
When <literal>FOR UPDATE</literal> or <literal>FOR SHARE</literal>
1175+
appears in a sub-<command>SELECT</>, the rows locked are those
1176+
returned to the outer query by the sub-query. This might involve
1177+
fewer rows than inspection of the sub-query alone would suggest,
1178+
since conditions from the outer query might be used to optimize
1179+
execution of the sub-query. For example,
1180+
<programlisting>
1181+
SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5;
1182+
</programlisting>
1183+
will lock only rows having <literal>col1 = 5</>, even though that
1184+
condition is not textually within the sub-query.
1185+
</para>
1186+
11541187
<caution>
11551188
<para>
11561189
Avoid locking a row and then modifying it within a later savepoint or
@@ -1177,30 +1210,26 @@ ROLLBACK TO s;
11771210

11781211
<caution>
11791212
<para>
1180-
It is possible for a <command>SELECT</> command using both
1181-
<literal>LIMIT</literal> and <literal>FOR UPDATE/SHARE</literal>
1182-
clauses to return fewer rows than specified by <literal>LIMIT</literal>.
1183-
This is because <literal>LIMIT</> is applied first. The command
1184-
selects the specified number of rows,
1185-
but might then block trying to obtain a lock on one or more of them.
1186-
Once the <literal>SELECT</> unblocks, the row might have been deleted
1187-
or updated so that it does not meet the query <literal>WHERE</> condition
1188-
anymore, in which case it will not be returned.
1189-
</para>
1190-
</caution>
1191-
1192-
<caution>
1193-
<para>
1194-
Similarly, it is possible for a <command>SELECT</> command
1195-
using <literal>ORDER BY</literal> and <literal>FOR
1196-
UPDATE/SHARE</literal> to return rows out of order. This is
1197-
because <literal>ORDER BY</> is applied first. The command
1198-
orders the result, but might then block trying to obtain a lock
1199-
on one or more of the rows. Once the <literal>SELECT</>
1200-
unblocks, one of the ordered columns might have been modified
1201-
and be returned out of order. A workaround is to perform
1202-
<command>SELECT ... FOR UPDATE/SHARE</> and then <command>SELECT
1203-
... ORDER BY</>.
1213+
It is possible for a <command>SELECT</> command using <literal>ORDER
1214+
BY</literal> and <literal>FOR UPDATE/SHARE</literal> to return rows out of
1215+
order. This is because <literal>ORDER BY</> is applied first.
1216+
The command sorts the result, but might then block trying to obtain a lock
1217+
on one or more of the rows. Once the <literal>SELECT</> unblocks, some
1218+
of the ordering column values might have been modified, leading to those
1219+
rows appearing to be out of order (though they are in order in terms
1220+
of the original column values). This can be worked around at need by
1221+
placing the <literal>FOR UPDATE/SHARE</literal> clause in a sub-query,
1222+
for example
1223+
<programlisting>
1224+
SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY column1;
1225+
</programlisting>
1226+
Note that this will result in locking all rows of <structname>mytable</>,
1227+
whereas <literal>FOR UPDATE</> at the top level would lock only the
1228+
actually returned rows. This can make for a significant performance
1229+
difference, particularly if the <literal>ORDER BY</> is combined with
1230+
<literal>LIMIT</> or other restrictions. So this technique is recommended
1231+
only if concurrent updates of the ordering columns are expected and a
1232+
strictly sorted result is required.
12041233
</para>
12051234
</caution>
12061235
</refsect2>
@@ -1541,15 +1570,28 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
15411570
used by <productname>MySQL</productname>. The SQL:2008 standard
15421571
has introduced the clauses <literal>OFFSET ... FETCH {FIRST|NEXT}
15431572
...</literal> for the same functionality, as shown above
1544-
in <xref linkend="sql-limit" endterm="sql-limit-title">, and this
1573+
in <xref linkend="sql-limit" endterm="sql-limit-title">. This
15451574
syntax is also used by <productname>IBM DB2</productname>.
15461575
(Applications written for <productname>Oracle</productname>
15471576
frequently use a workaround involving the automatically
1548-
generated <literal>rownum</literal> column, not available in
1577+
generated <literal>rownum</literal> column,which isnot available in
15491578
PostgreSQL, to implement the effects of these clauses.)
15501579
</para>
15511580
</refsect2>
15521581

1582+
<refsect2>
1583+
<title><literal>FOR UPDATE</> and <literal>FOR SHARE</></title>
1584+
1585+
<para>
1586+
Although <literal>FOR UPDATE</> appears in the SQL standard, the
1587+
standard allows it only as an option of <command>DECLARE CURSOR</>.
1588+
<productname>PostgreSQL</productname> allows it in any <command>SELECT</>
1589+
query as well as in sub-<command>SELECT</>s, but this is an extension.
1590+
The <literal>FOR SHARE</> variant, and the <literal>NOWAIT</> option,
1591+
do not appear in the standard.
1592+
</para>
1593+
</refsect2>
1594+
15531595
<refsect2>
15541596
<title>Nonstandard Clauses</title>
15551597

‎src/backend/nodes/copyfuncs.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
* Portions Copyright (c) 1994, Regents of the University of California
1616
*
1717
* IDENTIFICATION
18-
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.449 2009/10/26 02:26:31 tgl Exp $
18+
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.450 2009/10/28 14:55:38 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -1859,6 +1859,7 @@ _copyRowMarkClause(RowMarkClause *from)
18591859
COPY_SCALAR_FIELD(rti);
18601860
COPY_SCALAR_FIELD(forUpdate);
18611861
COPY_SCALAR_FIELD(noWait);
1862+
COPY_SCALAR_FIELD(pushedDown);
18621863

18631864
returnnewnode;
18641865
}
@@ -2223,6 +2224,7 @@ _copyQuery(Query *from)
22232224
COPY_SCALAR_FIELD(hasSubLinks);
22242225
COPY_SCALAR_FIELD(hasDistinctOn);
22252226
COPY_SCALAR_FIELD(hasRecursive);
2227+
COPY_SCALAR_FIELD(hasForUpdate);
22262228
COPY_NODE_FIELD(cteList);
22272229
COPY_NODE_FIELD(rtable);
22282230
COPY_NODE_FIELD(jointree);

‎src/backend/nodes/equalfuncs.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,7 @@
2222
* Portions Copyright (c) 1994, Regents of the University of California
2323
*
2424
* IDENTIFICATION
25-
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.371 2009/10/26 02:26:31 tgl Exp $
25+
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.372 2009/10/28 14:55:38 tgl Exp $
2626
*
2727
*-------------------------------------------------------------------------
2828
*/
@@ -860,6 +860,7 @@ _equalQuery(Query *a, Query *b)
860860
COMPARE_SCALAR_FIELD(hasSubLinks);
861861
COMPARE_SCALAR_FIELD(hasDistinctOn);
862862
COMPARE_SCALAR_FIELD(hasRecursive);
863+
COMPARE_SCALAR_FIELD(hasForUpdate);
863864
COMPARE_NODE_FIELD(cteList);
864865
COMPARE_NODE_FIELD(rtable);
865866
COMPARE_NODE_FIELD(jointree);
@@ -2198,6 +2199,7 @@ _equalRowMarkClause(RowMarkClause *a, RowMarkClause *b)
21982199
COMPARE_SCALAR_FIELD(rti);
21992200
COMPARE_SCALAR_FIELD(forUpdate);
22002201
COMPARE_SCALAR_FIELD(noWait);
2202+
COMPARE_SCALAR_FIELD(pushedDown);
22012203

22022204
return true;
22032205
}

‎src/backend/nodes/outfuncs.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.370 2009/10/26 02:26:31 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.371 2009/10/28 14:55:38 tgl Exp $
1212
*
1313
* NOTES
1414
* Every node type that can appear in stored rules' parsetrees *must*
@@ -1987,6 +1987,7 @@ _outQuery(StringInfo str, Query *node)
19871987
WRITE_BOOL_FIELD(hasSubLinks);
19881988
WRITE_BOOL_FIELD(hasDistinctOn);
19891989
WRITE_BOOL_FIELD(hasRecursive);
1990+
WRITE_BOOL_FIELD(hasForUpdate);
19901991
WRITE_NODE_FIELD(cteList);
19911992
WRITE_NODE_FIELD(rtable);
19921993
WRITE_NODE_FIELD(jointree);
@@ -2036,6 +2037,7 @@ _outRowMarkClause(StringInfo str, RowMarkClause *node)
20362037
WRITE_UINT_FIELD(rti);
20372038
WRITE_BOOL_FIELD(forUpdate);
20382039
WRITE_BOOL_FIELD(noWait);
2040+
WRITE_BOOL_FIELD(pushedDown);
20392041
}
20402042

20412043
staticvoid

‎src/backend/nodes/readfuncs.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.226 2009/10/26 02:26:32 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.227 2009/10/28 14:55:38 tgl Exp $
1212
*
1313
* NOTES
1414
* Path and Plan nodes do not have any readfuncs support, because we
@@ -203,6 +203,7 @@ _readQuery(void)
203203
READ_BOOL_FIELD(hasSubLinks);
204204
READ_BOOL_FIELD(hasDistinctOn);
205205
READ_BOOL_FIELD(hasRecursive);
206+
READ_BOOL_FIELD(hasForUpdate);
206207
READ_NODE_FIELD(cteList);
207208
READ_NODE_FIELD(rtable);
208209
READ_NODE_FIELD(jointree);
@@ -295,6 +296,7 @@ _readRowMarkClause(void)
295296
READ_UINT_FIELD(rti);
296297
READ_BOOL_FIELD(forUpdate);
297298
READ_BOOL_FIELD(noWait);
299+
READ_BOOL_FIELD(pushedDown);
298300

299301
READ_DONE();
300302
}

‎src/backend/optimizer/plan/planner.c

Lines changed: 37 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.260 2009/10/26 02:26:33 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.261 2009/10/28 14:55:38 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1638,19 +1638,7 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
16381638
}
16391639

16401640
/*
1641-
* If there is a LIMIT/OFFSET clause, add the LIMIT node.
1642-
*/
1643-
if (parse->limitCount||parse->limitOffset)
1644-
{
1645-
result_plan= (Plan*)make_limit(result_plan,
1646-
parse->limitOffset,
1647-
parse->limitCount,
1648-
offset_est,
1649-
count_est);
1650-
}
1651-
1652-
/*
1653-
* Finally, if there is a FOR UPDATE/SHARE clause, add the LockRows node.
1641+
* If there is a FOR UPDATE/SHARE clause, add the LockRows node.
16541642
* (Note: we intentionally test parse->rowMarks not root->rowMarks here.
16551643
* If there are only non-locking rowmarks, they should be handled by
16561644
* the ModifyTable node instead.)
@@ -1660,6 +1648,23 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
16601648
result_plan= (Plan*)make_lockrows(result_plan,
16611649
root->rowMarks,
16621650
SS_assign_special_param(root));
1651+
/*
1652+
* The result can no longer be assumed sorted, since locking might
1653+
* cause the sort key columns to be replaced with new values.
1654+
*/
1655+
current_pathkeys=NIL;
1656+
}
1657+
1658+
/*
1659+
* Finally, if there is a LIMIT/OFFSET clause, add the LIMIT node.
1660+
*/
1661+
if (parse->limitCount||parse->limitOffset)
1662+
{
1663+
result_plan= (Plan*)make_limit(result_plan,
1664+
parse->limitOffset,
1665+
parse->limitCount,
1666+
offset_est,
1667+
count_est);
16631668
}
16641669

16651670
/* Compute result-relations list if needed */
@@ -1795,20 +1800,33 @@ preprocess_rowmarks(PlannerInfo *root)
17951800

17961801
/*
17971802
* Convert RowMarkClauses to PlanRowMark representation.
1798-
*
1799-
* Note: currently, it is syntactically impossible to have FOR UPDATE
1800-
* applied to an update/delete target rel. If that ever becomes
1801-
* possible, we should drop the target from the PlanRowMark list.
18021803
*/
18031804
prowmarks=NIL;
18041805
foreach(l,parse->rowMarks)
18051806
{
18061807
RowMarkClause*rc= (RowMarkClause*)lfirst(l);
1807-
PlanRowMark*newrc=makeNode(PlanRowMark);
1808+
RangeTblEntry*rte=rt_fetch(rc->rti,parse->rtable);
1809+
PlanRowMark*newrc;
18081810

1811+
/*
1812+
* Currently, it is syntactically impossible to have FOR UPDATE
1813+
* applied to an update/delete target rel. If that ever becomes
1814+
* possible, we should drop the target from the PlanRowMark list.
1815+
*/
18091816
Assert(rc->rti!=parse->resultRelation);
1817+
1818+
/*
1819+
* Ignore RowMarkClauses for subqueries; they aren't real tables
1820+
* and can't support true locking. Subqueries that got flattened
1821+
* into the main query should be ignored completely. Any that didn't
1822+
* will get ROW_MARK_COPY items in the next loop.
1823+
*/
1824+
if (rte->rtekind!=RTE_RELATION)
1825+
continue;
1826+
18101827
rels=bms_del_member(rels,rc->rti);
18111828

1829+
newrc=makeNode(PlanRowMark);
18121830
newrc->rti=newrc->prti=rc->rti;
18131831
if (rc->forUpdate)
18141832
newrc->markType=ROW_MARK_EXCLUSIVE;
@@ -1838,7 +1856,6 @@ preprocess_rowmarks(PlannerInfo *root)
18381856
continue;
18391857

18401858
newrc=makeNode(PlanRowMark);
1841-
18421859
newrc->rti=newrc->prti=i;
18431860
/* real tables support REFERENCE, anything else needs COPY */
18441861
if (rte->rtekind==RTE_RELATION)

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp