ALLBASE/SQL Reference Manual (36216-90216)

Chapter 5 171
Concurrency Control through Locks and Isolation Levels
Use of Locking by Transactions
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