SKIP LOCKED DATA

Hi

Apologies for posting about new vapourware features for distant
future releases at a very busy time in the cycle for 9.2...

I am wondering out loud whether I am brave enough to try to propose
SKIP LOCKED DATA support and would be grateful for any feedback and/or
{en|dis}couragement. I don't see it on the todo list, and didn't find
signs of others working on this (did I miss something?), but there are
examples of users asking for this feature (by various names) on the
mailing lists. Has the idea already been rejected, is it
fundamentally infeasible for some glaring reason, or far too
complicated for new players?

What it is:

Like the existing NOWAIT option, it means your query doesn't wait for
others sessions when trying to get an exclusive lock. However, rather
than returning an error if it would block, it simply skips over the
rows that couldn't be locked.

What it looks like in other RDBMSs:

DB2 (z/OS only): FOR UPDATE SKIP LOCKED DATA
Oracle: FOR UPDATE SKIP LOCKED
Sybase: FOR UPDATE READPAST
MS SQL Server: FOR UPDATE WITH (READPAST)

(I'm not 100% sure about the last two, which I found by googling for
equivalents of the first two, and there are no doubt subtle differences
among these).

What it's for:

A common usage for this is to increase parallelism in systems with
multiple workers taking jobs from a queue. I've used it for this
purpose myself on another RDBMS, having seen it recommended for some
types of work queue implementation. It may have other uses.

How it might be implemented in PostgreSQL:

1. Extend the grammar and parser to support SKIP LOCKED DATA (or some
other choice of words) in the same place that NOWAIT can appear.

2. Modify heap_lock_tuple so that the boolean 'nowait' argument is
replaced by an enumeration LockWaitPolicy with values
LOCK_WAIT_POLICY_WAIT (= what false currently does),
LOCK_WAIT_POLICY_LOCK_OR_ERROR (= what true currently does),
LOCK_WAIT_POLICY_LOCK_OR_SKIP (= new behaviour). Where currently
'nowait' is handled, the new case would also be handled, cleaning up
resources and returning a new HTSU_Result enumerator
HeapTupleWouldBlock.

3. Modify ExecLockRows to pass the appropriate value to
heap_lock_tuple (presumably received via ExecRowMark, as nowait is
received currently). Modify the switch on the result of that call,
treating the new case HeapTupleWouldBlock the same way that
HeapTupleSelfUpdated is treated -- that is, goto lnext to fetch the
next tuple.

4. Probably some changes to handle table-level locks too.

5. Probably many other things that I'm not aware of right now and
won't discover until I dig/ask further and/or run into a brick wall!

Useful? Doable?

Thanks,

Thomas Munro