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

• Incompatible Locks During readpast Queries
  For select and readtext commands, incompatible locks are exclusive locks. Therefore, select and readtext commands can access any rows or pages on which shared or update locks are held.
• Allpages-Locked Tables and readpast Queries
  The readpast option is ignored when it is specified for an allpages-locked table.
• Effects of Isolation Levels Select Queries with readpast
  Readpast locking will have different effects in the select command based on the transaction isolation level.
• Data Modification Commands with readpast and Isolation Levels
  If the transaction isolation level for a session is 0, the delete, update, and writetext commands that use readpast do not issue warning messages.
• text, unitext, and image columns and readpast
  If a select command with the readpast option encounters a text column that has an incompatible lock on it, readpast locking retrieves the row, but returns the text column with a value of null. No distinction is made, in this case, between a text column containing a null value and a null value returned because the column is locked.