HP 3000 Manuals HP 3000 Manuals
Controlling Locking
ALLBASE/SQL supports a variety of lock granularities, lock types, and
isolation levels to enable a transaction to lock only what is necessary
to keep other transactions from interfering with its work. For a
complete general discussion of locking and concurrency issues, refer to
the chapter "Concurrency Control through Locks and Isolation Levels" in
the ALLBASE/SQL Reference Manual. This section concentrates primarily on
locks and performance.
Locking degrades performance in two ways:
* A transaction must wait if the object it needs is already locked
in an incompatible mode by some other transaction.
* Deadlocks sometimes occur.
An application that is well tuned for performance has a low rate of
deadlock and a high rate of concurrency. In reality, however, a tradeoff
is usually necessary. A low deadlock rate is often achieved by limiting
the number of users attempting to obtain a lock by locking at a coarse
level of granularity (for example, the table level rather than the page
level). This strategy tends to increase the wait time for the lock
(thereby reducing concurrency). Conversely, a short wait time for locks
is usually achieved by locking at a finer level of granularity (for
example, the page level rather than the table level). This strategy
tends to increase the number of deadlocks.
When any ALLBASE/SQL statement is executed, page locks are acquired on
one or more system tables (that is, tables owned by the special user
HPRDBSS). Page locks and row locks are also acquired on certain
ALLBASE/SQL internal tables (that is, tables owned by DBCore). You
cannot directly change the locking behavior of these tables.
When ALLBASE/SQL statements that reference a user table are executed,
row, page, and table locks of different kinds may be obtained on the
table. You can help control what kind of locks are obtained and how long
they are held by one of the following strategies:
* You can modify the implicit locking structure of the table by
changing the table type with the ALTER TABLE statement.
* You can use the LOCK TABLE statement to override the implicit lock
mode for a given transaction.
* Instead of RR, you can use the CS (Cursor Stability), RC (Read
Committed), and RU (Read Uncommitted) isolation levels to reduce
the duration of certain locks in a transaction.
These strategies can help promote improved concurrency and reduced
deadlocks.