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

Commit2a4fad1

Browse files
committed
Add NOWAIT option to SELECT FOR UPDATE/SHARE.
Original patch by Hans-Juergen Schoenig, revisions by Karel Zakand Tom Lane.
1 parentca7abcd commit2a4fad1

File tree

28 files changed

+387
-157
lines changed

28 files changed

+387
-157
lines changed

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

Lines changed: 22 additions & 9 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.88 2005/07/14 06:17:36 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.89 2005/08/01 20:31:04 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -30,7 +30,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
3030
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [, ...] ]
3131
[ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
3232
[ OFFSET <replaceable class="parameter">start</replaceable> ]
33-
[ FOR { UPDATE | SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] ]
33+
[ FOR { UPDATE | SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ][ NOWAIT ]]
3434

3535
where <replaceable class="parameter">from_item</replaceable> can be one of:
3636

@@ -151,7 +151,7 @@ where <replaceable class="parameter">from_item</replaceable> can be one of:
151151
</listitem>
152152
</orderedlist>
153153
</para>
154-
154+
155155
<para>
156156
You must have <literal>SELECT</literal> privilege on a table to
157157
read its values. The use of <literal>FOR UPDATE</literal> or
@@ -506,7 +506,7 @@ HAVING <replaceable class="parameter">condition</replaceable>
506506
<replaceable class="parameter">select_statement</replaceable> is
507507
any <command>SELECT</command> statement without an <literal>ORDER
508508
BY</>, <literal>LIMIT</>, <literal>FOR UPDATE</literal>, or
509-
<literal>FOR SHARE</literal> clause.
509+
<literal>FOR SHARE</literal> clause.
510510
(<literal>ORDER BY</> and <literal>LIMIT</> can be attached to a
511511
subexpression if it is enclosed in parentheses. Without
512512
parentheses, these clauses will be taken to apply to the result of
@@ -803,14 +803,14 @@ OFFSET <replaceable class="parameter">start</replaceable>
803803
<para>
804804
The <literal>FOR UPDATE</literal> clause has this form:
805805
<synopsis>
806-
FOR UPDATE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ]
806+
FOR UPDATE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ]
807807
</synopsis>
808808
</para>
809809

810810
<para>
811811
The closely related <literal>FOR SHARE</literal> clause has this form:
812812
<synopsis>
813-
FOR SHARE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ]
813+
FOR SHARE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ]
814814
</synopsis>
815815
</para>
816816

@@ -831,6 +831,18 @@ FOR SHARE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ]
831831
linkend="mvcc">.
832832
</para>
833833

834+
<para>
835+
To prevent the operation from waiting for other transactions to commit,
836+
use the <literal>NOWAIT</> option. <command>SELECT FOR UPDATE
837+
NOWAIT</command> reports an error, rather than waiting, if a selected row
838+
cannot be locked immediately. Note that <literal>NOWAIT</> applies only
839+
to the row-level lock(s) &mdash; the required <literal>ROW SHARE</literal>
840+
table-level lock is still taken in the ordinary way (see
841+
<xref linkend="mvcc">). You can use the <literal>NOWAIT</> option of
842+
<xref linkend="sql-lock" endterm="sql-lock-title">
843+
if you need to acquire the table-level lock without waiting.
844+
</para>
845+
834846
<para>
835847
<literal>FOR SHARE</literal> behaves similarly, except that it
836848
acquires a shared rather than exclusive lock on each retrieved
@@ -843,7 +855,8 @@ FOR SHARE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ]
843855
<para>
844856
It is currently not allowed for a single <command>SELECT</command>
845857
statement to include both <literal>FOR UPDATE</literal> and
846-
<literal>FOR SHARE</literal>.
858+
<literal>FOR SHARE</literal>, nor can different parts of the statement use
859+
both <literal>NOWAIT</> and normal waiting mode.
847860
</para>
848861

849862
<para>
@@ -861,8 +874,8 @@ FOR SHARE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ]
861874
</para>
862875

863876
<para>
864-
It is possible for a <command>SELECT</> command using both
865-
<literal>LIMIT</literal> and <literal>FOR UPDATE/SHARE</literal>
877+
It is possible for a <command>SELECT</> command using both
878+
<literal>LIMIT</literal> and <literal>FOR UPDATE/SHARE</literal>
866879
clauses to return fewer rows than specified by <literal>LIMIT</literal>.
867880
This is because <literal>LIMIT</> is applied first. The command
868881
selects the specified number of rows,

