LOCK Option (lock_option) 

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

Syntax

<lock_option> ::=
  WITH LOCK [(IGNORE)|(NOWAIT)] [EXCLUSIVE|OPTIMISTIC] [ISOLATION LEVEL <unsigned_integer>]

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

Explanation

(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 REQUEST_TIMEOUT).

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 REQUEST_TIMEOUT).

If (NOWAIT) is specified, the database system does not wait until another user has released a data object. Instead, it returns a message 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 is only meaningful in connection with the isolation levels 0, 1, 10, and 15.

Shared lock

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.

See also:

LOCK Statement (lock_statement)

Concepts of the Database System, Locks for Database Objects