Using Row Level Locking

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:

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.

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:

Page locks are also acquired when the transaction is performing a serial scan on the table at the Repeatable Read (RR) isolation level.