In the first three installments of this series on DB2 locking we have looked ata broad overview of locking (part 1), table and table space locks (part 2) and the difference between locks and latches (part 3). Today we will move ahead and discuss page and row locking.
Page Locking
The types of page locks that
DB2 can take are outlined in Table 1. S-locks allow data to be read concurrently but not modified. With
an X-lock, data on a page can be
modified (with INSERT, UPDATE, or DELETE), but concurrent access is not allowed. U-locks enable X-locks to be queued, whereas S-locks exist on data that must
be modified.
Table 1. Page Locks
As with table space locks,
concurrent page locks can be acquired but only with compatible page locks. The
compatibility matrix for page locks is shown in Table 2.
Table 2. Page Lock Compatibility Matrix
When are these page locks
taken? Page locks can be acquired only under the following conditions:
- The DDL for the object requesting a lock specifies LOCKSIZE PAGE or LOCKSIZE ANY.
- If LOCKSIZE ANY was specified, the NUMLKTS threshold or the table space LOCKMAX specification must not have been exceeded. You learn more about these topics later in this section.
If all these factors are met,
page locking progresses as outlined in Table 3. The type of processing in
the left column causes the indicated page lock to be acquired for the scope of
pages identified in the right column. DB2 holds each page lock until it is
released as specified in the ISOLATION level of the plan requesting the particular lock. Page locks can be promoted from one type of lock to another based
on the type of processing that is occurring. A program can FETCH a row using a cursor with the FOR UPDATE OF clause, causing a U-lock to be
acquired on that row’s page. Later, the program can modify that row, causing
the U-lock to be promoted to an X-lock.
Table 3. How Page Locks Are Acquired
Row
Locks
The smallest piece of DB2 data
that you can lock is the individual row. The types of row locks that DB2 can
take are similar to the types of page locks that it can take. Refer back to Table 1 and simply replace Page with Row. So row locks act like page locks, only on a smaller granularity (that is, on rows instead of pages).
S-locks allow data to be read
concurrently but not modified. With an X-lock, you can modify data in that row (using INSERT, UPDATE, MERGE, or DELETE), but concurrent access is
not allowed. U-locks enable X-locks to be queued, whereas S-locks exist on data that must be modified.
Once again, concurrent row
locks can be acquired but only with compatible row locks. Table 2 works the same way for row locks as it does for page locks.
When are these row locks
taken? Row locks can be acquired when the DDL for the object requesting a lock
specifies LOCKSIZE ROW. (Although it is theoretically possible for LOCKSIZE ANY to choose row locks, in
practice I have yet to see this happen.) Again, we can use an earlier Table (Table 3) replacing the word page with the word row to see how row locking progresses. The type of processing in the left column causes the indicated row
lock to be acquired for the scope of rows identified in the right column. A row
lock is held until it is released as specified by the ISOLATION level of the plan requesting
the particular lock.
Row locks can be promoted from one type of lock to another based
on the type of processing that is occurring. A program can FETCH a row using a cursor with the FOR UPDATE OF clause, causing a U-lock to be acquired on that
row. Later, the program can modify that row, causing the U-lock to be promoted to an X-lock.
Page
Locks Versus Row Locks
The answer to the question of
whether to use page locks or row locks is, of course, “It depends!” The nature
of your specific data and applications determine whether page or row locks are
most applicable.
The resources required to
acquire, maintain, and release a row lock are just about the same as the
resources required for a page lock. Therefore, the number of rows per page must
be factored into the row-versus-page locking decision. The more rows per page,
the more resources row locking will consume. For example, a table space with a
single table that houses 25 rows per page can consume as much as 25 times more
resources for locking if row locks are chosen over page locks. Of course, this
estimate is very rough, and other factors (such as lock avoidance) can reduce
the number of locks acquired, and thereby reduce the overhead associated with
row locking. However, locking a row-at-a-time instead of a page-at-a-time can
reduce contention. Row locking almost always consumes more resources than page
locking. Likewise, if two applications running concurrently access the same
data in different orders, row locking might actually decrease concurrent data
access.
You must therefore ask these
questions:
- What is the nature of the applications that access the objects in question? Of course, the answer to this question differs not only from organization to organization, but also from application to application within the same organization.
- Which is more important, reducing the resources required to execute an application or increasing data availability? The answer to this question will depend upon the priorities set by your organization and any application teams accessing the data.
As a general rule of thumb,
favor specifying LOCKSIZE PAGE, as page locking is generally the most practical locking strategy
for most applications. If you’re experiencing severe contention problems on a
table space that is currently using LOCKSIZE
PAGE, consider changing to LOCKSIZE ROW and gauging the impact on
performance, resource consumption, and concurrent data access. Alternatively,
you also might choose to specify LOCKSIZE
ANY and let DB2 choose the type of locking to be
performed.
Note
Note: A possible alternative to row locking is to specify MAXROWS 1 for the table space and use LOCKSIZE PAGE (or LOCKSIZE ANY), instead of LOCKSIZE ROW.
No comments:
Post a Comment