ALLBASE/SQL Reference Manual (36216-90216)

178 Chapter5
Concurrency Control through Locks and Isolation Levels
Details of Locking
Figure 5-4. also portrays a query that accesses two pages of a table.
Figure 5-4. Row Versus Page Level Locking
With row level locking, only the rows containing data scanned for the query are locked. All
other rows can be locked by other transactions. With page level locking, the same query
locks an entire page, even if the page contains row(s) not used by the query.
Table size can affect concurrency at the page level. For example, if a small table occupies
only one page, then the effect of a page level lock is the same as locking the entire table. In
the case of small tables where frequent access is needed by multiple transactions, row level
locking can provide the best concurrency. After issuing an UPDATE STATISTICS statement
on a table, you can query the SYSTEM.TABLE view to determine how many pages it
occupies.
Table level locking serializes access to the table, that is, forces transactions with
incompatible locks to operate on a table one at a time. This reduces deadlocks by keeping
other users from accessing the table until the transaction is committed or otherwise
terminated. A small table limits concurrency by its very nature since the probability is
high that many users will want to access the limited number of pages or rows. By locking a
small table at the table level, you can improve performance by reducing the work of
retrying deadlocked transactions. On larger tables, the price of table level locking is
higher, since the naturally higher concurrency of the large table is sacrificed to
serialization.
Page level locking improves concurrency by allowing multiple users to access different
pages in the same table concurrently. Row level locking maximizes concurrency by
allowing multiple users to access different rows in the same table at the same time, even
on the same page.
Because ALLBASE/SQL uses a buffer system in accessing data from database files, keep