‎doc/src/sgml/ref/select_into.sgml‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/select_into.sgml,v 1.35 2005/04/28 21:47:10 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/select_into.sgml,v 1.36 2005/08/01 20:31:04 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -31,7 +31,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="PARAMETER">expression</replac
3131
[ ORDER BY <replaceable class="PARAMETER">expression</replaceable> [ ASC | DESC | USING <replaceable class="PARAMETER">operator</replaceable> ] [, ...] ]
3232
[ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]
3333
[ OFFSET <replaceable class="PARAMETER">start</replaceable> ]
34-
[ FOR { UPDATE | SHARE } [ OF <replaceable class="PARAMETER">tablename</replaceable> [, ...] ] ]
34+
[ FOR { UPDATE | SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ] ]
3535
</synopsis>
3636
</refsynopsisdiv>
3737

‎doc/src/sgml/sql.sgml‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/sql.sgml,v 1.37 2005/07/14 06:17:35 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/sql.sgml,v 1.38 2005/08/01 20:31:05 tgl Exp $
33
-->
44

55
<chapter id="sql-intro">
@@ -865,7 +865,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="PARAMETER">expression</replac
865865
[ ORDER BY <replaceable class="PARAMETER">expression</replaceable> [ ASC | DESC | USING <replaceable class="PARAMETER">operator</replaceable> ] [, ...] ]
866866
[ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]
867867
[ OFFSET <replaceable class="PARAMETER">start</replaceable> ]
868-
[ FOR { UPDATE | SHARE } [ OF <replaceable class="PARAMETER">class_name</replaceable> [, ...] ] ]
868+
[ FOR { UPDATE | SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ] ]
869869
</synopsis>
870870
</para>
871871

‎src/backend/access/heap/heapam.c‎

Lines changed: 34 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/heap/heapam.c,v 1.195 2005/06/20 18:37:01 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/heap/heapam.c,v 1.196 2005/08/01 20:31:05 tgl Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -1945,7 +1945,7 @@ simple_heap_update(Relation relation, ItemPointer otid, HeapTuple tup)
19451945
*/
19461946
HTSU_Result
19471947
heap_lock_tuple(Relationrelation,HeapTupletuple,Buffer*buffer,
1948-
CommandIdcid,LockTupleModemode)
1948+
CommandIdcid,LockTupleModemode,boolnowait)
19491949
{
19501950
HTSU_Resultresult;
19511951
ItemPointertid=&(tuple->t_self);
@@ -1998,7 +1998,16 @@ heap_lock_tuple(Relation relation, HeapTuple tuple, Buffer *buffer,
19981998
*/
19991999
if (!have_tuple_lock)
20002000
{
2001-
LockTuple(relation,tid,tuple_lock_type);
2001+
if (nowait)
2002+
{
2003+
if (!ConditionalLockTuple(relation,tid,tuple_lock_type))
2004+
ereport(ERROR,
2005+
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
2006+
errmsg("could not obtain lock on row in relation \"%s\"",
2007+
RelationGetRelationName(relation))));
2008+
}
2009+
else
2010+
LockTuple(relation,tid,tuple_lock_type);
20022011
have_tuple_lock= true;
20032012
}
20042013

@@ -2020,7 +2029,17 @@ heap_lock_tuple(Relation relation, HeapTuple tuple, Buffer *buffer,
20202029
elseif (infomask&HEAP_XMAX_IS_MULTI)
20212030
{
20222031
/* wait for multixact to end */
2023-
MultiXactIdWait((MultiXactId)xwait);
2032+
if (nowait)
2033+
{
2034+
if (!ConditionalMultiXactIdWait((MultiXactId)xwait))
2035+
ereport(ERROR,
2036+
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
2037+
errmsg("could not obtain lock on row in relation \"%s\"",
2038+
RelationGetRelationName(relation))));
2039+
}
2040+
else
2041+
MultiXactIdWait((MultiXactId)xwait);
2042+
20242043
LockBuffer(*buffer,BUFFER_LOCK_EXCLUSIVE);
20252044

20262045
/*
@@ -2045,7 +2064,17 @@ heap_lock_tuple(Relation relation, HeapTuple tuple, Buffer *buffer,
20452064
else
20462065
{
20472066
/* wait for regular transaction to end */
2048-
XactLockTableWait(xwait);
2067+
if (nowait)
2068+
{
2069+
if (!ConditionalXactLockTableWait(xwait))
2070+
ereport(ERROR,
2071+
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
2072+
errmsg("could not obtain lock on row in relation \"%s\"",
2073+
RelationGetRelationName(relation))));
2074+
}
2075+
else
2076+
XactLockTableWait(xwait);
2077+
20492078
LockBuffer(*buffer,BUFFER_LOCK_EXCLUSIVE);
20502079

20512080
/*

‎src/backend/access/transam/multixact.c‎

Lines changed: 39 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -42,7 +42,7 @@
4242
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
4343
* Portions Copyright (c) 1994, Regents of the University of California
4444
*
45-
* $PostgreSQL: pgsql/src/backend/access/transam/multixact.c,v 1.5 2005/06/08 15:50:25 tgl Exp $
45+
* $PostgreSQL: pgsql/src/backend/access/transam/multixact.c,v 1.6 2005/08/01 20:31:06 tgl Exp $
4646
*
4747
*-------------------------------------------------------------------------
4848
*/
@@ -558,6 +558,43 @@ MultiXactIdWait(MultiXactId multi)
558558
}
559559
}
560560

