5.15 How does granularity affect locking (continued)?
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
Page level locking uses more run-time control block space than table level
locking, since each page must be locked. Row level locking uses even more
space than page level locking, since each row must be locked. This can
require a considerable number of lock control blocks (and pages of shared
memory). The following chart indicates the maximum number of locks that
could be obtained on a table by using table, page, and row level locking (the
maximum number is possible when all of the rows in the table are locked):
Locking Level | Maximum number of locks
--------------|------------------------
Table level | 1
Page level | n + (1)
Row level | m + (n + 1)
n equals the number of pages in the table.
m equals the number of rows in the table.
Table level locking requires 1 lock. Page level locking requires up to n
page level locks, plus 1 intention lock at the table level (intention locks
will be explained later in this module). Row level locking requires up to m
row level locks, and up to (n + 1) intention locks at the page and table
level.
Because row level locking on a large table may consume a tremendous number of
run-time control block pages, the use of the PUBLICROW table type on large
tables is discouraged. Large tables for which maximum concurrency is desired
should generally be defined as PUBLIC. The PUBLICROW table type should
generally be reserved for use on small tables.
Printable version
