locking_specification [...] SQL_request [;]
Syntax Elements
- locking_specification
LOCKING { [ DATABASE ] database_name |
[ TABLE ] table_name |
[ VIEW ] view_name |
ROW
} [ FOR | IN ] lock_type [ MODE ] [ NOWAIT ]
- SQL_request
- A valid SQL request.
- You cannot specify LOCKING with the CALL statement.
- If SQL_request is null, then the only effect of LOCK is to lock the specified object.
- This specification is required for row-level locking (rowhash or rowkey), but optional for database-level locking, base table-level locking, and view-level locking.
- DATABASE
- Optional keyword to indicate the type of object to lock is a database.
- database_name
- Name of the database or user to be locked.
- TABLE
- Optional keyword to indicate the type of object to lock is a table.
- table_name
- Name of the base table to be locked.
- VIEW
- Optional keyword to indicate the type of object to lock is a view.
- view_name
- Name of the view to be locked.
- ROW
- Optional keyword specifying rowhash or rowkey level for locking in accordance with the defining statement. See Using LOCKING ROW.
- lock_type
{ ACCESS |
{ EXCLUSIVE | EXCL } |
SHARE |
WRITE |
CHECKSUM
}
For Object File System tables, CHECKSUM has a default value that you cannot change.
- MODE
- Optional keyword following the lock type.
- NOWAIT
- If the system cannot get the indicated lock, the request must end.
- Specify this option when a request waiting for resources may tie up resources otherwise available to another request.
- ACCESS
- Permits selection of data from a base table that can be locked for write access by other users.
- The data selected using an ACCESS lock can be inconsistent because the data may be modified concurrently with the request. Therefore, only use this lock for casual inspection of data.
- Placing an ACCESS lock requires the SELECT privilege on the specified object.
- EXCLUSIVE
- EXCL
- Excludes all other users.
- This is the most restrictive lock.
- EXCLUSIVE locks are rarely used except to make structural changes to a database.
- Placing an EXCLUSIVE lock on a database object requires the DROP privilege on that object.
- READ
- SHARE [Deprecated]
- Makes sure of data consistency during a read operation such as a SELECT request.
- Multiple users can concurrently hold a READ lock on a base table. If a READ lock is in place, no modification of the object is allowed.
- Placing a READ lock requires the SELECT privilege on the specified object.
- WRITE
- Enables a single user to modify data.
- If the WRITE lock is in place, all other users are excluded from viewing or modifying the object except readers who are viewing data using an ACCESS lock.
- Until a WRITE lock is released, no new READ locks are permitted on the locked object.
- Placing a WRITE lock requires an UPDATE, INSERT, or DELETE privilege on the specified object.
- CHECKSUM
- Used only for updatable cursor queries submitted outside of stored procedures.