>>-SET LOCK MODE TO--+-NOT WAIT----------+---------------------><
                     '-WAIT--+---------+-'
                             '-seconds-'
 

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, p. Literal Number

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.

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

The WAIT clause causes the database server to suspend the process until the lock is released or until a specified number of seconds have passed without the lock being released.

The database server protects against the possibility of a deadlock when you request the WAIT option. Before the database server suspends a process, it checks whether suspending the process could create a deadlock. If the database server discovers that a deadlock could occur, it ends the operation (overruling your instruction to wait) and returns an error code. In the case of either a suspected or actual deadlock, the database server returns an error.

Cautiously use the unlimited waiting period that was created when you specify the WAIT option without seconds. If you do not specify an upper limit, and the process that placed the lock somehow fails to release it, suspended processes could wait indefinitely. Because a true deadlock situation does not exist, the database server does not take corrective action.

In a network environment, the DBA uses the ONCONFIG parameter DEADLOCK_TIMEOUT to establish a default value for seconds. If you use a SET LOCK MODE statement to set an upper limit, your value applies only when your waiting period is shorter than the system default.

Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]