Logical and Physical Locking in IBM DB2 for z/OS

October 18, 2010

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






Data sharing and row-level locking




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.


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

The Network for Technology Professionals


About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers