Show TOC

Background documentationLOCK Option (lock_option)

 

The LOCK option (lock_option) requests a lock for each selected row.

Structure

Syntax Syntax

  1. <lock_option> ::=
  WITH LOCK [(IGNORE)|(NOWAIT)] [EXCLUSIVE|OPTIMISTIC] [ISOLATION LEVEL <unsigned_integer>]
End of the code.

unsigned_integer may only have the values 0, 1, 2, 3, 10, 15, 20 or 30

(IGNORE)

If (IGNORE) is not specified and a lock collision occurs, the system waits for a locked row to be released (but only as long as is specified by the special database parameter RequestTimeout).

If (IGNORE) is specified, the system does not wait for a locked row to be released by another transaction. Instead, it ignores this row if a lock collision occurs. If there is no collision, the requested lock is set. (IGNORE) can only be specified in isolation level 1.

(NOWAIT)

If (NOWAIT) is not specified and a lock collision occurs, the system waits for the locked data object to be released (but only as long as is specified by the database parameter RequestTimeout).

If (NOWAIT) is specified, the database system does not wait until another user has released a data object. Instead, it issues a return code if a collision occurs. If there is no collision, the requested lock is set.

EXCLUSIVE

An exclusive lock is defined. As long as the locked row has not been changed or deleted, the exclusive lock can be released using the UNLOCK statement.

OPTIMISTIC

OPTIMISTIC defines an optimistic lock on rows. This only makes sense in connection with the isolation levels 0, 1, 10 and 15.

Shared Locks

If neither EXCLUSIVE nor OPTIMISTIC is specified, a shared lock is set for the corresponding rows.

ISOLATION LEVEL

The locks are set independently of the ISOLATION specification (isolation_spec) of the CONNECT statement. The isolation level of the LOCK option can have a higher or lower value than that in the CONNECT statement.

If an isolation level is specified by the LOCK option, it is only valid for the duration of the SQL statement that contains the LOCK option specification. Afterwards, the isolation level that was specified in the CONNECT statement is applicable again. In the case of a SELECT statement (select_statement) for which the results table is not actually physically generated, the specified isolation level is valid for this SQL statement and all FETCH statements that refer to the result table. The isolation level that was specified in the CONNECT statement is applicable for other SQL statements that were executed in the meantime.

More Information

LOCK Statement (lock_statement)

Concepts of the Database System, SQL Locks