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.
To monitor lock activity, run SQLMON and access the screens in
the Load and Lock subsystems.
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.
