5.14 How does granularity affect locking?
NOTE: the shading on this picture may not be accurate. Please click on the
picture to obtain a larger image, which is accurate.
Key points
- Granularity is the size of the object that is locked. ALLBASE/SQL locks
data at three levels of granularity: row (or tuple), page, and
table.
- The smallest (lowest, finest) granularity is at the row level. The
largest (highest, most coarse) granularity is at the table level.
- Generally, the smaller the lock granularity, the greater the number of users
that can simultaneously access data in the table, because a smaller portion of
the data is locked by each user.
- When any SQL statement is executed, page level locks are acquired on one
or more system catalog tables (that is, tables owned by the special user
HPRDBSS). In addition, row level locks are acquired on certain
ALLBASE/SQL internal tables (that is, tables owned by DBCore). You cannot
directly change the locking behavior of these tables.
- When SQL statements that reference a user table are executed, row,
page, or table locks of different kinds may be obtained on the table.
You can help control the granularity of locking by doing the following:
- You can modify the implicit locking structure of a table by changing the
table's type with the
ALTER TABLE statement. Four table types exist (these
will be explained in greater detail later in this module). The following
locks are acquired by default when data is accessed in each type of table:
- PRIVATE (default) - table locks
- PUBLICREAD - table locks
- PUBLIC - page locks
- PUBLICROW - row locks
- You can use the
LOCK TABLE statement to override the implicit locking
structure of a table for a given transaction. For example, if a transaction
will read every row in a PUBLICROW table, you can use the LOCK TABLE
statement to obtain a single table lock instead of many row locks. LOCK TABLE
will also be explained in greater detail later in this module.
Page last updated on November 29, 1995
|
Printable version
