SET LOCK MODE statement

Use the SET LOCK MODE statement to define how the database server handles a process that tries to access a locked row or table.

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax


Element	Description	Restrictions	Syntax
seconds	Maximum number of seconds that a process waits for a lock to be released before issuing an error	Valid only if shorter than system default	Literal Number

Usage

This statement can direct the response of the database server in the following ways when a process tries to access a locked row or table.

Lock Mode: Effect
NOT WAIT: Database server ends the operation immediately and returns an error code. This condition is the default.
WAIT: Database server suspends the process until the lock releases.
WAIT seconds: Database server suspends the process until the lock releases or until the waiting period ends. If the lock remains after the waiting period, the operation ends and an error code is returned.

For a description of the two distinct meanings of the term lock mode in this document, see Locking Granularity in the related concepts section.

To avoid waiting in operations that attempt to read rows on which concurrent sessions hold exclusive row-level locks, you can also use the LAST COMMITTED feature, either by setting it explicitly in the SET ISOLATION COMMITTED READ statement, or by setting the USELASTCOMMITTED configuration parameter or the USELASTCOMMITTED session environment option.

Examples

In the following example, the user specifies that if the process requests a locked row, the operation should end immediately and an error code should be returned:

SET LOCK MODE TO NOT WAIT;

In the following example, the user specifies that the process should be suspended until the lock is released:

SET LOCK MODE TO WAIT;

The next example sets an upper limit of 17 seconds on the length of any wait:

SET LOCK MODE TO WAIT 17;