On 27 August 2014 17:18, Alvaro Herrera <alvhe...@2ndquadrant.com> wrote:
> Thomas Munro wrote:
>> On 25 August 2014 02:57, Alvaro Herrera <alvhe...@2ndquadrant.com> wrote:
>> > Thomas Munro wrote:
>> >> The difficulty of course will be testing all these racy cases
>> >> reproducibly...
>> >
>> > Does this help?
>> > http://www.postgresql.org/message-id/51fb4305.3070...@2ndquadrant.com
>> > The useful trick there is forcing a query to get its snapshot and then
>> > go to sleep before actually doing anything, by way of an advisory lock.
>>
>> Yes it does, thanks Alvaro and Craig. I think the attached spec
>> reproduces the problem using that trick, ie shows NOWAIT blocking,
>> presumably in EvalPlanQualFetch (though I haven't stepped through it
>> with a debugger yet). I'm afraid I'm out of Postgres hacking cycles
>> for a few days, but next weekend I should have a new patch that fixes
>> this by teaching EvalPlanQualFetch about wait policies, with isolation
>> tests for NOWAIT and SKIP LOCKED.
>
> Hmm, http://www.postgresql.org/message-id/51fb6703.9090...@2ndquadrant.com
Thanks, I hadn't seen this, I should have checked the archives better.
I have actually already updated my patch to handle EvalPlanQualFetch
with NOWAIT and SKIP LOCKED with isolation specs, see attached. I
will compare with Craig's and see if I screwed anything up... of
course I am happy to merge and submit a new patch on top of Craig's if
it's going to be committed.
I haven't yet figured out how to get get into a situation where
heap_lock_updated_tuple_rec waits.
Best regards,
Thomas Munro
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 231dc6a..0469705 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -45,7 +45,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
[ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
[ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ]
- [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ] [...] ]
+ [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
<phrase>where <replaceable class="parameter">from_item</replaceable> can be one of:</phrase>
@@ -1283,7 +1283,7 @@ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] {
The locking clause has the general form
<synopsis>
-FOR <replaceable>lock_strength</> [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ]
+FOR <replaceable>lock_strength</> [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ]
</synopsis>
where <replaceable>lock_strength</> can be one of
@@ -1359,11 +1359,17 @@ KEY SHARE
<para>
To prevent the operation from waiting for other transactions to commit,
- use the <literal>NOWAIT</> option. With <literal>NOWAIT</>, the statement
- reports an error, rather than waiting, if a selected row
- cannot be locked immediately. Note that <literal>NOWAIT</> applies only
- to the row-level lock(s) — the required <literal>ROW SHARE</literal>
- table-level lock is still taken in the ordinary way (see
+ use either the <literal>NOWAIT</> or <literal>SKIP LOCKED</literal>
+ option. With <literal>NOWAIT</>, the statement reports an error, rather
+ than waiting, if a selected row cannot be locked immediately.
+ With <literal>SKIP LOCKED</literal>, any selected rows that cannot be
+ immediately locked are skipped. Skipping locked rows provides an
+ inconsistent view of the data, so this is not suitable for general purpose
+ work, but can be used to avoid lock contention with multiple consumers
+ accessing a queue-like table. Note that <literal>NOWAIT</>
+ and <literal>SKIP LOCKED</literal> apply only to the row-level lock(s)
+ — the required <literal>ROW SHARE</literal> table-level lock is
+ still taken in the ordinary way (see
<xref linkend="mvcc">). You can use
<xref linkend="sql-lock">
with the <literal>NOWAIT</> option first,
@@ -1386,14 +1392,14 @@ KEY SHARE
</para>
<para>
- Multiple locking
- clauses can be written if it is necessary to specify different locking
- behavior for different tables. If the same table is mentioned (or
- implicitly affected) by more than one locking clause,
- then it is processed as if it was only specified by the strongest one.
- Similarly, a table is processed
- as <literal>NOWAIT</> if that is specified in any of the clauses
- affecting it.
+ Multiple locking clauses can be written if it is necessary to specify
+ different locking behavior for different tables. If the same table is
+ mentioned (or implicitly affected) by more than one locking clause, then
+ it is processed as if it was only specified by the strongest one.
+ Similarly, a table is processed as <literal>NOWAIT</> if that is specified
+ in any of the clauses affecting it. Otherwise, it is processed
+ as <literal>SKIP LOCKED</literal> if that is specified in any of the
+ clauses affecting it.
</para>
<para>
@@ -1930,9 +1936,9 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
<productname>PostgreSQL</productname> allows it in any <command>SELECT</>
query as well as in sub-<command>SELECT</>s, but this is an extension.
The <literal>FOR NO KEY UPDATE</>, <literal>FOR SHARE</> and
- <literal>FOR KEY SHARE</> variants,
- as well as the <literal>NOWAIT</> option,
- do not appear in the standard.
+ <literal>FOR KEY SHARE</> variants, as well as the <literal>NOWAIT</>
+ and <literal>SKIP LOCKED</literal> options, do not appear in the
+ standard.
</para>
</refsect2>
diff --git a/doc/src/sgml/sql.sgml b/doc/src/sgml/sql.sgml
index ba92607..57396d7 100644
--- a/doc/src/sgml/sql.sgml
+++ b/doc/src/sgml/sql.sgml
@@ -863,7 +863,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="PARAMETER">expression</replac
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]
[ OFFSET <replaceable class="PARAMETER">start</replaceable> ]
- [ FOR { UPDATE | SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ] [...] ]
+ [ FOR { UPDATE | SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
</synopsis>
</para>
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index 4d7575b..478fb74 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -4090,7 +4090,7 @@ get_mxact_status_for_lock(LockTupleMode mode, bool is_update)
* cid: current command ID (used for visibility test, and stored into
* tuple's cmax if lock is successful)
* mode: indicates if shared or exclusive tuple lock is desired
- * nowait: if true, ereport rather than blocking if lock not available
+ * wait_policy: whether to block, ereport or skip if lock not available
* follow_updates: if true, follow the update chain to also lock descendant
* tuples.
*
@@ -4103,6 +4103,7 @@ get_mxact_status_for_lock(LockTupleMode mode, bool is_update)
* HeapTupleMayBeUpdated: lock was successfully acquired
* HeapTupleSelfUpdated: lock failed because tuple updated by self
* HeapTupleUpdated: lock failed because tuple updated by other xact
+ * HeapTupleWouldBlock: lock couldn't be acquired and wait_policy is skip
*
* In the failure cases, the routine fills *hufd with the tuple's t_ctid,
* 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)
*/
HTSU_Result
heap_lock_tuple(Relation relation, HeapTuple tuple,
- CommandId cid, LockTupleMode mode, bool nowait,
+ CommandId cid, LockTupleMode mode, LockWaitPolicy wait_policy,
bool follow_updates,
Buffer *buffer, HeapUpdateFailureData *hufd)
{
@@ -4220,16 +4221,28 @@ l3:
*/
if (!have_tuple_lock)
{
- if (nowait)
+ switch (wait_policy)
{
- if (!ConditionalLockTupleTuplock(relation, tid, mode))
- ereport(ERROR,
- (errcode(ERRCODE_LOCK_NOT_AVAILABLE),
- errmsg("could not obtain lock on row in relation \"%s\"",
- RelationGetRelationName(relation))));
+ case LockWaitBlock:
+ LockTupleTuplock(relation, tid, mode);
+ break;
+ case LockWaitSkip:
+ if (!ConditionalLockTupleTuplock(relation, tid, mode))
+ {
+ result = HeapTupleWouldBlock;
+ /* recovery code expects to have buffer lock held */
+ LockBuffer(*buffer, BUFFER_LOCK_EXCLUSIVE);
+ goto failed;
+ }
+ break;
+ case LockWaitError:
+ if (!ConditionalLockTupleTuplock(relation, tid, mode))
+ ereport(ERROR,
+ (errcode(ERRCODE_LOCK_NOT_AVAILABLE),
+ errmsg("could not obtain lock on row in relation \"%s\"",
+ RelationGetRelationName(relation))));
+ break;
}
- else
- LockTupleTuplock(relation, tid, mode);
have_tuple_lock = true;
}
@@ -4432,21 +4445,35 @@ l3:
if (status >= MultiXactStatusNoKeyUpdate)
elog(ERROR, "invalid lock mode in heap_lock_tuple");
- /* wait for multixact to end */
- if (nowait)
+ /* wait for multixact to end, or die trying */
+ switch (wait_policy)
{
- if (!ConditionalMultiXactIdWait((MultiXactId) xwait,
- status, infomask, relation,
- NULL))
- ereport(ERROR,
- (errcode(ERRCODE_LOCK_NOT_AVAILABLE),
- errmsg("could not obtain lock on row in relation \"%s\"",
- RelationGetRelationName(relation))));
+ case LockWaitBlock:
+ MultiXactIdWait((MultiXactId) xwait, status, infomask,
+ relation, &tuple->t_data->t_ctid, XLTW_Lock, NULL);
+ break;
+ case LockWaitSkip:
+ if (!ConditionalMultiXactIdWait((MultiXactId) xwait,
+ status, infomask, relation,
+ NULL))
+ {
+ result = HeapTupleWouldBlock;
+ /* recovery code expects to have buffer lock held */
+ LockBuffer(*buffer, BUFFER_LOCK_EXCLUSIVE);
+ goto failed;
+ }
+ break;
+ case LockWaitError:
+ if (!ConditionalMultiXactIdWait((MultiXactId) xwait,
+ status, infomask, relation,
+ NULL))
+ ereport(ERROR,
+ (errcode(ERRCODE_LOCK_NOT_AVAILABLE),
+ errmsg("could not obtain lock on row in relation \"%s\"",
+ RelationGetRelationName(relation))));
+
+ break;
}
- else
- MultiXactIdWait((MultiXactId) xwait, status, infomask,
- relation, &tuple->t_data->t_ctid,
- XLTW_Lock, NULL);
/* if there are updates, follow the update chain */
if (follow_updates &&
@@ -4491,18 +4518,30 @@ l3:
}
else
{
- /* wait for regular transaction to end */
- if (nowait)
+ /* wait for regular transaction to end, or die trying */
+ switch (wait_policy)
{
- if (!ConditionalXactLockTableWait(xwait))
- ereport(ERROR,
- (errcode(ERRCODE_LOCK_NOT_AVAILABLE),
- errmsg("could not obtain lock on row in relation \"%s\"",
- RelationGetRelationName(relation))));
+ case LockWaitBlock:
+ XactLockTableWait(xwait, relation, &tuple->t_data->t_ctid,
+ XLTW_Lock);
+ break;
+ case LockWaitSkip:
+ if (!ConditionalXactLockTableWait(xwait))
+ {
+ result = HeapTupleWouldBlock;
+ /* recovery code expects to have buffer lock held */
+ LockBuffer(*buffer, BUFFER_LOCK_EXCLUSIVE);
+ goto failed;
+ }
+ break;
+ case LockWaitError:
+ if (!ConditionalXactLockTableWait(xwait))
+ ereport(ERROR,
+ (errcode(ERRCODE_LOCK_NOT_AVAILABLE),
+ errmsg("could not obtain lock on row in relation \"%s\"",
+ RelationGetRelationName(relation))));
+ break;
}
- else
- XactLockTableWait(xwait, relation, &tuple->t_data->t_ctid,
- XLTW_Lock);
/* if there are updates, follow the update chain */
if (follow_updates &&
@@ -4564,7 +4603,8 @@ l3:
failed:
if (result != HeapTupleMayBeUpdated)
{
- Assert(result == HeapTupleSelfUpdated || result == HeapTupleUpdated);
+ Assert(result == HeapTupleSelfUpdated || result == HeapTupleUpdated ||
+ result == HeapTupleWouldBlock);
Assert(!(tuple->t_data->t_infomask & HEAP_XMAX_INVALID));
hufd->ctid = tuple->t_data->t_ctid;
hufd->xmax = HeapTupleHeaderGetUpdateXid(tuple->t_data);
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 9bf0098..f4c0ffa 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -2706,7 +2706,7 @@ ltrmark:;
tuple.t_self = *tid;
test = heap_lock_tuple(relation, &tuple,
estate->es_output_cid,
- lockmode, false /* wait */ ,
+ lockmode, LockWaitBlock,
false, &buffer, &hufd);
switch (test)
{
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 072c7df..2101c56 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -830,7 +830,7 @@ InitPlan(QueryDesc *queryDesc, int eflags)
erm->prti = rc->prti;
erm->rowmarkId = rc->rowmarkId;
erm->markType = rc->markType;
- erm->noWait = rc->noWait;
+ erm->waitPolicy = rc->waitPolicy;
ItemPointerSetInvalid(&(erm->curCtid));
estate->es_rowMarks = lappend(estate->es_rowMarks, erm);
}
@@ -1863,7 +1863,7 @@ EvalPlanQual(EState *estate, EPQState *epqstate,
/*
* Get and lock the updated version of the row; if fail, return NULL.
*/
- copyTuple = EvalPlanQualFetch(estate, relation, lockmode,
+ copyTuple = EvalPlanQualFetch(estate, relation, LockWaitBlock, lockmode,
tid, priorXmax);
if (copyTuple == NULL)
@@ -1922,6 +1922,7 @@ EvalPlanQual(EState *estate, EPQState *epqstate,
* estate - executor state data
* relation - table containing tuple
* lockmode - requested tuple lock mode
+ * wait_policy - requested lock wait policy
* *tid - t_ctid from the outdated tuple (ie, next updated version)
* priorXmax - t_xmax from the outdated tuple
*
@@ -1935,6 +1936,7 @@ EvalPlanQual(EState *estate, EPQState *epqstate,
*/
HeapTuple
EvalPlanQualFetch(EState *estate, Relation relation, int lockmode,
+ LockWaitPolicy wait_policy,
ItemPointer tid, TransactionId priorXmax)
{
HeapTuple copyTuple = NULL;
@@ -1978,15 +1980,27 @@ EvalPlanQualFetch(EState *estate, Relation relation, int lockmode,
/*
* If tuple is being updated by other transaction then we have to
- * wait for its commit/abort.
+ * wait for its commit/abort, or die trying.
*/
if (TransactionIdIsValid(SnapshotDirty.xmax))
{
ReleaseBuffer(buffer);
- XactLockTableWait(SnapshotDirty.xmax,
- relation, &tuple.t_data->t_ctid,
- XLTW_FetchUpdated);
- continue; /* loop back to repeat heap_fetch */
+ switch (wait_policy)
+ {
+ case LockWaitBlock:
+ XactLockTableWait(SnapshotDirty.xmax,
+ relation, &tuple.t_data->t_ctid,
+ XLTW_FetchUpdated);
+ continue; /* loop back to repeat heap_fetch */
+ case LockWaitSkip:
+ return NULL;
+ case LockWaitError:
+ ereport(ERROR,
+ (errcode(ERRCODE_LOCK_NOT_AVAILABLE),
+ errmsg("could not obtain lock on row in relation \"%s\"",
+ RelationGetRelationName(relation))));
+ break;
+ }
}
/*
@@ -2012,7 +2026,7 @@ EvalPlanQualFetch(EState *estate, Relation relation, int lockmode,
*/
test = heap_lock_tuple(relation, &tuple,
estate->es_output_cid,
- lockmode, false /* wait */ ,
+ lockmode, wait_policy,
false, &buffer, &hufd);
/* We now have two pins on the buffer, get rid of one */
ReleaseBuffer(buffer);
diff --git a/src/backend/executor/nodeLockRows.c b/src/backend/executor/nodeLockRows.c
index 298d4b4..f9feff4 100644
--- a/src/backend/executor/nodeLockRows.c
+++ b/src/backend/executor/nodeLockRows.c
@@ -133,11 +133,15 @@ lnext:
test = heap_lock_tuple(erm->relation, &tuple,
estate->es_output_cid,
- lockmode, erm->noWait, true,
+ lockmode, erm->waitPolicy, true,
&buffer, &hufd);
ReleaseBuffer(buffer);
switch (test)
{
+ case HeapTupleWouldBlock:
+ /* couldn't lock tuple in SKIP LOCKED mode */
+ goto lnext;
+
case HeapTupleSelfUpdated:
/*
@@ -171,11 +175,11 @@ lnext:
/* updated, so fetch and lock the updated version */
copyTuple = EvalPlanQualFetch(estate, erm->relation, lockmode,
- &hufd.ctid, hufd.xmax);
+ erm->waitPolicy, &hufd.ctid, hufd.xmax);
if (copyTuple == NULL)
{
- /* Tuple was deleted, so don't return it */
+ /* Tuple was deleted or skipped (in SKIP LOCKED), so don't return it */
goto lnext;
}
/* remember the actually locked tuple's TID */
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index aa053a0..b677f27 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -959,7 +959,7 @@ _copyPlanRowMark(const PlanRowMark *from)
COPY_SCALAR_FIELD(prti);
COPY_SCALAR_FIELD(rowmarkId);
COPY_SCALAR_FIELD(markType);
- COPY_SCALAR_FIELD(noWait);
+ COPY_SCALAR_FIELD(waitPolicy);
COPY_SCALAR_FIELD(isParent);
return newnode;
@@ -2071,7 +2071,7 @@ _copyRowMarkClause(const RowMarkClause *from)
COPY_SCALAR_FIELD(rti);
COPY_SCALAR_FIELD(strength);
- COPY_SCALAR_FIELD(noWait);
+ COPY_SCALAR_FIELD(waitPolicy);
COPY_SCALAR_FIELD(pushedDown);
return newnode;
@@ -2452,7 +2452,7 @@ _copyLockingClause(const LockingClause *from)
COPY_NODE_FIELD(lockedRels);
COPY_SCALAR_FIELD(strength);
- COPY_SCALAR_FIELD(noWait);
+ COPY_SCALAR_FIELD(waitPolicy);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 719923e..459ecbe 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2286,7 +2286,7 @@ _equalLockingClause(const LockingClause *a, const LockingClause *b)
{
COMPARE_NODE_FIELD(lockedRels);
COMPARE_SCALAR_FIELD(strength);
- COMPARE_SCALAR_FIELD(noWait);
+ COMPARE_SCALAR_FIELD(waitPolicy);
return true;
}
@@ -2382,7 +2382,7 @@ _equalRowMarkClause(const RowMarkClause *a, const RowMarkClause *b)
{
COMPARE_SCALAR_FIELD(rti);
COMPARE_SCALAR_FIELD(strength);
- COMPARE_SCALAR_FIELD(noWait);
+ COMPARE_SCALAR_FIELD(waitPolicy);
COMPARE_SCALAR_FIELD(pushedDown);
return true;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index e686a6c..e5d8502 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -836,7 +836,7 @@ _outPlanRowMark(StringInfo str, const PlanRowMark *node)
WRITE_UINT_FIELD(prti);
WRITE_UINT_FIELD(rowmarkId);
WRITE_ENUM_FIELD(markType, RowMarkType);
- WRITE_BOOL_FIELD(noWait);
+ WRITE_BOOL_FIELD(waitPolicy);
WRITE_BOOL_FIELD(isParent);
}
@@ -2136,7 +2136,7 @@ _outLockingClause(StringInfo str, const LockingClause *node)
WRITE_NODE_FIELD(lockedRels);
WRITE_ENUM_FIELD(strength, LockClauseStrength);
- WRITE_BOOL_FIELD(noWait);
+ WRITE_ENUM_FIELD(waitPolicy, LockWaitPolicy);
}
static void
@@ -2326,7 +2326,7 @@ _outRowMarkClause(StringInfo str, const RowMarkClause *node)
WRITE_UINT_FIELD(rti);
WRITE_ENUM_FIELD(strength, LockClauseStrength);
- WRITE_BOOL_FIELD(noWait);
+ WRITE_ENUM_FIELD(waitPolicy, LockWaitPolicy);
WRITE_BOOL_FIELD(pushedDown);
}
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 69d9989..e3a12e9 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -320,7 +320,7 @@ _readRowMarkClause(void)
READ_UINT_FIELD(rti);
READ_ENUM_FIELD(strength, LockClauseStrength);
- READ_BOOL_FIELD(noWait);
+ READ_ENUM_FIELD(waitPolicy, LockWaitPolicy);
READ_BOOL_FIELD(pushedDown);
READ_DONE();
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index e1480cd..cd61309 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -2229,7 +2229,7 @@ preprocess_rowmarks(PlannerInfo *root)
newrc->markType = ROW_MARK_KEYSHARE;
break;
}
- newrc->noWait = rc->noWait;
+ newrc->waitPolicy = rc->waitPolicy;
newrc->isParent = false;
prowmarks = lappend(prowmarks, newrc);
@@ -2257,7 +2257,7 @@ preprocess_rowmarks(PlannerInfo *root)
newrc->markType = ROW_MARK_REFERENCE;
else
newrc->markType = ROW_MARK_COPY;
- newrc->noWait = false; /* doesn't matter */
+ newrc->waitPolicy = LockWaitBlock; /* doesn't matter */
newrc->isParent = false;
prowmarks = lappend(prowmarks, newrc);
diff --git a/src/backend/optimizer/prep/prepsecurity.c b/src/backend/optimizer/prep/prepsecurity.c
index 2420f97..cafd830 100644
--- a/src/backend/optimizer/prep/prepsecurity.c
+++ b/src/backend/optimizer/prep/prepsecurity.c
@@ -233,19 +233,19 @@ expand_security_qual(PlannerInfo *root, List *tlist, int rt_index,
{
case ROW_MARK_EXCLUSIVE:
applyLockingClause(subquery, 1, LCS_FORUPDATE,
- rc->noWait, false);
+ rc->waitPolicy, false);
break;
case ROW_MARK_NOKEYEXCLUSIVE:
applyLockingClause(subquery, 1, LCS_FORNOKEYUPDATE,
- rc->noWait, false);
+ rc->waitPolicy, false);
break;
case ROW_MARK_SHARE:
applyLockingClause(subquery, 1, LCS_FORSHARE,
- rc->noWait, false);
+ rc->waitPolicy, false);
break;
case ROW_MARK_KEYSHARE:
applyLockingClause(subquery, 1, LCS_FORKEYSHARE,
- rc->noWait, false);
+ rc->waitPolicy, false);
break;
case ROW_MARK_REFERENCE:
case ROW_MARK_COPY:
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index 0410fdd..69756e4 100644
--- a/src/backend/optimizer/prep/prepunion.c
+++ b/src/backend/optimizer/prep/prepunion.c
@@ -1389,7 +1389,7 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
newrc->prti = rti;
newrc->rowmarkId = oldrc->rowmarkId;
newrc->markType = oldrc->markType;
- newrc->noWait = oldrc->noWait;
+ newrc->waitPolicy = oldrc->waitPolicy;
newrc->isParent = false;
root->rowMarks = lappend(root->rowMarks, newrc);
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index fb6c44c..0f6e9b0 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -2358,7 +2358,7 @@ transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc,
allrels = makeNode(LockingClause);
allrels->lockedRels = NIL; /* indicates all rels */
allrels->strength = lc->strength;
- allrels->noWait = lc->noWait;
+ allrels->waitPolicy = lc->waitPolicy;
if (lockedRels == NIL)
{
@@ -2372,13 +2372,13 @@ transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc,
switch (rte->rtekind)
{
case RTE_RELATION:
- applyLockingClause(qry, i,
- lc->strength, lc->noWait, pushedDown);
+ applyLockingClause(qry, i, lc->strength, lc->waitPolicy,
+ pushedDown);
rte->requiredPerms |= ACL_SELECT_FOR_UPDATE;
break;
case RTE_SUBQUERY:
- applyLockingClause(qry, i,
- lc->strength, lc->noWait, pushedDown);
+ applyLockingClause(qry, i, lc->strength, lc->waitPolicy,
+ pushedDown);
/*
* FOR UPDATE/SHARE of subquery is propagated to all of
@@ -2424,15 +2424,13 @@ transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc,
switch (rte->rtekind)
{
case RTE_RELATION:
- applyLockingClause(qry, i,
- lc->strength, lc->noWait,
- pushedDown);
+ applyLockingClause(qry, i, lc->strength,
+ lc->waitPolicy, pushedDown);
rte->requiredPerms |= ACL_SELECT_FOR_UPDATE;
break;
case RTE_SUBQUERY:
- applyLockingClause(qry, i,
- lc->strength, lc->noWait,
- pushedDown);
+ applyLockingClause(qry, i, lc->strength,
+ lc->waitPolicy, pushedDown);
/* see comment above */
transformLockingClause(pstate, rte->subquery,
allrels, true);
@@ -2499,7 +2497,8 @@ transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc,
*/
void
applyLockingClause(Query *qry, Index rtindex,
- LockClauseStrength strength, bool noWait, bool pushedDown)
+ LockClauseStrength strength, LockWaitPolicy waitPolicy,
+ bool pushedDown)
{
RowMarkClause *rc;
@@ -2516,15 +2515,16 @@ applyLockingClause(Query *qry, Index rtindex,
* a shared and exclusive lock at the same time; it'll end up being
* exclusive anyway.)
*
- * We also consider that NOWAIT wins if it's specified both ways. This
- * is a bit more debatable but raising an error doesn't seem helpful.
- * (Consider for instance SELECT FOR UPDATE NOWAIT from a view that
- * internally contains a plain FOR UPDATE spec.)
+ * We also consider that NOWAIT wins if it is specified multiple ways,
+ * otherwise SKIP LOCKED wins. This is a bit more debatable but
+ * raising an error doesn't seem helpful. (Consider for instance
+ * SELECT FOR UPDATE NOWAIT from a view that internally contains a
+ * plain FOR UPDATE spec.)
*
* And of course pushedDown becomes false if any clause is explicit.
*/
rc->strength = Max(rc->strength, strength);
- rc->noWait |= noWait;
+ rc->waitPolicy = Max(rc->waitPolicy, waitPolicy);
rc->pushedDown &= pushedDown;
return;
}
@@ -2533,7 +2533,7 @@ applyLockingClause(Query *qry, Index rtindex,
rc = makeNode(RowMarkClause);
rc->rti = rtindex;
rc->strength = strength;
- rc->noWait = noWait;
+ rc->waitPolicy = waitPolicy;
rc->pushedDown = pushedDown;
qry->rowMarks = lappend(qry->rowMarks, rc);
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 6f4d645..d63b571 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -284,6 +284,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <boolean> opt_force opt_or_replace
opt_grant_grant_option opt_grant_admin_option
opt_nowait opt_if_exists opt_with_data
+%type <ival> opt_nowait_or_skip
%type <list> OptRoleList AlterOptRoleList
%type <defelt> CreateOptRoleElem AlterOptRoleElem
@@ -577,7 +578,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
LABEL LANGUAGE LARGE_P LAST_P LATERAL_P
LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL
- LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOGGED
+ LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOCKED LOGGED
MAPPING MATCH MATERIALIZED MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
@@ -601,7 +602,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
- SHOW SIMILAR SIMPLE SMALLINT SNAPSHOT SOME STABLE STANDALONE_P START
+ SHOW SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME STABLE STANDALONE_P START
STATEMENT STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING
SYMMETRIC SYSID SYSTEM_P
@@ -9211,6 +9212,12 @@ opt_nowait: NOWAIT { $$ = TRUE; }
| /*EMPTY*/ { $$ = FALSE; }
;
+opt_nowait_or_skip:
+ NOWAIT { $$ = LockWaitError; }
+ | SKIP LOCKED { $$ = LockWaitSkip; }
+ | /*EMPTY*/ { $$ = LockWaitBlock; }
+ ;
+
/*****************************************************************************
*
@@ -9852,12 +9859,12 @@ for_locking_items:
;
for_locking_item:
- for_locking_strength locked_rels_list opt_nowait
+ for_locking_strength locked_rels_list opt_nowait_or_skip
{
LockingClause *n = makeNode(LockingClause);
n->lockedRels = $2;
n->strength = $1;
- n->noWait = $3;
+ n->waitPolicy = $3;
$$ = (Node *) n;
}
;
@@ -12986,6 +12993,7 @@ unreserved_keyword:
| LOCAL
| LOCATION
| LOCK_P
+ | LOCKED
| LOGGED
| MAPPING
| MATCH
@@ -13069,6 +13077,7 @@ unreserved_keyword:
| SHARE
| SHOW
| SIMPLE
+ | SKIP
| SNAPSHOT
| STABLE
| STANDALONE_P
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index e6c5530..6d4edcd 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -62,7 +62,8 @@ static void rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation,
static void rewriteTargetListUD(Query *parsetree, RangeTblEntry *target_rte,
Relation target_relation);
static void markQueryForLocking(Query *qry, Node *jtnode,
- LockClauseStrength strength, bool noWait, bool pushedDown);
+ LockClauseStrength strength, LockWaitPolicy waitPolicy,
+ bool pushedDown);
static List *matchLocks(CmdType event, RuleLock *rulelocks,
int varno, Query *parsetree);
static Query *fireRIRrules(Query *parsetree, List *activeRIRs,
@@ -1481,7 +1482,7 @@ ApplyRetrieveRule(Query *parsetree,
*/
if (rc != NULL)
markQueryForLocking(rule_action, (Node *) rule_action->jointree,
- rc->strength, rc->noWait, true);
+ rc->strength, rc->waitPolicy, true);
return parsetree;
}
@@ -1499,7 +1500,8 @@ ApplyRetrieveRule(Query *parsetree,
*/
static void
markQueryForLocking(Query *qry, Node *jtnode,
- LockClauseStrength strength, bool noWait, bool pushedDown)
+ LockClauseStrength strength, LockWaitPolicy waitPolicy,
+ bool pushedDown)
{
if (jtnode == NULL)
return;
@@ -1510,15 +1512,15 @@ markQueryForLocking(Query *qry, Node *jtnode,
if (rte->rtekind == RTE_RELATION)
{
- applyLockingClause(qry, rti, strength, noWait, pushedDown);
+ applyLockingClause(qry, rti, strength, waitPolicy, pushedDown);
rte->requiredPerms |= ACL_SELECT_FOR_UPDATE;
}
else if (rte->rtekind == RTE_SUBQUERY)
{
- applyLockingClause(qry, rti, strength, noWait, pushedDown);
+ applyLockingClause(qry, rti, strength, waitPolicy, pushedDown);
/* FOR UPDATE/SHARE of subquery is propagated to subquery's rels */
markQueryForLocking(rte->subquery, (Node *) rte->subquery->jointree,
- strength, noWait, true);
+ strength, waitPolicy, true);
}
/* other RTE types are unaffected by FOR UPDATE */
}
@@ -1528,14 +1530,14 @@ markQueryForLocking(Query *qry, Node *jtnode,
ListCell *l;
foreach(l, f->fromlist)
- markQueryForLocking(qry, lfirst(l), strength, noWait, pushedDown);
+ markQueryForLocking(qry, lfirst(l), strength, waitPolicy, pushedDown);
}
else if (IsA(jtnode, JoinExpr))
{
JoinExpr *j = (JoinExpr *) jtnode;
- markQueryForLocking(qry, j->larg, strength, noWait, pushedDown);
- markQueryForLocking(qry, j->rarg, strength, noWait, pushedDown);
+ markQueryForLocking(qry, j->larg, strength, waitPolicy, pushedDown);
+ markQueryForLocking(qry, j->rarg, strength, waitPolicy, pushedDown);
}
else
elog(ERROR, "unrecognized node type: %d",
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 7237e5d..6e41cbd 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -4446,8 +4446,10 @@ get_select_query_def(Query *query, deparse_context *context,
appendStringInfo(buf, " OF %s",
quote_identifier(get_rtable_name(rc->rti,
context)));
- if (rc->noWait)
+ if (rc->waitPolicy == LockWaitError)
appendStringInfoString(buf, " NOWAIT");
+ else if (rc->waitPolicy == LockWaitSkip)
+ appendStringInfoString(buf, " SKIP LOCKED");
}
}
diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h
index 493839f..7f7166d 100644
--- a/src/include/access/heapam.h
+++ b/src/include/access/heapam.h
@@ -19,6 +19,7 @@
#include "nodes/primnodes.h"
#include "storage/bufpage.h"
#include "storage/lock.h"
+#include "utils/lockwaitpolicy.h"
#include "utils/relcache.h"
#include "utils/snapshot.h"
@@ -144,7 +145,7 @@ extern HTSU_Result heap_update(Relation relation, ItemPointer otid,
CommandId cid, Snapshot crosscheck, bool wait,
HeapUpdateFailureData *hufd, LockTupleMode *lockmode);
extern HTSU_Result heap_lock_tuple(Relation relation, HeapTuple tuple,
- CommandId cid, LockTupleMode mode, bool nowait,
+ CommandId cid, LockTupleMode mode, LockWaitPolicy wait_policy,
bool follow_update,
Buffer *buffer, HeapUpdateFailureData *hufd);
extern void heap_inplace_update(Relation relation, HeapTuple tuple);
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index 239aff3..52118f0 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -199,7 +199,8 @@ extern TupleTableSlot *EvalPlanQual(EState *estate, EPQState *epqstate,
Relation relation, Index rti, int lockmode,
ItemPointer tid, TransactionId priorXmax);
extern HeapTuple EvalPlanQualFetch(EState *estate, Relation relation,
- int lockmode, ItemPointer tid, TransactionId priorXmax);
+ int lockmode, LockWaitPolicy, ItemPointer tid,
+ TransactionId priorXmax);
extern void EvalPlanQualInit(EPQState *epqstate, EState *estate,
Plan *subplan, List *auxrowmarks, int epqParam);
extern void EvalPlanQualSetPlan(EPQState *epqstate,
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index b271f21..39d2c10 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -429,7 +429,7 @@ typedef struct ExecRowMark
Index prti; /* parent range table index, if child */
Index rowmarkId; /* unique identifier for resjunk columns */
RowMarkType markType; /* see enum in nodes/plannodes.h */
- bool noWait; /* NOWAIT option */
+ LockWaitPolicy waitPolicy; /* NOWAIT and SKIP LOCKED */
ItemPointerData curCtid; /* ctid of currently locked tuple, if any */
} ExecRowMark;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index d2c0b29..0310539 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -23,6 +23,7 @@
#include "nodes/bitmapset.h"
#include "nodes/primnodes.h"
#include "nodes/value.h"
+#include "utils/lockwaitpolicy.h"
/* Possible sources of a Query */
typedef enum QuerySource
@@ -630,7 +631,7 @@ typedef struct LockingClause
NodeTag type;
List *lockedRels; /* FOR [KEY] UPDATE/SHARE relations */
LockClauseStrength strength;
- bool noWait; /* NOWAIT option */
+ LockWaitPolicy waitPolicy; /* NOWAIT and SKIP LOCKED */
} LockingClause;
/*
@@ -975,7 +976,7 @@ typedef struct RowMarkClause
NodeTag type;
Index rti; /* range table index of target relation */
LockClauseStrength strength;
- bool noWait; /* NOWAIT option */
+ LockWaitPolicy waitPolicy; /* NOWAIT and SKIP LOCKED */
bool pushedDown; /* pushed down from higher query level? */
} RowMarkClause;
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 3b9c683..c174fb6 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -17,6 +17,7 @@
#include "access/sdir.h"
#include "nodes/bitmapset.h"
#include "nodes/primnodes.h"
+#include "utils/lockwaitpolicy.h"
/* ----------------------------------------------------------------
@@ -831,7 +832,7 @@ typedef struct PlanRowMark
Index prti; /* range table index of parent relation */
Index rowmarkId; /* unique identifier for resjunk columns */
RowMarkType markType; /* see enum above */
- bool noWait; /* NOWAIT option */
+ LockWaitPolicy waitPolicy; /* NOWAIT and SKIP LOCKED options */
bool isParent; /* true if this is a "dummy" parent entry */
} PlanRowMark;
diff --git a/src/include/parser/analyze.h b/src/include/parser/analyze.h
index 370a445..f5da6bf 100644
--- a/src/include/parser/analyze.h
+++ b/src/include/parser/analyze.h
@@ -39,6 +39,7 @@ extern bool analyze_requires_snapshot(Node *parseTree);
extern char *LCS_asString(LockClauseStrength strength);
extern void CheckSelectLocking(Query *qry, LockClauseStrength strength);
extern void applyLockingClause(Query *qry, Index rtindex,
- LockClauseStrength strength, bool noWait, bool pushedDown);
+ LockClauseStrength strength,
+ LockWaitPolicy waitPolicy, bool pushedDown);
#endif /* ANALYZE_H */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 17888ad..1470c55 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -230,6 +230,7 @@ PG_KEYWORD("localtime", LOCALTIME, RESERVED_KEYWORD)
PG_KEYWORD("localtimestamp", LOCALTIMESTAMP, RESERVED_KEYWORD)
PG_KEYWORD("location", LOCATION, UNRESERVED_KEYWORD)
PG_KEYWORD("lock", LOCK_P, UNRESERVED_KEYWORD)
+PG_KEYWORD("locked", LOCKED, UNRESERVED_KEYWORD)
PG_KEYWORD("logged", LOGGED, UNRESERVED_KEYWORD)
PG_KEYWORD("mapping", MAPPING, UNRESERVED_KEYWORD)
PG_KEYWORD("match", MATCH, UNRESERVED_KEYWORD)
@@ -344,6 +345,7 @@ PG_KEYWORD("share", SHARE, UNRESERVED_KEYWORD)
PG_KEYWORD("show", SHOW, UNRESERVED_KEYWORD)
PG_KEYWORD("similar", SIMILAR, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("simple", SIMPLE, UNRESERVED_KEYWORD)
+PG_KEYWORD("skip", SKIP, UNRESERVED_KEYWORD)
PG_KEYWORD("smallint", SMALLINT, COL_NAME_KEYWORD)
PG_KEYWORD("snapshot", SNAPSHOT, UNRESERVED_KEYWORD)
PG_KEYWORD("some", SOME, RESERVED_KEYWORD)
diff --git a/src/include/utils/lockwaitpolicy.h b/src/include/utils/lockwaitpolicy.h
new file mode 100644
index 0000000..f8ad07e
--- /dev/null
+++ b/src/include/utils/lockwaitpolicy.h
@@ -0,0 +1,34 @@
+/*-------------------------------------------------------------------------
+ *
+ * lockwaitpolicy.h
+ * Header file for the enum LockWaitPolicy enum, which is needed in
+ * several modules (parser, planner, executor, heap manager).
+ *
+ * Copyright (c) 2014, PostgreSQL Global Development Group
+ *
+ * src/include/utils/lockwaitpolicy.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef LOCKWAITPOLICY_H
+#define LOCKWAITPOLICY_H
+
+/*
+ * Policy for what to do when a row lock cannot be obtained immediately.
+ *
+ * The enum values defined here control how the parser treats multiple FOR
+ * UPDATE/SHARE clauses that affect the same table. If multiple locking
+ * clauses are defined then the one with the highest numerical value takes
+ * precedence -- see applyLockingClause.
+ */
+typedef enum
+{
+ /* Wait for the lock to become available */
+ LockWaitBlock = 1,
+ /* SELECT FOR UPDATE SKIP LOCKED, skipping rows that can't be locked */
+ LockWaitSkip = 2,
+ /* SELECT FOR UPDATE NOWAIT, abandoning the transaction */
+ LockWaitError = 3
+} LockWaitPolicy;
+
+#endif /* LOCKWAITPOLICY_H */
diff --git a/src/include/utils/snapshot.h b/src/include/utils/snapshot.h
index d8e8b35..53e474f 100644
--- a/src/include/utils/snapshot.h
+++ b/src/include/utils/snapshot.h
@@ -104,7 +104,8 @@ typedef enum
HeapTupleInvisible,
HeapTupleSelfUpdated,
HeapTupleUpdated,
- HeapTupleBeingUpdated
+ HeapTupleBeingUpdated,
+ HeapTupleWouldBlock /* can be returned by heap_tuple_lock */
} HTSU_Result;
#endif /* SNAPSHOT_H */
diff --git a/src/test/isolation/expected/nowait-4.out b/src/test/isolation/expected/nowait-4.out
new file mode 100644
index 0000000..26f59be
--- /dev/null
+++ b/src/test/isolation/expected/nowait-4.out
@@ -0,0 +1,19 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s2a s1a s2b s2c s2d s2e s1b s2f
+step s2a: SELECT pg_advisory_lock(0);
+pg_advisory_lock
+
+
+step s1a: SELECT * FROM foo WHERE pg_advisory_lock(0) IS NOT NULL FOR UPDATE NOWAIT; <waiting ...>
+step s2b: UPDATE foo SET data = data;
+step s2c: BEGIN;
+step s2d: UPDATE foo SET data = data;
+step s2e: SELECT pg_advisory_unlock(0);
+pg_advisory_unlock
+
+t
+step s1a: <... completed>
+error in steps s2e s1a: ERROR: could not obtain lock on row in relation "foo"
+step s1b: COMMIT;
+step s2f: COMMIT;
diff --git a/src/test/isolation/expected/skip-locked-2.out b/src/test/isolation/expected/skip-locked-2.out
new file mode 100644
index 0000000..9240543
--- /dev/null
+++ b/src/test/isolation/expected/skip-locked-2.out
@@ -0,0 +1,49 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1a s2a s2b s1b s2c
+step s1a: SELECT * FROM queue ORDER BY id FOR SHARE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2a: SELECT * FROM queue ORDER BY id FOR SHARE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1b: COMMIT;
+step s2c: COMMIT;
+
+starting permutation: s2a s1a s2b s1b s2c
+step s2a: SELECT * FROM queue ORDER BY id FOR SHARE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1a: SELECT * FROM queue ORDER BY id FOR SHARE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1b: COMMIT;
+step s2c: COMMIT;
+
+starting permutation: s2a s2b s1a s1b s2c
+step s2a: SELECT * FROM queue ORDER BY id FOR SHARE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1a: SELECT * FROM queue ORDER BY id FOR SHARE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1b: COMMIT;
+step s2c: COMMIT;
diff --git a/src/test/isolation/expected/skip-locked-3.out b/src/test/isolation/expected/skip-locked-3.out
new file mode 100644
index 0000000..fa8fe87
--- /dev/null
+++ b/src/test/isolation/expected/skip-locked-3.out
@@ -0,0 +1,19 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a s2a s3a s1b s2b s3b
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE LIMIT 1;
+id data status
+
+1 foo NEW
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE LIMIT 1; <waiting ...>
+step s3a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1b: COMMIT;
+step s2a: <... completed>
+id data status
+
+1 foo NEW
+step s2b: COMMIT;
+step s3b: COMMIT;
diff --git a/src/test/isolation/expected/skip-locked-4.out b/src/test/isolation/expected/skip-locked-4.out
new file mode 100644
index 0000000..2c9cfe8
--- /dev/null
+++ b/src/test/isolation/expected/skip-locked-4.out
@@ -0,0 +1,21 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s2a s1a s2b s2c s2d s2e s1b s2f
+step s2a: SELECT pg_advisory_lock(0);
+pg_advisory_lock
+
+
+step s1a: SELECT * FROM foo WHERE pg_advisory_lock(0) IS NOT NULL ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; <waiting ...>
+step s2b: UPDATE foo SET data = data WHERE id = 1;
+step s2c: BEGIN;
+step s2d: UPDATE foo SET data = data WHERE id = 1;
+step s2e: SELECT pg_advisory_unlock(0);
+pg_advisory_unlock
+
+t
+step s1a: <... completed>
+id data
+
+2 x
+step s1b: COMMIT;
+step s2f: COMMIT;
diff --git a/src/test/isolation/expected/skip-locked.out b/src/test/isolation/expected/skip-locked.out
new file mode 100644
index 0000000..f9b9cf2
--- /dev/null
+++ b/src/test/isolation/expected/skip-locked.out
@@ -0,0 +1,401 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1a s1b s1c s2a s2b s2c
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1c: COMMIT;
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2c: COMMIT;
+
+starting permutation: s1a s1b s2a s1c s2b s2c
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1c: COMMIT;
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2c: COMMIT;
+
+starting permutation: s1a s1b s2a s2b s1c s2c
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1c: COMMIT;
+step s2c: COMMIT;
+
+starting permutation: s1a s1b s2a s2b s2c s1c
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s2c: COMMIT;
+step s1c: COMMIT;
+
+starting permutation: s1a s2a s1b s1c s2b s2c
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1c: COMMIT;
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2c: COMMIT;
+
+starting permutation: s1a s2a s1b s2b s1c s2c
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1c: COMMIT;
+step s2c: COMMIT;
+
+starting permutation: s1a s2a s1b s2b s2c s1c
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s2c: COMMIT;
+step s1c: COMMIT;
+
+starting permutation: s1a s2a s2b s1b s1c s2c
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1c: COMMIT;
+step s2c: COMMIT;
+
+starting permutation: s1a s2a s2b s1b s2c s1c
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2c: COMMIT;
+step s1c: COMMIT;
+
+starting permutation: s1a s2a s2b s2c s1b s1c
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s2c: COMMIT;
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1c: COMMIT;
+
+starting permutation: s2a s1a s1b s1c s2b s2c
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1c: COMMIT;
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2c: COMMIT;
+
+starting permutation: s2a s1a s1b s2b s1c s2c
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1c: COMMIT;
+step s2c: COMMIT;
+
+starting permutation: s2a s1a s1b s2b s2c s1c
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2c: COMMIT;
+step s1c: COMMIT;
+
+starting permutation: s2a s1a s2b s1b s1c s2c
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1c: COMMIT;
+step s2c: COMMIT;
+
+starting permutation: s2a s1a s2b s1b s2c s1c
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s2c: COMMIT;
+step s1c: COMMIT;
+
+starting permutation: s2a s1a s2b s2c s1b s1c
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2c: COMMIT;
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1c: COMMIT;
+
+starting permutation: s2a s2b s1a s1b s1c s2c
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1c: COMMIT;
+step s2c: COMMIT;
+
+starting permutation: s2a s2b s1a s1b s2c s1c
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s2c: COMMIT;
+step s1c: COMMIT;
+
+starting permutation: s2a s2b s1a s2c s1b s1c
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+2 bar NEW
+step s2c: COMMIT;
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1c: COMMIT;
+
+starting permutation: s2a s2b s2c s1a s1b s1c
+step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s2c: COMMIT;
+step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
+id data status
+
+1 foo NEW
+step s1c: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 10c89ff..e39d39c 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -28,3 +28,8 @@ test: timeouts
test: nowait
test: nowait-2
test: nowait-3
+test: nowait-4
+test: skip-locked
+test: skip-locked-2
+test: skip-locked-3
+test: skip-locked-4
diff --git a/src/test/isolation/specs/nowait-4.spec b/src/test/isolation/specs/nowait-4.spec
new file mode 100644
index 0000000..06faece
--- /dev/null
+++ b/src/test/isolation/specs/nowait-4.spec
@@ -0,0 +1,35 @@
+# Test NOWAIT with an updated tuple chain.
+
+setup
+{
+ CREATE TABLE foo (
+ id int PRIMARY KEY,
+ data text NOT NULL
+ );
+ INSERT INTO foo VALUES (1, 'x');
+}
+
+teardown
+{
+ DROP TABLE foo;
+}
+
+session "s1"
+setup { BEGIN; }
+step "s1a" { SELECT * FROM foo WHERE pg_advisory_lock(0) IS NOT NULL FOR UPDATE NOWAIT; }
+step "s1b" { COMMIT; }
+
+session "s2"
+step "s2a" { SELECT pg_advisory_lock(0); }
+step "s2b" { UPDATE foo SET data = data; }
+step "s2c" { BEGIN; }
+step "s2d" { UPDATE foo SET data = data; }
+step "s2e" { SELECT pg_advisory_unlock(0); }
+step "s2f" { COMMIT; }
+
+# s1 takes a snapshot but then waits on an advisory lock, then s2
+# updates the row in one transaction, then again in another without
+# committing, before allowing s1 to proceed to try to lock a row;
+# because it has a snapshot that sees the older version we, reach the
+# waiting code in EvalPlanQualFetch which ereports when in NOWAIT mode
+permutation "s2a" "s1a" "s2b" "s2c" "s2d" "s2e" "s1b" "s2f"
\ No newline at end of file
diff --git a/src/test/isolation/specs/skip-locked-2.spec b/src/test/isolation/specs/skip-locked-2.spec
new file mode 100644
index 0000000..a179d34
--- /dev/null
+++ b/src/test/isolation/specs/skip-locked-2.spec
@@ -0,0 +1,41 @@
+# Test SKIP LOCKED with multixact locks.
+
+setup
+{
+ CREATE TABLE queue (
+ id int PRIMARY KEY,
+ data text NOT NULL,
+ status text NOT NULL
+ );
+ INSERT INTO queue VALUES (1, 'foo', 'NEW'), (2, 'bar', 'NEW');
+}
+
+teardown
+{
+ DROP TABLE queue;
+}
+
+session "s1"
+setup { BEGIN; }
+step "s1a" { SELECT * FROM queue ORDER BY id FOR SHARE SKIP LOCKED LIMIT 1; }
+step "s1b" { COMMIT; }
+
+session "s2"
+setup { BEGIN; }
+step "s2a" { SELECT * FROM queue ORDER BY id FOR SHARE SKIP LOCKED LIMIT 1; }
+step "s2b" { SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; }
+step "s2c" { COMMIT; }
+
+# s1 and s2 both get SHARE lock, creating a multixact lock, then s2
+# tries to update to UPDATE but skips the record because it can't
+# acquire a multixact lock
+permutation "s1a" "s2a" "s2b" "s1b" "s2c"
+
+# the same but with the SHARE locks acquired in a different order, so
+# s2 again skips because it can't acquired a multixact lock
+permutation "s2a" "s1a" "s2b" "s1b" "s2c"
+
+# s2 acquires SHARE then UPDATE, then s1 tries to acquire SHARE but
+# can't so skips the first record because it can't acquire a regular
+# lock
+permutation "s2a" "s2b" "s1a" "s1b" "s2c"
diff --git a/src/test/isolation/specs/skip-locked-3.spec b/src/test/isolation/specs/skip-locked-3.spec
new file mode 100644
index 0000000..30bf4c6
--- /dev/null
+++ b/src/test/isolation/specs/skip-locked-3.spec
@@ -0,0 +1,36 @@
+# Test SKIP LOCKED with tuple locks.
+
+setup
+{
+ CREATE TABLE queue (
+ id int PRIMARY KEY,
+ data text NOT NULL,
+ status text NOT NULL
+ );
+ INSERT INTO queue VALUES (1, 'foo', 'NEW'), (2, 'bar', 'NEW');
+}
+
+teardown
+{
+ DROP TABLE queue;
+}
+
+session "s1"
+setup { BEGIN; }
+step "s1a" { SELECT * FROM queue ORDER BY id FOR UPDATE LIMIT 1; }
+step "s1b" { COMMIT; }
+
+session "s2"
+setup { BEGIN; }
+step "s2a" { SELECT * FROM queue ORDER BY id FOR UPDATE LIMIT 1; }
+step "s2b" { COMMIT; }
+
+session "s3"
+setup { BEGIN; }
+step "s3a" { SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; }
+step "s3b" { COMMIT; }
+
+# s3 skips to the second record because it can't obtain the tuple lock
+# (s2 holds the tuple lock because it is next in line to obtain the
+# row lock, and s1 holds the row lock)
+permutation "s1a" "s2a" "s3a" "s1b" "s2b" "s3b"
diff --git a/src/test/isolation/specs/skip-locked-4.spec b/src/test/isolation/specs/skip-locked-4.spec
new file mode 100644
index 0000000..458e283
--- /dev/null
+++ b/src/test/isolation/specs/skip-locked-4.spec
@@ -0,0 +1,36 @@
+# Test SKIP LOCKED with an updated tuple chain.
+
+setup
+{
+ CREATE TABLE foo (
+ id int PRIMARY KEY,
+ data text NOT NULL
+ );
+ INSERT INTO foo VALUES (1, 'x'), (2, 'x');
+}
+
+teardown
+{
+ DROP TABLE foo;
+}
+
+session "s1"
+setup { BEGIN; }
+step "s1a" { SELECT * FROM foo WHERE pg_advisory_lock(0) IS NOT NULL ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; }
+step "s1b" { COMMIT; }
+
+session "s2"
+step "s2a" { SELECT pg_advisory_lock(0); }
+step "s2b" { UPDATE foo SET data = data WHERE id = 1; }
+step "s2c" { BEGIN; }
+step "s2d" { UPDATE foo SET data = data WHERE id = 1; }
+step "s2e" { SELECT pg_advisory_unlock(0); }
+step "s2f" { COMMIT; }
+
+# s1 takes a snapshot but then waits on an advisory lock, then s2
+# updates the row in one transaction, then again in another without
+# committing, before allowing s1 to proceed to try to lock a row;
+# because it has a snapshot that sees the older version, we reach the
+# waiting code in EvalPlanQualFetch which skips rows when in SKIP
+# LOCKED mode, so s1 sees the second row
+permutation "s2a" "s1a" "s2b" "s2c" "s2d" "s2e" "s1b" "s2f"
\ No newline at end of file
diff --git a/src/test/isolation/specs/skip-locked.spec b/src/test/isolation/specs/skip-locked.spec
new file mode 100644
index 0000000..3565963
--- /dev/null
+++ b/src/test/isolation/specs/skip-locked.spec
@@ -0,0 +1,28 @@
+# Test SKIP LOCKED when regular row locks can't be acquired.
+
+setup
+{
+ CREATE TABLE queue (
+ id int PRIMARY KEY,
+ data text NOT NULL,
+ status text NOT NULL
+ );
+ INSERT INTO queue VALUES (1, 'foo', 'NEW'), (2, 'bar', 'NEW');
+}
+
+teardown
+{
+ DROP TABLE queue;
+}
+
+session "s1"
+setup { BEGIN; }
+step "s1a" { SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; }
+step "s1b" { SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; }
+step "s1c" { COMMIT; }
+
+session "s2"
+setup { BEGIN; }
+step "s2a" { SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; }
+step "s2b" { SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; }
+step "s2c" { COMMIT; }
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
