Informix Guide to SQL: Syntax SQL Statements

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

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

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

SET LOCK MODE TO WAIT

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 places 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 networked 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. The number of seconds that the process waits applies only if you acquire locks within the current database server and a remote database server within the same transaction.

Related statements: LOCK TABLE, SET ISOLATION, SET TRANSACTION and UNLOCK TABLE

For a discussion on how to set the lock mode, see the Informix Guide to SQL: Tutorial.