On 26 July 2014 15:43, Tom Lane <t...@sss.pgh.pa.us> wrote: > Thomas Munro <mu...@ip9.org> writes: >> I couldn't find an existing reasonable place to share a single wait >> policy enumeration between parser/planner/executor and the heap access >> module, and I get the feeling that it would be unacceptable to >> introduce one. > > There is a precedent in the form of AclMode, which is needed throughout > the system and is currently declared in parsenodes.h. I can't say I've > ever been particularly pleased with that arrangement though, since it > forces inclusion of parsenodes.h in many places that might not otherwise > have any interest in parse nodes. > > It might be better if we'd declared AclMode in a single-purpose header, > say utils/aclmode.h, and then #include'd that into parsenodes.h. > There's certainly plenty of other single-datatype headers laying about.
Here is a new version of the patch with a single enum LockWaitPolicy defined in utils/lockwaitpolicy.h. 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 35f9404..4e8729c 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -4091,7 +4091,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. * @@ -4104,6 +4104,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 @@ -4115,7 +4116,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) { @@ -4218,7 +4219,11 @@ l3: */ if (!have_tuple_lock) { - if (nowait) + if (wait_policy == LockWaitBlock) + { + LockTupleTuplock(relation, tid, mode); + } + else if (wait_policy == LockWaitError) { if (!ConditionalLockTupleTuplock(relation, tid, mode)) ereport(ERROR, @@ -4226,8 +4231,11 @@ l3: errmsg("could not obtain lock on row in relation \"%s\"", RelationGetRelationName(relation)))); } - else - LockTupleTuplock(relation, tid, mode); + else /* wait_policy == LockWaitSkip */ + { + if (!ConditionalLockTupleTuplock(relation, tid, mode)) + return HeapTupleWouldBlock; + } have_tuple_lock = true; } @@ -4429,7 +4437,13 @@ l3: elog(ERROR, "invalid lock mode in heap_lock_tuple"); /* wait for multixact to end */ - if (nowait) + if (wait_policy == LockWaitBlock) + { + MultiXactIdWait((MultiXactId) xwait, status, infomask, + relation, &tuple->t_data->t_ctid, + XLTW_Lock, NULL); + } + else if (wait_policy == LockWaitError) { if (!ConditionalMultiXactIdWait((MultiXactId) xwait, status, infomask, relation, @@ -4440,10 +4454,18 @@ l3: errmsg("could not obtain lock on row in relation \"%s\"", RelationGetRelationName(relation)))); } - else - MultiXactIdWait((MultiXactId) xwait, status, infomask, - relation, &tuple->t_data->t_ctid, - XLTW_Lock, NULL); + else /* wait_policy == LockWaitSkip */ + { + if (!ConditionalMultiXactIdWait((MultiXactId) xwait, + status, infomask, relation, + &tuple->t_data->t_ctid, + XLTW_Lock, NULL)) + { + if (have_tuple_lock) + UnlockTupleTuplock(relation, tid, mode); + return HeapTupleWouldBlock; + } + } /* if there are updates, follow the update chain */ if (follow_updates && @@ -4489,7 +4511,12 @@ l3: else { /* wait for regular transaction to end */ - if (nowait) + if (wait_policy == LockWaitBlock) + { + XactLockTableWait(xwait, relation, &tuple->t_data->t_ctid, + XLTW_Lock); + } + else if (wait_policy == LockWaitError) { if (!ConditionalXactLockTableWait(xwait)) ereport(ERROR, @@ -4497,9 +4524,15 @@ l3: errmsg("could not obtain lock on row in relation \"%s\"", RelationGetRelationName(relation)))); } - else - XactLockTableWait(xwait, relation, &tuple->t_data->t_ctid, - XLTW_Lock); + else /* wait_policy == LockWaitSkip */ + { + if (!ConditionalXactLockTableWait(xwait)) + { + if (have_tuple_lock) + UnlockTupleTuplock(relation, tid, mode); + return HeapTupleWouldBlock; + } + } /* if there are updates, follow the update chain */ if (follow_updates && 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..0f8e357 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); } @@ -2012,7 +2012,7 @@ EvalPlanQualFetch(EState *estate, Relation relation, int lockmode, */ test = heap_lock_tuple(relation, &tuple, estate->es_output_cid, - lockmode, false /* wait */ , + lockmode, LockWaitBlock, 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..b1ab1ce 100644 --- a/src/backend/executor/nodeLockRows.c +++ b/src/backend/executor/nodeLockRows.c @@ -73,6 +73,7 @@ lnext: Buffer buffer; HeapUpdateFailureData hufd; LockTupleMode lockmode; + LockWaitPolicy wait_policy; HTSU_Result test; HeapTuple copyTuple; @@ -131,13 +132,19 @@ lnext: break; } + wait_policy = erm->waitPolicy; + test = heap_lock_tuple(erm->relation, &tuple, estate->es_output_cid, - lockmode, erm->noWait, true, + lockmode, wait_policy, true, &buffer, &hufd); ReleaseBuffer(buffer); switch (test) { + case HeapTupleWouldBlock: + /* couldn't lock tuple in SKIP LOCKED mode */ + goto lnext; + case HeapTupleSelfUpdated: /* diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 3088578..e37d3bb 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 1b07db6..5e915ba 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 dd7f900..2c5c85d 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..f312bca 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) { @@ -2373,12 +2373,12 @@ transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc, { case RTE_RELATION: applyLockingClause(qry, i, - lc->strength, lc->noWait, pushedDown); + lc->strength, lc->waitPolicy, pushedDown); rte->requiredPerms |= ACL_SELECT_FOR_UPDATE; break; case RTE_SUBQUERY: applyLockingClause(qry, i, - lc->strength, lc->noWait, pushedDown); + lc->strength, lc->waitPolicy, pushedDown); /* * FOR UPDATE/SHARE of subquery is propagated to all of @@ -2425,13 +2425,13 @@ transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc, { case RTE_RELATION: applyLockingClause(qry, i, - lc->strength, lc->noWait, + lc->strength, lc->waitPolicy, pushedDown); rte->requiredPerms |= ACL_SELECT_FOR_UPDATE; break; case RTE_SUBQUERY: applyLockingClause(qry, i, - lc->strength, lc->noWait, + lc->strength, lc->waitPolicy, pushedDown); /* see comment above */ transformLockingClause(pstate, rte->subquery, @@ -2499,7 +2499,7 @@ 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 +2516,18 @@ 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; + StaticAssertExpr(LockWaitError > LockWaitSkip, "LockWaitPolicy order"); + StaticAssertExpr(LockWaitSkip > LockWaitBlock, "LockWaitPolicy order"); + rc->waitPolicy = Max(rc->waitPolicy, waitPolicy); rc->pushedDown &= pushedDown; return; } @@ -2533,7 +2536,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 a113809..b90ead8 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 + LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOCKED 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 @@ -9217,6 +9218,12 @@ opt_nowait: NOWAIT { $$ = TRUE; } | /*EMPTY*/ { $$ = FALSE; } ; +opt_nowait_or_skip: + NOWAIT { $$ = LockWaitError; } + | SKIP LOCKED { $$ = LockWaitSkip; } + | /*EMPTY*/ { $$ = LockWaitBlock; } + ; + /***************************************************************************** * @@ -9858,12 +9865,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; } ; @@ -12992,6 +12999,7 @@ unreserved_keyword: | LOCAL | LOCATION | LOCK_P + | LOCKED | MAPPING | MATCH | MATERIALIZED @@ -13074,6 +13082,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..3672240 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -62,7 +62,7 @@ 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 +1481,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 +1499,7 @@ 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 +1510,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 +1528,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/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 8364bef..388c352 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; 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..969584e 100644 --- a/src/include/parser/analyze.h +++ b/src/include/parser/analyze.h @@ -39,6 +39,6 @@ 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 b52e507..8d0bfb2 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("mapping", MAPPING, UNRESERVED_KEYWORD) PG_KEYWORD("match", MATCH, UNRESERVED_KEYWORD) PG_KEYWORD("materialized", MATERIALIZED, UNRESERVED_KEYWORD) @@ -343,6 +344,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..d72f77a --- /dev/null +++ b/src/include/utils/lockwaitpolicy.h @@ -0,0 +1,29 @@ +/*------------------------------------------------------------------------- + * + * 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. + */ +typedef enum +{ + /* Wait for the lock to become available */ + LockWaitBlock, + /* SELECT FOR UPDATE SKIP LOCKED, skipping rows that can't be locked */ + LockWaitSkip, + /* SELECT FOR UPDATE NOWAIT, abandoning the transaction */ + LockWaitError +} 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.out b/src/test/isolation/expected/nowait.out new file mode 100644 index 0000000..8713721 --- /dev/null +++ b/src/test/isolation/expected/nowait.out @@ -0,0 +1,329 @@ +Parsed test spec with 2 sessions + +starting permutation: s1a s1b s1c s2a s2b s2c +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s1c: COMMIT; +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT 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 NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: could not obtain lock on row in relation "queue" +step s1c: COMMIT; +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: current transaction is aborted, commands ignored until end of transaction block +step s2c: COMMIT; + +starting permutation: s1a s1b s2a s2b s1c s2c +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: could not obtain lock on row in relation "queue" +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: current transaction is aborted, commands ignored until end of transaction block +step s1c: COMMIT; +step s2c: COMMIT; + +starting permutation: s1a s1b s2a s2b s2c s1c +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: could not obtain lock on row in relation "queue" +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: current transaction is aborted, commands ignored until end of transaction block +step s2c: COMMIT; +step s1c: COMMIT; + +starting permutation: s1a s2a s1b s1c s2b s2c +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: could not obtain lock on row in relation "queue" +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s1c: COMMIT; +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: current transaction is aborted, commands ignored until end of transaction block +step s2c: COMMIT; + +starting permutation: s1a s2a s1b s2b s1c s2c +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: could not obtain lock on row in relation "queue" +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: current transaction is aborted, commands ignored until end of transaction block +step s1c: COMMIT; +step s2c: COMMIT; + +starting permutation: s1a s2a s1b s2b s2c s1c +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: could not obtain lock on row in relation "queue" +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: current transaction is aborted, commands ignored until end of transaction block +step s2c: COMMIT; +step s1c: COMMIT; + +starting permutation: s1a s2a s2b s1b s1c s2c +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: could not obtain lock on row in relation "queue" +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: current transaction is aborted, commands ignored until end of transaction block +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT 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 NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: could not obtain lock on row in relation "queue" +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: current transaction is aborted, commands ignored until end of transaction block +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT 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 NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: could not obtain lock on row in relation "queue" +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: current transaction is aborted, commands ignored until end of transaction block +step s2c: COMMIT; +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT 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 NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: could not obtain lock on row in relation "queue" +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: current transaction is aborted, commands ignored until end of transaction block +step s1c: COMMIT; +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT 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 NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: could not obtain lock on row in relation "queue" +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: current transaction is aborted, commands ignored until end of transaction block +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT 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 NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: could not obtain lock on row in relation "queue" +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: current transaction is aborted, commands ignored until end of transaction block +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT 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 NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: could not obtain lock on row in relation "queue" +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: current transaction is aborted, commands ignored until end of transaction block +step s1c: COMMIT; +step s2c: COMMIT; + +starting permutation: s2a s1a s2b s1b s2c s1c +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: could not obtain lock on row in relation "queue" +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: current transaction is aborted, commands ignored until end of transaction block +step s2c: COMMIT; +step s1c: COMMIT; + +starting permutation: s2a s1a s2b s2c s1b s1c +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: could not obtain lock on row in relation "queue" +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s2c: COMMIT; +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: current transaction is aborted, commands ignored until end of transaction block +step s1c: COMMIT; + +starting permutation: s2a s2b s1a s1b s1c s2c +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: could not obtain lock on row in relation "queue" +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: current transaction is aborted, commands ignored until end of transaction block +step s1c: COMMIT; +step s2c: COMMIT; + +starting permutation: s2a s2b s1a s1b s2c s1c +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: could not obtain lock on row in relation "queue" +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: current transaction is aborted, commands ignored until end of transaction block +step s2c: COMMIT; +step s1c: COMMIT; + +starting permutation: s2a s2b s1a s2c s1b s1c +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: could not obtain lock on row in relation "queue" +step s2c: COMMIT; +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +ERROR: current transaction is aborted, commands ignored until end of transaction block +step s1c: COMMIT; + +starting permutation: s2a s2b s2c s1a s1b s1c +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s2c: COMMIT; +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; +id data status + +1 foo NEW +step s1c: 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 36acec1..fd22009 100644 --- a/src/test/isolation/isolation_schedule +++ b/src/test/isolation/isolation_schedule @@ -25,3 +25,5 @@ test: propagate-lock-delete test: drop-index-concurrently-1 test: alter-table-1 test: timeouts +test: skip-locked +test: nowait diff --git a/src/test/isolation/specs/nowait.spec b/src/test/isolation/specs/nowait.spec new file mode 100644 index 0000000..83cedae --- /dev/null +++ b/src/test/isolation/specs/nowait.spec @@ -0,0 +1,26 @@ +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 NOWAIT LIMIT 1; } +step "s1b" { SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; } +step "s1c" { COMMIT; } + +session "s2" +setup { BEGIN; } +step "s2a" { SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; } +step "s2b" { SELECT * FROM queue ORDER BY id FOR UPDATE NOWAIT LIMIT 1; } +step "s2c" { COMMIT; } diff --git a/src/test/isolation/specs/skip-locked.spec b/src/test/isolation/specs/skip-locked.spec new file mode 100644 index 0000000..2ce4739 --- /dev/null +++ b/src/test/isolation/specs/skip-locked.spec @@ -0,0 +1,26 @@ +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