ALLBASE/SQL Reference Manual (36216-90216)

Chapter 11 461
SQL Statements E - R
LOCK TABLE
• 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