|
|
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.
ISQL or Application Programs
LOCK TABLE [Owner.]TableName IN {SHARE [UPDATE]
EXCLUSIVE }MODE
- [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.
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.
You can issue this statement if you have SELECT or OWNER authority
for the table or if you have DBA authority.
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
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
|