Db2 for z/OS and its ecosystem

Db2 for z/OS and its ecosystem

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

Concurrency and locking controls roundup for Db2 for z/OS

By Paul McWilliams posted 04/24/20 07:36 PM

  
By Haakon Roberts and Paul McWilliams
IBM Distinguished Engineer Haakon Roberts recently hosted a series refresher education sessions on the subject of locking in Db2 for z/OS, for the Db2 development team at IBM Silicon Valley Lab and around the world.  
Part of his in-depth multi-session presentation was a summary of the various controls, which have been added to Db2 over the years, for controlling application locking behavior in Db2 for z/OS. We thought it worthwhile to share his summary of these options here. The following list is an overview of current Db2 for z/OS application bind options, subsystem parameters (zparms) and SQL clauses that give you control over the degree of concurrency and serialization for database applications and SQL statements:
  • ISOLATION bind option & SQL isolation-clause: defines the degree to which a given application is isolated from other applications. That is, if an application reads a particular piece of data, and it wants to re-read the data later in the same commit scope, can the application tolerate the data being changed by other applications, or does the application need to be able to reread the data and know that it has not changed since the first time that the application read it? That is, how stable must the result of the query be?  The following table summarizes the meaning of the four isolation options. Note that these options refer to query processing only - updated data will always be locked by Db2 for the duration of the unit of work. For more information, see Choosing an ISOLATION option.
Isolation level 
Meaning
Uncommitted read (UR)
Provides the greatest concurrency (the least locking) and the least stability. The application can read uncommitted data, and does so without acquiring any locks. An application the uses UR might read rows of data that another transaction has inserted or updated, but eventually rolls back.
  • Use only for applications that can tolerate reading uncommitted changes to the data. 
  • LOB and XML Locks are sometimes required.
Cursor stability (CS)
The application sees only committed data. A lock is usually acquired to ensure that each row of data has been committed, but when the next row is fetched, the previous row or page is unlocked, and that process continues for each qualifying row. In that way, the data row is stable only as long as a cursor is on it. When the application moves off the row or page, another transaction can update it.
Read stability (RS)
The application keeps locks on all qualified rows until commit. However, locks are not kept for unqualified rows, and another application might later come along and update any unqualified row so that  it now qualifies.
Repeatable read (RR)
Provides the least concurrency (the most locking) and the greatest stability.  Every data row that is read by the application is locked and held until commit, regardless of whether it qualified. No other application can update a non-qualifying row to make it subsequently qualify. 
  • IRMLRWT zparm: specifies the IRLM lock timeout interval, which is the  duration after which IRLM returns a request back to Db2, indicating that the request cannot be satisfied. The default is 30 seconds. If an application waits more than this time for a lock, IRLM returns the request back to Db2 with a timeout message, Db2 returns it to the application, and the application must then determine what to do next. For more information, see IRLM startup procedure options.
  • DEADLOCK option of START irlmproc: specifies the interval for how often IRLM checks for deadlocks and timeouts. The default value is 1 second, but you can set it as low as 100 milliseconds.  For more information, see IRLM startup procedure options.
  • LOCKSIZE clause of CREATE TABLESPACE and ALTER TABLESPACE:  specifies the size for locks held on a table or table space by application processes.  You can specify whether you want row locks, page locks, or just a gross lock on the entire table space. The default is ANY, which specifies that you start off getting page locks. If you alter the table space from any to row the table space must be stopped to complete the ALTER. For partitioned table spaces, locks are acquired on individual partitions rather than a single lock on the entire table space. For more information, see Specifying the size of locks for a table space.
  • EVALUNC zparm:  specifies that Db2 evaluates whether a row meets selection criteria without acquiring a lock to detect that the data is committed. That is, the evaluation is done on uncommitted data.
  • SKIPUNCI zparm: specifies that Db2 ignores uncommitted inserts for isolation CS or RS applications.  For example, if an isolation CS application is doing a read and another transaction is doing an insert that hasn't committed yet and won't for another five minutes, the application skips the uncommitted insert.  For more information, see Accessing currently committed data to avoid lock contention.
  • SKIP LOCKED DATA clause:  specifies that rows are skipped when incompatible locks that would block the progress of the statement are held on the rows by other transactions. These rows can belong to any accessed table that is specified in the statement. SKIP LOCKED DATA can be used only with isolation CS or RS and applies only to row level or page level locks.  With this option, a fetch doesn't have to wait for data that is locked for update, the locked data is skipped and not returned. For more information, see Improving concurrency for applications that tolerate incomplete results.
