Adaptive Server has two levels of locking:
For tables that use allpages locking or datapages locking, either page locks or table locks.
For tables that use datarows locking, either row locks or table locks
Page or row locks are less restrictive (or smaller) than table locks. A page lock locks all the rows on data page or an index page; a table lock locks an entire table. A row lock locks only a single row on a page. Adaptive Server uses page or row locks whenever possible to reduce contention and to improve concurrency.
Adaptive Server uses a table lock to provide more efficient locking when an entire table or a large number of pages or rows will be accessed by a statement. Locking strategy is directly tied to the query plan, so the query plan can be as important for its locking strategies as for its I/O implications. If an update or delete statement has no useful index, it performs a table scan and acquires a table lock. For example, the following statement acquires a table lock:
update account set balance = balance * 1.05
If an update or delete statement uses an index, it begins by acquiring page or row locks. It tries to acquire a table lock only when a large number of pages or rows are affected. To avoid the overhead of managing hundreds of locks on a table, Adaptive Server uses a lock promotion threshold setting. Once a scan of a table accumulates more page or row locks than allowed by the lock promotion threshold, Adaptive Server tries to issue a table lock. If it succeeds, the page or row locks are no longer necessary and are released. See “Configuring locks and lock promotion thresholds” for more information.
Adaptive Server chooses which type of lock to use after it determines the query plan. The way you write a query or transaction can affect the type of lock the server chooses. You can also force the server to make certain locks more or less restrictive by specifying options for select queries or by changing the transaction’s isolation level. See “Controlling isolation levels” for more information. Applications can explicitly request a table lock with the lock table command.
