HP 3000 Manuals HP 3000 Manuals
Using Row Level Locking
Row level locking provides the finest level of lock granularity, where
only the row that is read or updated is locked. By locking the row
alone, ALLBASE/SQL allows other concurrent transactions to access other
rows on the same page. This is in contrast with page level locking,
where an entire page containing the row is locked, with the result that
concurrent transactions accessing the same page must wait until the lock
is released.
You enable row level locking for a specific table by defining the table
to be of type PUBLICROW in the CREATE TABLE statement. For PUBLICROW
tables, ALLBASE/SQL uses row rather than page level locking. A table may
also be changed to PUBLICROW by using the ALTER TABLE statement, as in
the following example:
ALTER TABLE PurchDB.Parts SET TYPE = PUBLICROW
Benefits of Row Level Locking
In general, row level locking can be used to reduce or eliminate the
frequency and length of lock waits on hot spots. Hot spots are data
storage areas, such as pages, that are accessed frequently by concurrent
transactions. By locking at a finer level of granularity, the overall
throughput of the system can be increased by reducing lock waits.
Small tables are good candidates for row level locking. This is
especially true if the row size is small, and many rows fit on the same
page, and if the table is frequently accessed by concurrent transactions.
Large tables with hot spots may also be good candidates for row level
locking. An example is a history table in which small portions of the
table, such as the most recent history, are read and updated frequently
by concurrent transactions, even though most of rest of the table remains
untouched. In such cases, use row level locking with caution, especially
if the table may be used by some transactions in serial scans.
Hot spots that develop at either end of an index are not alleviated by
row level locking. This situation can arise if there is a series of
inserts or deletes of keys at either end by concurrent transactions. For
example, problems can occur on inserting successive rows containing
CURRENT_DATETIME into a table with an index on the DATETIME column. Hot
spots arise because key inserts and deletes lock the data pointed to by
the neighbor index entry to enforce repeatable read and constraints.
Shared Memory Considerations
A locked table, page, or row is represented in shared memory by means of
a lock object. Lock objects are stored in lock control blocks in shared
memory. The greater the number of lock objects, the more control blocks
are required in shared memory. If a page is locked, then all rows on the
page are implicitly locked. In this case, only one lock object is needed
to represent the lock in memory. However, if rows are locked in a page,
then a separate lock object is needed for every row on the page.
Use row level locking carefully. In allocating a row level lock,
ALLBASE/SQL will place an intention lock on the table, another intention
lock on the page, and the requested lock on the row. Thus row locking
uses more CPU than table or page level locking and is less efficient.
Row locking also generally uses more runtime control block pages than
page and table locking. For these reasons, you should avoid using row
level locking if the entire table will be scanned using an index.
As an example, consider a table having 100 pages containing 100 rows
each. A scan of the whole table will acquire 100*100 row locks for row
locking, in addition to 100 intention locks on the pages and an intention
lock on the table, for a total of 10,101 lock objects. With page level
locking, the total is only 101 lock objects.
A table that is a good candidate for row level locking is one in which
the following are true:
* Rows are small. If each row takes up a page, then page level
locking has about the same effect as row level locking, but page
level locking is more efficient.
* There will be concurrent write operations or concurrent read and
write operations. If there will only be concurrent read
operations, then the table should be PUBLICREAD. If only one
transaction will access the table at a time, then the table should
be PRIVATE or PUBLICREAD.
* A relatively small number of rows will be locked. This is true in
the following cases:
* The number of rows in the table is small.
* The number of rows in the table is large, but all
transactions are trying to access the same small number of
rows.
* The overall table size is small compared to the number of
concurrent transactions that are expected to access the
table. High concurrency becomes more critical when more
transactions are trying to access the same data.
If a large number of rows will be locked, then more shared memory
and CPU will be required to manipulate the lock objects than with
page level locking.
As an example, if 240 concurrent transactions randomly access a table
which contains 20 pages and if each page contains 200 tuples, then the
table is a good candidate for row level locking. If the table contained
only one tuple per page, or if only read operations were expected, or if
the table were 20,000 pages in size, then the table would not be a good
candidate for row level locking.
For more information about row level locking, refer to the document Row
Level Locking: Technical Addendum for ALLBASE/SQL Release F.
Page Locking on PUBLICROW Tables
Sometimes during updates, ALLBASE/SQL needs to acquire locks on pages
instead of rows even if the table is a PUBLICROW table. The following
are examples:
* When a new page must be allocated before inserting a new row.
* When a page must be deallocated after deleting the last tuple on a
page.
* When a page must be compressed to reclaim freed space.
Page locks are also acquired when the transaction is performing a serial
scan on the table at the Repeatable Read (RR) isolation level.