ALLBASE/SQL Reference Manual (36216-90216)

460 Chapter11
SQL Statements E - R
LOCK TABLE
LOCK TABLE
The LOCK TABLE statement provides a means of explicitly acquiring a lock on a table, to
override the automatic locking provided by ALLBASE/SQL in accord with the CREATE
TABLE locking modes.
Scope
ISQL or Application Programs
SQL Syntax
LOCK TABLE [ Owner. ] TableName IN {SHARE [UPDATE]
EXCLUSIVE }MODE
Parameters
[ Owner .] TableName specifies the table to be locked.
SHARE allows other transactions to read but not change the table during the time
you hold the lock.
Your transaction is delayed until any active transactions that have
changed the table have ended. Then you can retrieve from the specified
table with no further delays or overhead due to locking. Automatic locking
of pages or rows takes place as usual any time your transaction changes
the table.
SHARE UPDATE indicates that you may wish to update the rows selected. Other
transactions may not update the data page you are currently reading. If
you decide to update the row, an exclusive lock is obtained, so that other
transactions cannot read or update the page; This lock is held until the
transaction ends with a COMMIT WORK or ROLLBACK WORK statement.
EXCLUSIVE prevents other transactions from reading or changing the table during the
time you hold the lock.
Your transaction is delayed until any transactions that were previously
granted locks on the table have ended. Then your transaction experiences
no further overhead or delays due to locking on the specified table.
Description
• Of the three lock types described here, the highest level is exclusive (X), the next share
update (SIX), and the lowest share (S). When you request a lock on an object which is
already locked with a higher severity lock, the request is ignored.
• This statement can be used to avoid the overhead of acquiring many small locks when
scanning a table. For example, if you know that you are accessing all the rows of a table,
you can lock the entire table at once instead of letting ALLBASE/SQL automatically
lock each individual page or row as it is needed.