Adaptive Server has two levels of locking:
Page locks or table locks are used for tables that use allpages locking or datapages locking.
Row locks or table locks are used for tables that use datarows locking.
Page or row locks are less restrictive (or smaller) than table locks. A page lock locks all the rows on a 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 is accessed by a statement. Locking strategy is directly tied to the query plan, so a query plan can be as important for its locking strategies as for its I/O implications. For data-only-locked tables, an update or delete statement without a useful index performs a table scan and acquires a table lock. For example, the following statement acquires a table lock if the account table uses the datarows or datapages locking scheme:
update account set balance = balance * 1.05
An update or delete statement using an index begins by acquiring page or row locks. It acquires 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 (configured with sp_setpglockpromote). Once table scan 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”.
Adaptive Server chooses which type of lock to use after it determines the query plan. The way a query or transaction is written can affect the type of lock the server chooses. You can 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”. Applications can use the lock table command to explicitly request a table lock.