561+
/*
562+
* ConditionalMultiXactIdWait
563+
*As above, but only lock if we can get the lock without blocking.
564+
*/
565+
bool
566+
ConditionalMultiXactIdWait(MultiXactIdmulti)
567+
{
568+
boolresult= true;
569+
TransactionId*members;
570+
intnmembers;
571+
572+
nmembers=GetMultiXactIdMembers(multi,&members);
573+
574+
if (nmembers >=0)
575+
{
576+
inti;
577+
578+
for (i=0;i<nmembers;i++)
579+
{
580+
TransactionIdmember=members[i];
581+
582+
debug_elog4(DEBUG2,"ConditionalMultiXactIdWait: trying %d (%u)",
583+
i,member);
584+
if (!TransactionIdIsCurrentTransactionId(member))
585+
{
586+
result=ConditionalXactLockTableWait(member);
587+
if (!result)
588+
break;
589+
}
590+
}
591+
592+
pfree(members);
593+
}
594+
595+
returnresult;
596+
}
597+
561598
/*
562599
* CreateMultiXactId
563600
* Make a new MultiXactId
@@ -590,7 +627,7 @@ CreateMultiXactId(int nxids, TransactionId *xids)
590627
*/
591628
multi=mXactCacheGetBySet(nxids,xids);
592629
if (MultiXactIdIsValid(multi))
593-
{
630+
{
594631
debug_elog2(DEBUG2,"Create: in cache!");
595632
returnmulti;
596633
}

‎src/backend/commands/trigger.c‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $PostgreSQL: pgsql/src/backend/commands/trigger.c,v 1.189 2005/05/30 07:20:58 neilc Exp $
10+
* $PostgreSQL: pgsql/src/backend/commands/trigger.c,v 1.190 2005/08/0120:31:07 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -1598,7 +1598,8 @@ GetTupleForTrigger(EState *estate, ResultRelInfo *relinfo,
15981598
*newSlot=NULL;
15991599
tuple.t_self=*tid;
16001600
ltrmark:;
1601-
test=heap_lock_tuple(relation,&tuple,&buffer,cid,LockTupleExclusive);
1601+
test=heap_lock_tuple(relation,&tuple,&buffer,cid,
1602+
LockTupleExclusive, false);
16021603
switch (test)
16031604
{
16041605
caseHeapTupleSelfUpdated:

‎src/backend/executor/execMain.c‎

Lines changed: 12 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -26,7 +26,7 @@
2626
*
2727
*
2828
* IDENTIFICATION
29-
* $PostgreSQL: pgsql/src/backend/executor/execMain.c,v 1.251 2005/06/28 05:08:55 tgl Exp $
29+
* $PostgreSQL: pgsql/src/backend/executor/execMain.c,v 1.252 2005/08/01 20:31:07 tgl Exp $
3030
*
3131
*-------------------------------------------------------------------------
3232
*/
@@ -559,8 +559,9 @@ InitPlan(QueryDesc *queryDesc, bool explainOnly)
559559
/*
560560
* Have to lock relations selected FOR UPDATE/FOR SHARE
561561
*/
562-
estate->es_rowMark=NIL;
562+
estate->es_rowMarks=NIL;
563563
estate->es_forUpdate=parseTree->forUpdate;
564+
estate->es_rowNoWait=parseTree->rowNoWait;
564565
if (parseTree->rowMarks!=NIL)
565566
{
566567
ListCell*l;
@@ -577,7 +578,7 @@ InitPlan(QueryDesc *queryDesc, bool explainOnly)
577578
erm->relation=relation;
578579
erm->rti=rti;
579580
snprintf(erm->resname,sizeof(erm->resname),"ctid%u",rti);
580-
estate->es_rowMark=lappend(estate->es_rowMark,erm);
581+
estate->es_rowMarks=lappend(estate->es_rowMarks,erm);
581582
}
582583
}
583584

@@ -1010,12 +1011,12 @@ ExecEndPlan(PlanState *planstate, EState *estate)
10101011
}
10111012

10121013
heap_close(estate->es_into_relation_descriptor,NoLock);
1013-
}
1014+
}
10141015

