HPlogo ALLBASE/SQL Reference Manual > Chapter 5 Concurrency Control through Locks and Isolation Levels

Use of Locking by Transactions

MPE documents

Complete PDF
Table of Contents
Index

E0300 Edition 9 ♥
E0399 Edition 8
E0897 Edition 7

Transactions obtain locks to avoid the possible interference of one transaction with another. This is important when you use PUBLIC or PUBLICROW tables, which can be accessed by many concurrent users of a DBEnvironment. Within the framework of a transaction, the PUBLIC tables that contain the required data for the operation you are performing are locked to regulate access to the data they contain. In addition, individual pages in PUBLIC tables are locked as needed when they are read into the data buffer. In the case of PUBLICROW tables, individual rows are locked as needed before they are read into the tuple buffer. In some cases, the use of a table lock may make the use of individual locks on pages unnecessary. Locks are released on both tables and pages when the transaction that acquired them issues a COMMIT WORK or ROLLBACK WORK statement, or when other conditions are met (described further in the section on "Defining Isolation Levels").

Basics of Locking

The following are the two basic requirements of locking:

  • Read operations on data pages must acquire share locks before data can be retrieved.

  • Write operations on data pages must obtain exclusive locks before data is modified.

Lock types are described in more detail in a later section.

When a lock is obtained, the transaction ID (a number), the name of the object locked, and the type of lock acquired are stored in a lock list in shared memory. When a user needs a particular lock, a lock request is issued, and ALLBASE/SQL checks to see whether the object is already locked by some other transaction. If the lock request cannot be granted, the transaction waits until the other transaction releases the lock. If the request can be granted, the new lock is placed in the lock list. (Compatibility of locks is described in a later section.)

When one transaction is waiting for another transaction to release a lock, and the second transaction is also waiting for the first to release a lock, the transactions are said to be in deadlock. If a deadlock occurs, ALLBASE/SQL rolls back one transaction, and this allows the others to obtain the needed lock and continue.

When a transaction ends through a COMMIT WORK or ROLLBACK WORK statement, locks are released; that is, the entries are deleted from the lock list. If the transaction has obtained several different locks, they are all released in a group.

When a transaction ends through an abnormal termination, locks are released by the monitor.

Locks and Queries

During query processing on PUBLIC tables, the cursor is positioned on a row in the query result; by extension, the cursor also points to the underlying data buffer page from which the specific row was derived. Typically, the underlying page to which a cursor points is locked to restrict access to it by other transactions. When a page in the data buffer is locked, another transaction may only access that page in a compatible lock mode. For 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 reduction of throughput that it is obviously not desirable in most situations.

Another cost of locking is the use of shared memory resources. Each lock requires the use of some runtime control block space. The more locks used by a transaction, the more memory required for control blocks. This is especially important for PUBLICROW tables, which usually require more locks than PUBLIC tables.

To minimize the costs of locking on PUBLIC and PUBLICROW tables, you should design each transaction in such a way as to lock only as much data as necessary to keep out other transactions that might conflict with your transaction's work. The following sections explain the features of ALLBASE/SQL that you can use to accomplish this.



Understanding ALLBASE/SQL Data Access

Defining Isolation Levels between Transactions