Readpast Locking for Queue Processing

Readpast locking instructs a command to silently skip all incompatible locks it encounters, without blocking, terminating, or generating a message. It is primarily used when the rows of a table constitute a queue.

In such a case, a number of tasks may access the table to process the queued rows, which could, for example, represent queued customers or customer orders. A given task is not concerned with processing a specific member of the queue, but with processing any available members of the queue that meet its selection criteria.

Readpast locking is an option that is available for the select and readtext commands and the data modification commands update, delete, and writetext.

These examples illustrating readpast locking:

To skip all rows that have exclusive locks on them:

select * from titles readpast

To update only rows that are not locked by another session:

update titles 
    set price = price * 1.1 
    from titles readpast

To use readpast locking on the titles table, but not on the authors or titleauthor table:

select *
    from titles readpast, authors, titleauthor
    where titles.title_id = titleauthor.title_id
    and authors.au_id = titleauthor.au_id

To delete only rows that are not locked in the stores table, but to allow the scan to block on the authors table:

delete stores from stores readpast, authors
where stores.city = authors.city