Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

DB2

Posted Oct 18, 2010

Logical and Physical Locking in IBM DB2 for z/OS

By Lockwood Lyon

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



DB2 Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 



















Thanks for your registration, follow us on our social networks to keep up-to-date