Details of Locking

To promote the greatest concurrency, ALLBASE/SQL supports a variety of granularities and lock types. Granularity is the size of the object locked. Lock type is the severity of locking provided. Compatibility refers to the ability of different transactions to hold locks at the same time on the same object.

Lock Granularities

The use of different granularities of locking promotes a high level of concurrency. There are three levels of granularity in ALLBASE/SQL:

Row (tuple) level
Page level
Table level

Although some system operations use row level locking internally, system operations acquire page locks by default. User-created tables can be locked at the row, page, or table level, depending on the table type. B-tree and constraint indexes are locked with weak locks at the page level for update operations and are not locked at all on reads. Table, page, and row level locking are illustrated in Title not available and Title not available Title not available portrays a query that accesses two pages of a table.

Figure 5-3 Page Versus Table Level Locking

With page level locking, pages containing data scanned for the query are locked. All other pages can be locked by other transactions. With table level locking, the same query locks the table as a whole, whether or not the individual pages are being used for a query. This means that when a table has an exclusive lock on it, no other transaction can obtain any locks on the table or any data page in it until the transaction holding the page lock terminates.

Title not available 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 in mind that the system can actually acquire several page or row locks, one at a time, before the data is exposed to the user. In effect, the user's transaction obtains and releases locks on sets of pages or rows at a time as it moves through a query result. This is because data from many pages and rows can be required to fill the 12K tuple buffer.

Types of Locks

Locks in ALLBASE/SQL can be classified into the following five types, listed from the lowest to the highest level of severity:

Intention Share (IS): Indicates an intention to read data at a lower level of granularity. An IS lock on a PUBLIC table indicates an intention to read a page. An IS lock on a PUBLICROW table together with an IS lock on a page indicates an intention to read a row on that page. When a need to read data at a lower level is established, ALLBASE/SQL internally requests an IS lock at the higher level. For example, after an IS table lock has been granted on a PUBLIC table, requests are made for S locks on particular pages. In the case of a PUBLICROW table, after IS locks have been granted on both table and page, requests are made for S locks on particular rows.
Intention Exclusive (IX): Indicates an intention to update or modify data at a lower level of granularity. An IX lock on a PUBLIC table indicates an intention to modify data on a page. An IX lock on a PUBLICROW table together with an IX lock on a page indicates an intention to modify a row on that page. When a need to write data at a lower level is established, ALLBASE/SQL internally requests an IX lock at the higher level. For example, after an IX table lock has been granted on a PUBLIC table, requests are made for X locks on particular pages. In the case of a PUBLICROW table, after IX locks have been granted on both table and page, requests are made for X locks on particular rows.
Share (S): Permits reading by other transactions.
Share and Intention Exclusive (SIX): Indicates a share lock at the current level and an intention to update or modify data at a lower level of granularity. SIX locks are placed on both tables, pages, and rows. When the need to write data at the page or row level is established, and there is also a need to be able to read every page in the table without its being modified by any other transaction, then ALLBASE/SQL internally requests a SIX lock on the table. After an SIX lock has been granted on a PUBLIC table, no additional locks are acquired when a page is read, but an X page lock is acquired when a page is written. After an SIX lock has been granted on a PUBLICROW table, no additional locks are acquired when a row is read, but an IX page lock and an X row lock are acquired when a row is written.
Exclusive (X): Prevents any access by other users. An exclusive lock is required whenever data is inserted, deleted, or updated. Because no other user can read this data before the transaction completes, the integrity of the database is not endangered if the changes have to be rolled back, either at the user's request or on recovery after a system failure.

Some of these locks are intention locks. Intention locks are obtained at a higher level of granularity whenever a lock is obtained at a lower level. For example, when you obtain a share lock (S) on a page, the table is normally locked with an intention share lock (IS). This is done so that other transactions can quickly tell that a table is being read by someone without the need to determine which specific pages are being read. Suppose another transaction wishes to lock the table in exclusive mode. The IS lock on the table would prevent the other transaction from locking the table in exclusive mode. Without the use of higher granularity locks, ALLBASE/SQL would have to search all page or row locks to determine whether the exclusive lock request could be granted.

Title not available shows the use of an intention lock at the table level and share locks on the page level. The example assumes that an index is being used for data access.

Figure 5-5 Locks at Different Granularities

Lock Compatibility

Table 5-1 “Lock Compatibility Matrix” shows the compatibility of different lock types. A Y (yes) at the intersection of a row and column in the table indicates that two locks are compatible at the same level of granularity; a blank space indicates that they are not compatible.

Table 5-1 Lock Compatibility Matrix

	IS	IX	S	SIX
IS	Y	Y	Y	Y
IX	Y	Y
S	Y		Y
SIX	Y
X

When two lock requests are compatible, both transactions are allowed to access the table, page, or row concurrently, and the lock on this data object is promoted to or left at the lock mode of higher severity. For example, if transaction 2 wishes to update a page that is already being read by transaction 1, transaction 2 requests an IX lock on the table and an X lock on the page. Transaction 1 has an IS lock on the table, which is compatible with the requested IX, so the lock on the table is promoted to IX. Then, transaction 2 obtains the X lock on the page it needs to update only if transaction 1 is not already reading that same page. Note that S and X locks on the same page are not compatible.

When locks are not compatible, the second access request must wait until the lock acquired by the first access request is released.

Weak Locks

Intention exclusive locks are called weak locks when there is no other lock at a finer level of granularity on the object being locked. This is the case for index pages, which are locked IX when concurrent transactions are updating different rows on the same page. Weak locks, also known as sublocks or concurrent locks, are used to prevent the deletion of an index page by another concurrent transaction. ALLBASE/SQL uses strong locks (exclusive locks) on index pages only for splitting, deleting, or compressing index pages.

Details of Locking

Technical documentation

» Table of Contents

» Index

Lock Granularities

Types of Locks

Lock Compatibility

Weak Locks