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.
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
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