Skip to content

Commit df630b0

Browse files
alvherrealvherre
committed
Implement SKIP LOCKED for row-level locks
This clause changes the behavior of SELECT locking clauses in the presence of locked rows: instead of causing a process to block waiting for the locks held by other processes (or raise an error, with NOWAIT), SKIP LOCKED makes the new reader skip over such rows. While this is not appropriate behavior for general purposes, there are some cases in which it is useful, such as queue-like tables. Catalog version bumped because this patch changes the representation of stored rules. Reviewed by Craig Ringer (based on a previous attempt at an implementation by Simon Riggs, who also provided input on the syntax used in the current patch), David Rowley, and Álvaro Herrera. Author: Thomas Munro
1 parent c421efd commit df630b0

38 files changed

+907
-121
lines changed

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

Lines changed: 17 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -45,7 +45,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
4545
[ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
4646
[ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
4747
[ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ]
48-
[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ] [...] ]
48+
[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
4949

5050
<phrase>where <replaceable class="parameter">from_item</replaceable> can be one of:</phrase>
5151

@@ -1284,7 +1284,7 @@ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] {
12841284
The locking clause has the general form
12851285

12861286
<synopsis>
1287-
FOR <replaceable>lock_strength</> [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ]
1287+
FOR <replaceable>lock_strength</> [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ]
12881288
</synopsis>
12891289

12901290
where <replaceable>lock_strength</> can be one of
@@ -1360,9 +1360,15 @@ KEY SHARE
13601360

13611361
<para>
13621362
To prevent the operation from waiting for other transactions to commit,
1363-
use the <literal>NOWAIT</> option. With <literal>NOWAIT</>, the statement
1364-
reports an error, rather than waiting, if a selected row
1365-
cannot be locked immediately. Note that <literal>NOWAIT</> applies only
1363+
use either the <literal>NOWAIT</> or <literal>SKIP LOCKED</literal>
1364+
option. With <literal>NOWAIT</>, the statement reports an error, rather
1365+
than waiting, if a selected row cannot be locked immediately.
1366+
With <literal>SKIP LOCKED</literal>, any selected rows that cannot be
1367+
immediately locked are skipped. Skipping locked rows provides an
1368+
inconsistent view of the data, so this is not suitable for general purpose
1369+
work, but can be used to avoid lock contention with multiple consumers
1370+
accessing a queue-like table.
1371+
Note that <literal>NOWAIT</> and <literal>SKIP LOCKED</literal> apply only
13661372
to the row-level lock(s) &mdash; the required <literal>ROW SHARE</literal>
13671373
table-level lock is still taken in the ordinary way (see
13681374
<xref linkend="mvcc">). You can use
@@ -1394,7 +1400,9 @@ KEY SHARE
13941400
then it is processed as if it was only specified by the strongest one.
13951401
Similarly, a table is processed
13961402
as <literal>NOWAIT</> if that is specified in any of the clauses
1397-
affecting it.
1403+
affecting it. Otherwise, it is processed
1404+
as <literal>SKIP LOCKED</literal> if that is specified in any of the
1405+
clauses affecting it.
13981406
</para>
13991407

14001408
<para>
@@ -1931,9 +1939,9 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
19311939
<productname>PostgreSQL</productname> allows it in any <command>SELECT</>
19321940
query as well as in sub-<command>SELECT</>s, but this is an extension.
19331941
The <literal>FOR NO KEY UPDATE</>, <literal>FOR SHARE</> and
1934-
<literal>FOR KEY SHARE</> variants,
1935-
as well as the <literal>NOWAIT</> option,
1936-
do not appear in the standard.
1942+
<literal>FOR KEY SHARE</> variants, as well as the <literal>NOWAIT</>
1943+
and <literal>SKIP LOCKED</literal> options, do not appear in the
1944+
standard.
19371945
</para>
19381946
</refsect2>
19391947

‎doc/src/sgml/sql.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -863,7 +863,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="PARAMETER">expression</replac
863863
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
864864
[ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]
865865
[ OFFSET <replaceable class="PARAMETER">start</replaceable> ]
866-
[ FOR { UPDATE | SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ] [...] ]
866+
[ FOR { UPDATE | SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
867867
</synopsis>
868868
</para>
869869

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

Lines changed: 74 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -4090,7 +4090,7 @@ get_mxact_status_for_lock(LockTupleMode mode, bool is_update)
40904090
* cid: current command ID (used for visibility test, and stored into
40914091
* tuple's cmax if lock is successful)
40924092
* mode: indicates if shared or exclusive tuple lock is desired
4093-
* nowait: if true, ereport rather than blocking if lock not available
4093+
* wait_policy: what to do if tuple lock is not available
40944094
* follow_updates: if true, follow the update chain to also lock descendant
40954095
* tuples.
40964096
*
@@ -4103,6 +4103,7 @@ get_mxact_status_for_lock(LockTupleMode mode, bool is_update)
41034103
* HeapTupleMayBeUpdated: lock was successfully acquired
41044104
* HeapTupleSelfUpdated: lock failed because tuple updated by self
41054105
* HeapTupleUpdated: lock failed because tuple updated by other xact
4106+
* HeapTupleWouldBlock: lock couldn't be acquired and wait_policy is skip
41064107
*
41074108
* In the failure cases, the routine fills *hufd with the tuple's t_ctid,
41084109
* t_xmax (resolving a possible MultiXact, if necessary), and t_cmax
@@ -4114,7 +4115,7 @@ get_mxact_status_for_lock(LockTupleMode mode, bool is_update)
41144115
*/
41154116
HTSU_Result
41164117
heap_lock_tuple(Relation relation, HeapTuple tuple,
4117-
CommandId cid, LockTupleMode mode, bool nowait,
4118+
CommandId cid, LockTupleMode mode, LockWaitPolicy wait_policy,
41184119
bool follow_updates,
41194120
Buffer *buffer, HeapUpdateFailureData *hufd)
41204121
{
@@ -4220,16 +4221,28 @@ heap_lock_tuple(Relation relation, HeapTuple tuple,
42204221
*/
42214222
if (!have_tuple_lock)
42224223
{
4223-
if (nowait)
4224+
switch (wait_policy)
42244225
{
4225-
if (!ConditionalLockTupleTuplock(relation, tid, mode))
4226-
ereport(ERROR,
4227-
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
4228-
errmsg("could not obtain lock on row in relation \"%s\"",
4229-
RelationGetRelationName(relation))));
4226+
case LockWaitBlock:
4227+
LockTupleTuplock(relation, tid, mode);
4228+
break;
4229+
case LockWaitSkip:
4230+
if (!ConditionalLockTupleTuplock(relation, tid, mode))
4231+
{
4232+
result = HeapTupleWouldBlock;
4233+
/* recovery code expects to have buffer lock held */
4234+
LockBuffer(*buffer, BUFFER_LOCK_EXCLUSIVE);
4235+
goto failed;
4236+
}
4237+
break;
4238+
case LockWaitError:
4239+
if (!ConditionalLockTupleTuplock(relation, tid, mode))
4240+
ereport(ERROR,
4241+
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
4242+
errmsg("could not obtain lock on row in relation \"%s\"",
4243+
RelationGetRelationName(relation))));
4244+
break;
42304245
}
4231-
else
4232-
LockTupleTuplock(relation, tid, mode);
42334246
have_tuple_lock = true;
42344247
}
42354248

@@ -4432,21 +4445,35 @@ heap_lock_tuple(Relation relation, HeapTuple tuple,
44324445
if (status >= MultiXactStatusNoKeyUpdate)
44334446
elog(ERROR, "invalid lock mode in heap_lock_tuple");
44344447

4435-
/* wait for multixact to end */
4436-
if (nowait)
4448+
/* wait for multixact to end, or die trying */
4449+
switch (wait_policy)
44374450
{
4438-
if (!ConditionalMultiXactIdWait((MultiXactId) xwait,
4439-
status, infomask, relation,
4440-
NULL))
4441-
ereport(ERROR,
4442-
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
4443-
errmsg("could not obtain lock on row in relation \"%s\"",
4444-
RelationGetRelationName(relation))));
4451+
case LockWaitBlock:
4452+
MultiXactIdWait((MultiXactId) xwait, status, infomask,
4453+
relation, &tuple->t_data->t_ctid, XLTW_Lock, NULL);
4454+
break;
4455+
case LockWaitSkip:
4456+
if (!ConditionalMultiXactIdWait((MultiXactId) xwait,
4457+
status, infomask, relation,
4458+
NULL))
4459+
{
4460+
result = HeapTupleWouldBlock;
4461+
/* recovery code expects to have buffer lock held */
4462+
LockBuffer(*buffer, BUFFER_LOCK_EXCLUSIVE);
4463+
goto failed;
4464+
}
4465+
break;
4466+
case LockWaitError:
4467+
if (!ConditionalMultiXactIdWait((MultiXactId) xwait,
4468+
status, infomask, relation,
4469+
NULL))
4470+
ereport(ERROR,
4471+
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
4472+
errmsg("could not obtain lock on row in relation \"%s\"",
4473+
RelationGetRelationName(relation))));
4474+
4475+
break;
44454476
}
4446-
else
4447-
MultiXactIdWait((MultiXactId) xwait, status, infomask,
4448-
relation, &tuple->t_data->t_ctid,
4449-
XLTW_Lock, NULL);
44504477

44514478
/* if there are updates, follow the update chain */
44524479
if (follow_updates &&
@@ -4491,18 +4518,30 @@ heap_lock_tuple(Relation relation, HeapTuple tuple,
44914518
}
44924519
else
44934520
{
4494-
/* wait for regular transaction to end */
4495-
if (nowait)
4521+
/* wait for regular transaction to end, or die trying */
4522+
switch (wait_policy)
44964523
{
4497-
if (!ConditionalXactLockTableWait(xwait))
4498-
ereport(ERROR,
4499-
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
4500-
errmsg("could not obtain lock on row in relation \"%s\"",
4501-
RelationGetRelationName(relation))));
4524+
case LockWaitBlock:
4525+
XactLockTableWait(xwait, relation, &tuple->t_data->t_ctid,
4526+
XLTW_Lock);
4527+
break;
4528+
case LockWaitSkip:
4529+
if (!ConditionalXactLockTableWait(xwait))
4530+
{
4531+
result = HeapTupleWouldBlock;
4532+
/* recovery code expects to have buffer lock held */
4533+
LockBuffer(*buffer, BUFFER_LOCK_EXCLUSIVE);
4534+
goto failed;
4535+
}
4536+
break;
4537+
case LockWaitError:
4538+
if (!ConditionalXactLockTableWait(xwait))
4539+
ereport(ERROR,
4540+
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
4541+
errmsg("could not obtain lock on row in relation \"%s\"",
4542+
RelationGetRelationName(relation))));
4543+
break;
45024544
}
4503-
else
4504-
XactLockTableWait(xwait, relation, &tuple->t_data->t_ctid,
4505-
XLTW_Lock);
45064545

45074546
/* if there are updates, follow the update chain */
45084547
if (follow_updates &&
@@ -4564,7 +4603,8 @@ heap_lock_tuple(Relation relation, HeapTuple tuple,
45644603
failed:
45654604
if (result != HeapTupleMayBeUpdated)
45664605
{
4567-
Assert(result == HeapTupleSelfUpdated || result == HeapTupleUpdated);
4606+
Assert(result == HeapTupleSelfUpdated || result == HeapTupleUpdated ||
4607+
result == HeapTupleWouldBlock);
45684608
Assert(!(tuple->t_data->t_infomask & HEAP_XMAX_INVALID));
45694609
hufd->ctid = tuple->t_data->t_ctid;
45704610
hufd->xmax = HeapTupleHeaderGetUpdateXid(tuple->t_data);

‎src/backend/commands/trigger.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2706,7 +2706,7 @@ ltrmark:;
27062706
tuple.t_self = *tid;
27072707
test = heap_lock_tuple(relation, &tuple,
27082708
estate->es_output_cid,
2709-
lockmode, false /* wait */ ,
2709+
lockmode, LockWaitBlock,
27102710
false, &buffer, &hufd);
27112711
switch (test)
27122712
{

‎src/backend/executor/execMain.c

Lines changed: 30 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -836,7 +836,7 @@ InitPlan(QueryDesc *queryDesc, int eflags)
836836
erm->prti = rc->prti;
837837
erm->rowmarkId = rc->rowmarkId;
838838
erm->markType = rc->markType;
839-
erm->noWait = rc->noWait;
839+
erm->waitPolicy = rc->waitPolicy;
840840
ItemPointerSetInvalid(&(erm->curCtid));
841841
estate->es_rowMarks = lappend(estate->es_rowMarks, erm);
842842
}
@@ -1871,7 +1871,7 @@ EvalPlanQual(EState *estate, EPQState *epqstate,
18711871
/*
18721872
* Get and lock the updated version of the row; if fail, return NULL.
18731873
*/
1874-
copyTuple = EvalPlanQualFetch(estate, relation, lockmode, false /* wait */,
1874+
copyTuple = EvalPlanQualFetch(estate, relation, lockmode, LockWaitBlock,
18751875
tid, priorXmax);
18761876

18771877
if (copyTuple == NULL)
@@ -1930,20 +1930,24 @@ EvalPlanQual(EState *estate, EPQState *epqstate,
19301930
* estate - executor state data
19311931
* relation - table containing tuple
19321932
* lockmode - requested tuple lock mode
1933-
* noWait - wait mode to pass to heap_lock_tuple
1933+
* wait_policy - requested lock wait policy
19341934
* *tid - t_ctid from the outdated tuple (ie, next updated version)
19351935
* priorXmax - t_xmax from the outdated tuple
19361936
*
19371937
* Returns a palloc'd copy of the newest tuple version, or NULL if we find
19381938
* that there is no newest version (ie, the row was deleted not updated).
1939+
* We also return NULL if the tuple is locked and the wait policy is to skip
1940+
* such tuples.
1941+
*
19391942
* If successful, we have locked the newest tuple version, so caller does not
19401943
* need to worry about it changing anymore.
19411944
*
19421945
* Note: properly, lockmode should be declared as enum LockTupleMode,
19431946
* but we use "int" to avoid having to include heapam.h in executor.h.
19441947
*/
19451948
HeapTuple
1946-
EvalPlanQualFetch(EState *estate, Relation relation, int lockmode, bool noWait,
1949+
EvalPlanQualFetch(EState *estate, Relation relation, int lockmode,
1950+
LockWaitPolicy wait_policy,
19471951
ItemPointer tid, TransactionId priorXmax)
19481952
{
19491953
HeapTuple copyTuple = NULL;
@@ -1992,18 +1996,25 @@ EvalPlanQualFetch(EState *estate, Relation relation, int lockmode, bool noWait,
19921996
if (TransactionIdIsValid(SnapshotDirty.xmax))
19931997
{
19941998
ReleaseBuffer(buffer);
1995-
if (noWait)
1999+
switch (wait_policy)
19962000
{
1997-
if (!ConditionalXactLockTableWait(SnapshotDirty.xmax))
1998-
ereport(ERROR,
1999-
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
2000-
errmsg("could not obtain lock on row in relation \"%s\"",
2001-
RelationGetRelationName(relation))));
2001+
case LockWaitBlock:
2002+
XactLockTableWait(SnapshotDirty.xmax,
2003+
relation, &tuple.t_data->t_ctid,
2004+
XLTW_FetchUpdated);
2005+
break;
2006+
case LockWaitSkip:
2007+
if (!ConditionalXactLockTableWait(SnapshotDirty.xmax))
2008+
return NULL; /* skip instead of waiting */
2009+
break;
2010+
case LockWaitError:
2011+
if (!ConditionalXactLockTableWait(SnapshotDirty.xmax))
2012+
ereport(ERROR,
2013+
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
2014+
errmsg("could not obtain lock on row in relation \"%s\"",
2015+
RelationGetRelationName(relation))));
2016+
break;
20022017
}
2003-
else
2004-
XactLockTableWait(SnapshotDirty.xmax,
2005-
relation, &tuple.t_data->t_ctid,
2006-
XLTW_FetchUpdated);
20072018
continue; /* loop back to repeat heap_fetch */
20082019
}
20092020

@@ -2030,7 +2041,7 @@ EvalPlanQualFetch(EState *estate, Relation relation, int lockmode, bool noWait,
20302041
*/
20312042
test = heap_lock_tuple(relation, &tuple,
20322043
estate->es_output_cid,
2033-
lockmode, noWait,
2044+
lockmode, wait_policy,
20342045
false, &buffer, &hufd);
20352046
/* We now have two pins on the buffer, get rid of one */
20362047
ReleaseBuffer(buffer);
@@ -2076,6 +2087,10 @@ EvalPlanQualFetch(EState *estate, Relation relation, int lockmode, bool noWait,
20762087
/* tuple was deleted, so give up */
20772088
return NULL;
20782089

2090+
case HeapTupleWouldBlock:
2091+
ReleaseBuffer(buffer);
2092+
return NULL;
2093+
20792094
default:
20802095
ReleaseBuffer(buffer);
20812096
elog(ERROR, "unrecognized heap_lock_tuple status: %u",

‎src/backend/executor/nodeLockRows.c

Lines changed: 11 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -133,11 +133,15 @@ ExecLockRows(LockRowsState *node)
133133

134134
test = heap_lock_tuple(erm->relation, &tuple,
135135
estate->es_output_cid,
136-
lockmode, erm->noWait, true,
136+
lockmode, erm->waitPolicy, true,
137137
&buffer, &hufd);
138138
ReleaseBuffer(buffer);
139139
switch (test)
140140
{
141+
case HeapTupleWouldBlock:
142+
/* couldn't lock tuple in SKIP LOCKED mode */
143+
goto lnext;
144+
141145
case HeapTupleSelfUpdated:
142146

143147
/*
@@ -170,12 +174,15 @@ ExecLockRows(LockRowsState *node)
170174
}
171175

172176
/* updated, so fetch and lock the updated version */
173-
copyTuple = EvalPlanQualFetch(estate, erm->relation, lockmode, erm->noWait,
174-
&hufd.ctid, hufd.xmax);
177+
copyTuple = EvalPlanQualFetch(estate, erm->relation, lockmode,
178+
erm->waitPolicy, &hufd.ctid, hufd.xmax);
175179

176180
if (copyTuple == NULL)
177181
{
178-
/* Tuple was deleted, so don't return it */
182+
/*
183+
* Tuple was deleted; or it's locked and we're under SKIP
184+
* LOCKED policy, so don't return it
185+
*/
179186
goto lnext;
180187
}
181188
/* remember the actually locked tuple's TID */

0 commit comments

Comments
 (0)