Sybase Technical Library - Product Manuals Home
[Search Forms] [Previous Section with Hits] [Next Section with Hits] [Clear Search] Expand Search

Chapter 18 Transactions: Maintaining
Data Consistency and Recovery [Table of Contents] Appendix A The pubs2 Database

Transact-SQL User's Guide

[-] Chapter 19 Locking Commands and Options

Chapter 19

Locking Commands and Options

Adaptive Server provides a wide range of commands and options for providing locking. This chapter reviews the types of locking available and how they are invoked through Transact SQL.

Setting a time limit on waiting for locks

Adaptive Server allows you to specify a lock wait period that determines how long a command will wait to acquire locks:

wait/nowait option of the lock table command

Within a transaction, the lock table command allows you to request a table lock on a table without waiting for the command to acquire enough row-level or page-level locks to escalate to a table lock.

The lock table command contains a wait/nowait option that allows you to specify the length of time the command will wait until operations in other transactions relinquish any locks they have on the target table.

The syntax for lock table is:

lock table table_name in {share | exclusive} mode
     [wait [no_of_seconds] | nowait]

The following command, inside a transaction, sets a wait period of 2 seconds for acquiring a table lock on the titles table:

lock table titles in share mode wait 2

If the wait time expires before a table lock is acquired, the transaction proceeds, and row or page locking is used, exactly as it would have been without lock table, and the following informational message (error number 12207) is generated:

Could not acquire a lock within the specified wait period. COMMAND level wait...

For a code example of handling this error message during a transaction, see lock table in the Reference Manual.

If you use lock table...wait without specifying no_of_seconds, the command waits indefinitely for a lock.

You can set time limits on waiting for a lock at the session level and the system level, as described in the following sections. The wait period set with the lock table command overrides both of these

The nowait option is equivalent to the wait option with a 0-second wait: lock table either obtains a table lock immediately or generates the informational message given above. If the lock is not acquired, the transaction proceeds as it would have without the lock table command.

You can use the set lock command at either the session level or within a stored procedure to control the length of time a task will wait to acquire locks.

A System Administrator can use the sp_configure option, lock wait period, to set a server-wide time limit on acquiring locks.

Setting a session-level lock-wait limit

You can use set lock wait to control the length of time that a command in a session or in a stored procedure waits to acquire locks. The syntax is:

set lock {wait no_of_seconds | nowait}

no_of_seconds is an integer. Thus, the following example sets a session-level time limit of 5 seconds on waiting for locks:

set lock wait 5

With one exception, if the set lock wait period expires before a command acquires a lock, the command fails, the transaction containing it is rolled back, and the following error message is generated:

Msg 12205, Level 17, State 2:
Server 'sagan', Line 1:
Could not acquire a lock within the specified wait period. SESSION level wait period=300 seconds, spid=12, lock type=shared page, dbid=9, objid=2080010441, pageno=92300, rowno=0. Aborting the transaction.

The exception to this occurs when lock table in a transaction sets a longer wait period than set lock wait. In this case, the transaction uses the lock table wait period before timing out, as described in the preceding section.

The set lock nowait option is equivalent to the set lock wait option with a 0-second wait. If a command other than lock table cannot obtain a requested lock immediately, the command fails, its transaction is rolled back, and the preceding error message is generated.

If both a server-wide lock-wait limit and a session-level lock-wait limit are set, the session-level limit takes precedence. If no session-level wait period is set with set lock wait, the server-level wait period is used.

Stored procedures do not use a wait period set at either the server level or the session level. The wait period for commands in a stored procedure is unbounded, unless you explicitly specify a time limit by using the set lock wait command inside the stored procedure.

Setting a server-wide lock-wait limit

A System Administrator can configure a server-wide lock-wait limit with the configuration parameter lock wait period. The syntax is:

sp_configure "lock wait period" [, no_of_seconds]

If the lock-wait period expires before a command acquires a lock, unless there is an overriding set lock wait or lock table wait period, the command fails, the transaction containing it is rolled back, and the following error message is generated:

Msg 12205, Level 17, State 2:
Server 'wiz', Line 1:
Could not acquire a lock within the specified wait period. SERVER level wait period=300 seconds, spid=12, lock type=shared page, dbid=9, objid=2080010441, pageno=92300, rowno=0. Aborting the transaction.

A time limit entered through set lock wait or lock table wait overrides a server-level lock-wait period. Thus, for example, if the server-level wait period is 5 seconds and the session-level wait period is 10 seconds, an update command waits 10 seconds to acquire a lock before failing and aborting its transaction.

The default server-level lock-wait period is effectively "wait forever." To restore the default after setting a time-limited wait, you can use sp_configure to set the value of lock wait period as follows:

sp_configure "lock wait period", 0, "default"

Information on the number of lock-wait timeouts

sp_sysmon reports on the number of times tasks waiting for locks did not acquire the lock within the specified period. See the Performance and Tuning Guide for more information.

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 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:

Effects of isolation levels select queries with readpast

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

Session-level transaction isolation levels and readpast

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

Session-level isolation level and the use of readpast

Session Isolation Level

Effects

0, read uncommitted (dirty reads)

readpast is ignored, and rows containing uncommitted transactions are returned to the user. A warning message is printed.

1, read committed

Rows or pages with incompatible locks are skipped; no locks are held on the rows or pages read.

2, repeatable read

Rows or pages with incompatible locks skipped; shared locks are held on all rows or pages that are read until the end of the statement or transaction.

3, serializable

readpast is ignored, and the command executes at level 3. The command blocks on any rows or pages with incompatible locks.

Query-level isolation levels and readpast

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

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.

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:

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


Backup
and recovery of transactions [Table of Contents] Appendix A The pubs2 Database