LOCK TABLE is an executable command that can be issued interactively, embedded in a host language, or dynamically prepared.
To execute the LOCK TABLE command, users must either possess DBADM authority on the database, overall SYSADM or SYSCTRL authority for the location, SELECT privilege on the table identified, or be the owner of the table.
If the statement is embedded in an application program, the privilege set is the privileges that are held by the authorization ID of the owner of the plan or package. If the statement is dynamically prepared, the privilege set is determined by the DYNAMICRULES behavior in effect (run, bind, define, or invoke.)
Bold text indicates clauses or options that are supported only syntactically.
LOCK TABLE table-name [PARTITION integer]
IN {SHARE | EXCLUSIVE} MODE
| table-name | Identifies the table to be locked. The table must exist at the current server, and cannot be a view, temporary table (created or declared) or system catalog table. |
Locking is effective in multi-user environments only. However, the command is accepted in single-user environments, so that you can run your applications unchanged in either environment. The XDB Server automatically acquires record level locks as required during processing, so it is not necessary to acquire table level locks. However, table level locks may reduce the amount of time required to process large numbers of records in the same table. Two table locking modes are supported: exclusive and shared. All types of locks are released by a ROLLBACK or COMMIT command. For more information about the use of locks, see the Server Administration Guide.
The keyword EXCLUSIVE is used to acquire an exclusive lock on an existing table, usually when the intent of the transaction is to modify a number of records in the table. Until the lock is released, it prevents concurrent processes from executing any operations on the table. However, unless the lock is on a LOB table space, concurrent processes that are running at an isolation level of uncommitted read (UR) can execute read-only operations on the table.
When an exclusive table level lock is acquired, update transactions progress more quickly because there is no delay waiting for individual record level locks. However, an exclusive lock may take longer to acquire, and it does reduce concurrency by preventing any other transactions from accessing the locked table. For these reasons, the exclusive mode should be reserved for use by high volume record update transactions or when a transaction has a high priority. The loss of concurrency and the possible need to undo many changes when the transaction is rolled back can be minimized by keeping transactions as short as possible.
The keyword SHARE is used to acquire a shared lock on an existing database table, usually when the intent of the transaction is to read many records or perform repeatable reads. When a shared lock is acquired on a table, other users may read the records in that table, but cannot perform inserts, updates or deletes. Other users must acquire an exclusive record level lock on the table first, before executing INSERT, UPDATE and DELETE transactions.
All table level locks may be released by the UNLOCK TABLE command. The XDB Server protects against deadlock by regulating the persistency of lock requests. You can change the timeout interval through the Options utility. A single attempt to acquire a lock will last no longer than the period specified.
The example below shows the command to acquire an exclusive lock on the PART table:
LOCK TABLE part IN EXCLUSIVE MODE
The next example shows the command to acquire a shared lock on the PARTSUPP table:
LOCK TABLE partsupp IN SHARE MODE
Acquiring a table lock on a table ensures the correctness of "repeatable reads." For example, the query
SELECT SUM(qty)
FROM partsupp
will be correct because no other transaction will have the opportunity to change values in the QTY column while the lock is held.