 |
» |
|
|
|
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.
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 | |
Share Mode Lock 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. Share Update Mode Lock 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.
|
