Home

	Expand Search

Transact-SQL User's Guide

Chapter 19 Locking Commands and Options

Readpast locking for queue processing

Readpast locking for queue processing

Readpast locking is an option available for the select and readtext commands and the data modification commands update, delete, and writetext. It 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 syntax

The syntax for using readpast locking is:

{select | update | delete} ...
     from tablename [holdlock | noholdlock] 
          [readpast] [shared]
          ...

readtext [[database.]owner.]table_name.column_name
     text_pointer offset size 
     [holdlock | noholdlock] [shared] [readpast]
          ...

writetext [[database.]owner.]table_name.column_name
     text_pointer [readpast] [with log] data

Incompatible locks during readpast queries

For select and readtext commands, incompatible locks are exclusive locks. Therefore, select and readpast commands can access any rows or pages on which shared or update locks are held.

For delete, update, and writetext commands, any type of page or row lock is incompatible, so that:

• All rows with shared, update, or exclusive row locks are skipped in datarows-locked tables, and

• All pages with shared, update, or exclusive locks are skipped in datapages-locked tables.

All commands specifying readpast block if there is an exclusive table lock, except select commands executed at transaction isolation level 0.

Allpages-locked tables and readpast queries

If the readpast option is specified for an allpages-locked table, the readpast option is ignored. The command operates at the isolation level specified for the command or session:

• If the isolation level is 0, dirty reads are performed, and the command returns values from locked rows, and does not block.

• If the isolation level is 1 or 3, the command blocks when pages with incompatible locks must be read.

Effects of isolation levels select queries with readpast

Readpast locking is designed to be used at transaction isolation level 1 or 2.

For data-only-locked tables, the effects of readpast on a table in a select command are shown in Table 19-1.

If select commands that specify readpast also include any of the following clauses, the commands fail and display error messages:

• The at isolation clause, specifying 0 or read uncommitted

• The at isolation clause, specifying 3 or serializable

• The holdlock keyword on the same table

If a select query that specifies readpast also specifies at isolation 2 or at isolation repeatable read, shared locks are held on the readpast table or tables until the statement or transaction completes.

readtext commands that include readpast and that specify at isolation read uncommitted automatically run at isolation level 0 after issuing a warning message.

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.

• For datapages-locked tables, these commands modify all rows on all pages that are not locked with incompatible locks.

• For datarows-locked tables, they affect all rows that are not locked with incompatible locks.

If the transaction isolation level for a session is 3 (serializable reads), the delete, update, and writetext commands that use readpast automatically block when they encounter a row or page with an incompatible lock.

At transaction isolation level 2 (serializable reads), the delete, update, and writetext commands:

• Modify all rows on all pages that are not locked with incompatible locks.

• For datarows-locked tables, they affect all rows that are not locked with incompatible locks.

text 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.

If an update command with the readpast option applies to two or more text columns, and the first text column checked has an incompatible lock on it, readpast locking skips the row. If the column does not have an incompatible lock, the command acquires a lock and modifies the column. Then, if any subsequent text column in the row has an incompatible lock on it, the command blocks until it can obtain a lock and modify the column.

A delete command with the readpast option skips the row if any of the text columns in the row have an incompatible lock.

Readpast-locking examples

The following examples illustrate 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