The LOCK statement assigns a lock to the current transaction (see lock).

row_spec, table_name

The specified table cannot be a base table, a view table (see table), nor a synonym. If the table name identifies a view table, locks are set on the base tables on which the view table is based.

• 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 either the wait option is omitted or (WAIT) is specified, the system waits for locks to be released, until the period specified by the database parameter REQUEST_TIMEOUT has elapsed.

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

Specifying a row_spec requires that the specified table have a key column; that is, if the table name identifies a view table, this must be modifiable.

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 share lock can be set for this view table. As a result of this SQL statement, share locks are set for all base tables underlying the view table.

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

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 defines an optimistic lock on rows. This is only meaningful in connection with isolation levels 0, 1, 10, and 15.

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 share locks are set for the tables concerned, either by explicitly locking them using one or more lock statements, or implicitly by using the isolation level 3. This ensures that other users cannot modify the table.

Lock Behavior