HP 3000 Manuals HP 3000 Manuals
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]} MODE
{EXCLUSIVE }
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.
* LOCK TABLE can be useful in avoiding deadlocks by locking tables
in a predetermined order.
* To ensure data consistency, all locks are held until the end of
the transaction, at which point they are released. For this
reason no UNLOCK statement is available or necessary.
Authorization
You can issue this statement if you have SELECT or OWNER authority for
the table or if you have DBA authority.
Examples
1. Share Mode Lock
BEGIN WORK
Other transactions can issue only SELECT statements against the
table until this transaction is terminated.
LOCK TABLE PurchDB.OrderItems in SHARE MODE
The lock is released when the transaction is either committed or
rolled back.
COMMIT WORK
2. Share Update Mode Lock
BEGIN WORK
Other transactions can issue only SELECT statements against the
table:
LOCK TABLE PurchDB.OrderItems in SHARE UPDATE MODE
Other transactions can read the same page as the current
transaction.
SELECT ... FROM PurchDB.OrderItems
The shared lock is now upgraded to an exclusive lock for the page
on which the update is taking place. Other transactions must wait
for this transaction to be committed or rolled back.
UPDATE PurchDB.OrderItems SET ...
All locks are released when the transaction is either committed or
rolled back.
COMMIT WORK