ALLBASE/SQL Reference Manual (36216-90216)

172 Chapter5
Concurrency Control through Locks and Isolation Levels
Use of Locking by Transactions
example, if someone else is updating a row of user data on page A of a PUBLIC table, your
transaction must wait until the update is committed before reading rows from page A into
your tuple buffer.
During query processing on PUBLICROW tables, the underlying row to which a cursor points
is locked, and the page on which the row resides is also locked (with an intent lock,
explained in "Types of Locks", below). Other users can access the same row only in a
compatible lock mode, but they can access different rows on the same page in different lock
modes. For example, if someone else is updating a row of user data on page A, your
transaction must wait until the update is committed before it can read the same row.
However, you can read other rows from page A into your tuple buffer and update them.
Locks on System Catalog Pages
In addition to locks on user data, ALLBASE/SQL locks pages of data in the system catalog
for the duration of the transaction. Data pages in one or more system tables are locked
when any SQL statement is executed.
See the appendix, “Locks Held on the System Catalog By SQL Statements,” in the
ALLBASE/SQL Database Administration Guide for more information.
Locks on Index Pages
B-tree indexes on PRIVATE and PUBLICREAD user tables are never locked, because
concurrency control on the index is already achieved via the table level locks that are
always acquired on these tables. B-tree indexes on PUBLIC or PUBLICROW user tables are
not locked for read operations, but they are locked with intention exclusive (IX) page locks
for write operations. B-tree indexes on PUBLIC and PUBLICROW tables are locked with
exclusive (X) page locks only in the following cases:
• When an index row is inserted and the page must be compressed before the insertion.
Compression is an attempt to recover non-contiguous space that has become available
on an index page.
• When an insert is made and the page must be split into two new pages. Splitting occurs
when compression does not result in enough space for inserting the new index row. In
such a case, the data from the original page is moved to the two new pages, each of
which receives half of the key values from the original page. The new index key is
inserted on one of the new pages, and the original page is freed, that is, made available
for reuse. A total of three X locks are obtained during this operation: one on the original
page, and two on the newly allocated index pages.
• When a delete is made, and an index page becomes empty because the last key on the
page was deleted. In this case, ALLBASE/SQL frees the page, which requires an X page
lock.
Costs of Locking
The price paid for ensuring the integrity of the database through locking is a reduction in
throughput because of lock waits and deadlock and the CPU time used to obtain locks.
This price can be high. For example, one way to guarantee that two transactions do not
interfere with one another is to allow only one transaction access to a database table at a
time. This serialization of transactions avoids deadlocks, but it causes such a dramatic