LOCK Statement (lock_statement) 
The LOCK statement (lock_statement) assigns a lock to objects in the current transaction.
Structure
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 RequestTimeout 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 , it is essential that the specified table has 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 results 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.
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.