10151016
/*
10161017
* close any relations selected FOR UPDATE/FOR SHARE, again keeping locks
10171018
*/
1018-
foreach(l,estate->es_rowMark)
1019+
foreach(l,estate->es_rowMarks)
10191020
{
10201021
execRowMark*erm=lfirst(l);
10211022

@@ -1156,12 +1157,12 @@ lnext:;
11561157
/*
11571158
* Process any FOR UPDATE or FOR SHARE locking requested.
11581159
*/
1159-
elseif (estate->es_rowMark!=NIL)
1160+
elseif (estate->es_rowMarks!=NIL)
11601161
{
11611162
ListCell*l;
11621163

11631164
lmark:;
1164-
foreach(l,estate->es_rowMark)
1165+
foreach(l,estate->es_rowMarks)
11651166
{
11661167
execRowMark*erm=lfirst(l);
11671168
Bufferbuffer;
@@ -1190,7 +1191,7 @@ lnext:;
11901191
tuple.t_self=*((ItemPointer)DatumGetPointer(datum));
11911192
test=heap_lock_tuple(erm->relation,&tuple,&buffer,
11921193
estate->es_snapshot->curcid,
1193-
lockmode);
1194+
lockmode,estate->es_rowNoWait);
11941195
ReleaseBuffer(buffer);
11951196
switch (test)
11961197
{
@@ -1823,7 +1824,7 @@ EvalPlanQual(EState *estate, Index rti, ItemPointer tid)
18231824
ListCell*l;
18241825

18251826
relation=NULL;
1826-
foreach(l,estate->es_rowMark)
1827+
foreach(l,estate->es_rowMarks)
18271828
{
18281829
if (((execRowMark*)lfirst(l))->rti==rti)
18291830
{
@@ -2128,8 +2129,9 @@ EvalPlanQualStart(evalPlanQual *epq, EState *estate, evalPlanQual *priorepq)
21282129
if (estate->es_topPlan->nParamExec>0)
21292130
epqstate->es_param_exec_vals= (ParamExecData*)
21302131
palloc0(estate->es_topPlan->nParamExec*sizeof(ParamExecData));
2131-
epqstate->es_rowMark=estate->es_rowMark;
2132+
epqstate->es_rowMarks=estate->es_rowMarks;
21322133
epqstate->es_forUpdate=estate->es_forUpdate;
2134+
epqstate->es_rowNoWait=estate->es_rowNoWait;
21332135
epqstate->es_instrument=estate->es_instrument;
21342136
epqstate->es_select_into=estate->es_select_into;
21352137
epqstate->es_into_oids=estate->es_into_oids;

‎src/backend/executor/execUtils.c‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/executor/execUtils.c,v 1.124 2005/06/20 18:37:01 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/execUtils.c,v 1.125 2005/08/01 20:31:07 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -199,8 +199,9 @@ CreateExecutorState(void)
199199

200200
estate->es_processed=0;
201201
estate->es_lastoid=InvalidOid;
202-
estate->es_rowMark=NIL;
202+
estate->es_rowMarks=NIL;
203203
estate->es_forUpdate= false;
204+
estate->es_rowNoWait= false;
204205

205206
estate->es_instrument= false;
206207
estate->es_select_into= false;

0 commit comments

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