Logical and Physical Locking in IBM DB2 for z/OS


Many DBAs and analysts are confused by the variety of serialization
mechanisms used by IBM DB2 for z/OS to ensure data consistency and maintain
concurrency control. Understanding logical and physical locking can help you
better understand and improve application and system performance.

The word lock conjures up visions of DB2 holding tightly to a
resource on behalf of an application. Regrettably, the term is often misused
and misunderstood. The following discussion is applicable to most database
management systems, with particular emphasis on IBM’s flagship DBMS, DB2 for
z/OS.

Early versions of DB2 featured transactional locks on data as a way of
managing concurrent access to data. Multiple applications could read data
(tables, indexes) concurrently by acquiring share locks, while an
updating application (inserting, updating, or deleting rows) had to acquire an exclusive
lock.

These locks were initially of two sizes, or granularities: locks on
tables (or tablespaces), and locks on pages of 4K bytes. While later versions
of DB2 allowed for larger page sizes (8K, 32K, etc.), to understand current
lock types we concentrate on the basic 4K page.

It isn’t necessary to understand internal page formats; it’s enough to
understand that pages contain two categories of information: user data (rows),
and control information. One example of page control information is a linked
list of available space on a page; that is, space not occupied by rows. So,
when a new row is inserted on a page two changes take place: user data (the
row) is inserted; and page internal control information is changed.

Data concurrency and page consistency

In general, page changes due to insert, update, or delete of rows require
DB2 to control and manage two different things:

  • Concurrency. Access by two or more applications (using
    SQL) to the user data (the rows).
  • Consistency. Changes to page control information as a
    result of data changes.

DB2 maintains user data concurrency by using page locks managed by the
Intersystem Resource Lock Manager (IRLM). These locks are taken on behalf of
application transactions requesting access to data via SQL, so are sometimes
called transaction locks.

Page consistency is managed by internal subroutines called latches. A latch
for a page can be pictured as a status flag. If the flag is on (the
page is latched), then the control information on the page is currently being
changed. This process permits DB2 to efficiently regulate page changes by
forcing multiple changers (such as applications executing deletes) to make their
page changes serially.

Later versions of DB2 added two complicating factors to this mix. Luckily,
the basics of using locks for user data concurrency and latches for page
control consistency have not changed.

Data sharing and row-level locking

First, most large enterprises implement a multi-member DB2 in a
configuration called data sharing. This allowed multiple instances of DB2 code
to execute on separate hardware while sharing common data. If applications on
two of these systems want to access the same data, how is this controlled?

Second, DB2 now allows a greater lock granularity with the implementation of
row-level locking. In terms of user data access, this allows two applications
to access (and perhaps change) separate rows on the same page without
interfering with each other. How is this managed across multiple DB2s?

Locking is still used for user data concurrency. Locks for each DB2 member
are managed by their own IRLMs. The IRLMs communicate with each other using a
high-speed feature called z/OS cross-system extended services, or XES. The
IRLMs then can, as a group, ensure that user data is locked appropriately to
manage concurrent access. Since transaction locks are not used to serialize
changes to physical page control information, they are sometimes called logical
locks
in this environment. The word logical does not mean that
user data is no longer physically locked; rather, it refers to the way that DB2
stores row data on pages: sometimes encoded, compressed or encrypted.

Multi-member page latching

Latches are still used to control the consistency of pages. This is even
more important when using row-level locking, because locks no longer prevent
applications from making simultaneous page control data changes. In the data
sharing environment, DB2 manages page consistency using a global form of a
latch called a Page Physical Lock, or Page P-Lock.

Despite the name, a Page P-Lock is a latch taken on a page to control page
consistency. In data sharing, when a DB2 member determines that a page latch is
necessary (say, during a row insert), it sends a Page P-Lock request to a data
store called the Lock Structure. This data store exists in a centralized area
called the Coupling Facility, which is accessible by all DB2 members. This,
then, performs as a central latch manager, receiving and storing Page P-Locks
from multiple members.

There is one more issue to address. In data sharing, multiple DB2 members
(or instances) share access to tables. However, each DB2 member has its own
area of memory buffers allocated to store table pages read from DASD. How are
simultaneous data changes on separate members handled?

DB2 uses a third category of serialization mechanism to handle this
situation called a Page-Set or Partition Physical Lock. These pageset locks are
not used by the IRLM, and the use of the term lock is somewhat unfortunate.

A pageset usually corresponds to a physical dataset on mainframe DASD. These
include datasets corresponding to segmented tablespaces, each partition of a
partitioned tablespace, non-partitioned indexes, and each partition of a
partitioned index.

A DB2 member that needs to access DB2 data requests the operating system to open
the appropriate physical datasets. For each dataset, the DB2 member will
request a Pageset P-Lock from the coupling facility, and holds the lock until
the dataset is physically closed on that member.

Thus, the coupling facility maintains a complete list of all pagesets of interest
to all DB2 members. As a DB2 member begins to make updates to pages, this is
communicated to the coupling facility as another Pageset P-Lock. These locks
can be interrogated by other DB2 members to find out if data has been changed.

For example, DB2 member MBRA executes a Select on behalf of a user
transaction. MBRA opens the datasets corresponding to the table (and indexes,
if applicable), and sends a request to the coupling facility to acquire a
shared Pageset P-Lock. Let’s assume it is granted, MBRA completes the Select,
and the transaction commits.

Next, DB2 member MBRB executes an Insert on the same table. MBRB opens the
datasets corresponding to the table (and indexes, if applicable), and sends a
request to the coupling facility to acquire an exclusive Pageset P-Lock. Let’s
assume it is granted, MBRB completes the Insert, and the transaction commits.

If MBRA now attempts to execute a Select on the table, it queries the
coupling facility and determines that the table has changed. This is known
because of the Pageset P-Lock now existing that was created by MBRB. MBRA knows
that data has changed, and also knows that the data it has currently stored in memory
for that table (because of the prior Select) may be invalid. It must now
re-acquire the changed data pages.

Summary

These descriptions and examples are somewhat oversimplified. Still, they
allow the DBA and analyst to understand the difference between logical (or
transactional) locks and physical locks. As always, more information is
available in the appropriate DB2 manuals.

For an extended discussion of locking algorithms and serialization
mechanisms, see "ARIES Family of Locking and Recovery Algorithms" referenced
below,

C. Mohan – ARIES Family of
Locking and Recovery Algorithms
– Dec 01, 1999

IBM – Performance
Monitoring and Tuning Guide
– Mar 01, 2010

IBM – Data
Sharing: Planning and Administration
– Oct 01, 2009

»


See All Articles by Columnist

Lockwood Lyon

Lockwood Lyon
Lockwood Lyon
Lockwood Lyon is a systems and database performance specialist. He has more than 20 years of experience in IT as a database administrator, systems analyst, manager, and consultant. Most recently, he has spent time on DB2 subsystem installation and performance tuning. He is also the author of The MIS Manager's Guide to Performance Appraisal (McGraw-Hill, 1993).

Latest Articles