LOCK Statement (lock_statement) 

The LOCK statement (lock_statement) assigns a lock to objects in the current transaction.

Syntax

<lock_statement> ::=
LOCK [(WAIT)|(NOWAIT)] <lock_spec> IN SHARE MODE
LOCK [(WAIT)|(NOWAIT)] <lock_spec> IN EXCLUSIVE MODE
LOCK [(WAIT)|(NOWAIT)] <lock_spec> IN SHARE MODE <lock_spec> IN EXCLUSIVE MODE
LOCK [(WAIT)|(NOWAIT)] <row_spec> ... OPTIMISTIC

<lock_spec> ::= TABLE <table_name>,...
| <row_spec> ...
| TABLE <table_name>,... | <row_spec> ...

<row_spec> ::= ROW <table_name> KEY <key_spec>,...
| ROW <table_name> CURRENT OF <result_table_name>

Explanation

The table specified cannot be a temporary base table, a view table, or a synonym. If the table name identifies a view table, locks are set on the underlying base tables for the view table.

(WAIT)/(NOWAIT)

●     If (NOWAIT) is specified, the database does not wait for a lock to be released by another transaction. Instead, it issues an error message if a lock collision occurs. If there is no collision, the requested lock is set.

●     In the event of a lock collision, if a wait option or (WAIT) is specified, the system waits for locks to be released until the period specified by the special database parameter REQUEST_TIMEOUT has elapsed.

<lock_spec>

TABLE <table_name>,...

If TABLE <table_name>,... is specified, a lock is created for the table in question.

If the view table identified by the table name is not changeable, only a shared lock can be set for this view table. As a result of this SQL statement, shared locks are set for all base tables underlying the view table.

| <row_spec> ...

A <row_spec>... creates a lock for the table row denoted by the key values or a position in a results table.

In order to specify a row_spec, the specified table must have a key column. This means that if the table name identifies a view table, it must be modifiable.

For tables defined without key columns, the implicit key column SYSKEY CHAR(8) BYTE can be used in a key specification  key_spec .

If CURRENT OF <result_table_name> is specified, the result table must have been specified with FOR UPDATE.

SHARE

SHARE defines a shared lock for the listed objects. To set shared locks, the current user must have the SELECT privilege.

EXCLUSIVE

EXCLUSIVE defines an exclusive lock for the listed objects. To set exclusive locks, the current user must have the UPDATE, DELETE, or INSERT privilege.

OPTIMISTIC

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

More Information

Deadlock

If the database system detects a deadlock caused by locks, it ends the transaction with an implicit ROLLBACK WORK.

Reproducibility

If reproducible results are needed to read rows using a select statement, the read objects must be locked and the locks must be kept until reproduction. Reproducibility usually requires that the tables concerned are locked either explicitly using one or more lock statements or implicitly by using the isolation level 3. This ensures that other users cannot modify the table.

See also:

LOCK Option (lock_option)

LOCK Statement (lock_statement)

Concepts of the Database System, Locks for Database Objects