HP 3000 Manuals HP 3000 Manuals
Use of Locking by Transactions
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.