The following parameters and SQL statement clauses specify locking limits: 
  • NUMLKTS zparm: specifies the default maximum number of child locks that can be acquired for any single table space before lock escalation occurs.  A thread does not fail when it hits this limit, but instead of the thread continuing to acquire many individual row locks, lock escalation acquires a "gross" lock on the table space or set of partitions,  and releases individual page or row locks. For more information, see Specifying the default maximum number of locks per table space.


Haakon Roberts is an IBM Distinguished Engineer and Paul McWilliams is an Information Developer for Db2 for z/OS  for Db2 for z/OS development.
Sign in and subscribe to always get the latest news about Db2 for z/OS from the IBM lab: http://ibm.biz/db2znews-subscribe




#Db2forz/OS
#db2z/os
#Db2Znews
2 comments
53 views

Permalink

https://community.ibm.com/community/user/blogs/paul-mcwilliams1/2020/04/24/db2-zos-concurrency-locking-roundup

Comments

Suman Suhag
01/27/26 12:48 PM

Hey! Dear,

Core Synchronization,

Purpose: Protect buffer pool pages, EDM pool, RID pool
Types:
• Buffer latch → Page I/O synchronization
• EDM latch → Package cache access
• RID latch → Sequential fetch optimization
 
Monitoring: IFCID 002/003 (latch contention)

• Manages all Db2 locks (user + internal)
• Global lock structure (data sharing)
• Lock timeout: IRLMRWT parameter (default 30s)
• Deadlock detection cycle: ~1 second

IFCID 172 Deadlock Record Fields:
• QW0172HZ → Holder statement ID
• QW0172WZ → Waiter statement ID  
• QW0172WA → DB2 "worth" (log RBA count → rollback victim selection)
• QW0172FR → Lock resource type (T=Table, I=Index, P=Page)
 
Victim Selection:
1. Lower "worth" value → Selected for rollback
2. Tie → Lower DEADLOCK_PRIORITY wins
3. Application handles SQLCODE -911/-913

Critical Parameters:
• RRULOCK=YES → U locks (not S) for child table locks in RR/RS
• EVALUNC=YES → Skip uncommitted row evaluation locks
• SKIPUNCI=YES → Skip uncommitted insert locks (CS/RS)
• IDFORE=NO → Background index cleanup (reduces foreground locks)
• CACHEDYN=YES → Dynamic SQL statement cache

  1. UR for read-only reporting workloads
    2. CS for OLTP (balance concurrency/read consistency) 
    3. Explicit FOR UPDATE on modified rows only
    4. Consistent table access order (T1→T2→T3)
    5. Short transactions (<2s duration)
    6. Application retry logic for -911/-913

-- Lock contention by tablespace
SELECT TBSPACE, 
       SUM(LOCK_REQUESTS) AS REQ,
       SUM(LOCK_CONVERTS) AS CONV,
       SUM(LOCK_TIMEOUTS) AS TIMEOUTS
FROM SYSIBM.SYSSTATS_LOCK 
WHERE STATS_TIME > CURRENT TIMESTAMP - 1 DAY
GROUP BY TBSPACE
ORDER BY TIMEOUTS DESC;

Thanks,

SUMAN SUHAG

Martin Ålund
04/27/20 02:00 AM

I'd love to hear the whole presentation serie, either as webinars or as a master class at IDUG