Db2 for z/OS Users

Application granularity for NUMLKUS and NUMLKTS

By Lifei Zheng posted Jul 23, 2021 12:01 AM

  

Recap on NUMLKUS and NUMLKTS 


To enforce good application standards and avoid bad designed applications taking too much space in the lock structure, Db2 provides two system level parameters to control the locking limits.  

NUMLKUS - The NUMLKUS subsystem parameter specifies the maximum number of page, row, or LOB locks that a single application can hold concurrently for all table spaces. A value of 0 indicates that there is no limit to the number of data and row locks that a program can acquire. These values are constraints for a single application. Each concurrent application can hold the maximum number of locks specified here.

To avoid exhausting the IRLM's storage for locks, follow these guidelines:

  • Do not specify 0 or a very large value unless it is specifically required to run an application.
  • Consider the design of your applications. Long-running applications, particularly those that perform row-level locking, have few or infrequent commit points, or use repeatable-read isolation may use substantial amounts of lock storage. You should perform frequent commits to release locks.

If NUMLKUS is exceeded,  the next lock request will result in SQLCODE -904 with a 00C90096 reason code,   and message DSNT500I or DSNT501I are issued.



NUMLKTS - The NUMLKTS subsystem parameter specifies the default maximum number of page, row, or LOB locks that an application can hold simultaneously in a table or table space. This value becomes the default value (SYSTEM) for the LOCKMAX clause of the SQL statements CREATE TABLESPACE and ALTER TABLESPACE. A value of 0 indicates that there is no limit to the number of page and row locks that a program can acquire.
Recommendation: Do not set the value to 0, because it can cause the IRLM to experience storage shortages.

If a program requests more locks than the number specified in LOCKMAX on the object, lock escalation happens. Db2 attempts to promote the high level intent locks, either IS or IX, to S or X, respectively. If this succeeds, then the low level locks are all released and no more low level locks are requested. The purpose of lock escalation is to reduce the impact of low level locks, both in terms of CPU and storage, at the possible expense of concurrency.




What's new in V12 function level 507(V12R1M507)


Due to variety of applications and users accessing same objects,  it's difficult to determine the value for object level LOCKMAX and subsystem level NUMLKTS&NUMLKUS for all applications.  Before V12R1M507,  existing solution doesn't provide the application level granularity.   

If those values are set too high,  poor designed applications are not caught and tolerated. This could lead to storage shortage of the lock structure. 
If those values are set too low, some batch program might have to do excessive commits to avoid failure and unnecessary lock escalation. 

Now with V12R1M507,  two new built-in global variables are introduced to support application granularity for locking limits, which were previously specific only by the NUMLKUS and NUMLKTS subsystem parameters: 

  • SYSIBMADM.MAX_LOCKS_PER_TABLESPACE contains an integer value for the maximum number of page, row, or LOB locks that the application can hold simultaneously in a table space. If the application exceeds the maximum number of locks in a single table space, lock escalation occurs. MAX_LOCKS_PER_TABLESPACE corresponds to the existing NUMLKTS subsystem parameter.

  • SYSIBMADM.MAX_LOCKS_PER_USER contains an integer value integer value that specifies the maximum number of page, row, or LOB locks that a single application can concurrently hold for all table spaces. The limit applies to all table spaces that are defined with the LOCKSIZE PAGE, LOCKSIZE ROW, or LOCKSIZE ANY options. MAX_LOCKS_PER_USER corresponds to the existing NUMLKUS subsystem parameter.

Now users have the capability to allow higher or lower thresholds than the object level/subsystem level parameters for certain applications while other applications can still be constrained by existing parameters.  APPLCOMPAT V12R1M507 or above needs to be in effect. 



Sample scenarios. 


Scenario 1

NUMLKTS = 2000
NUMLKUS = 10000
TABLESPACE TS1 has LOCKMAX SYSTEM 
TABLESPACE TS2 has LOCKMAX 8000
TABLESPACE TS3 has LOCKMAX SYSTEM 
Application A needs to insert 1500 records into TS1 ,  7500 records into TS2 and 1500 records into TS3 in one commit scope.   

As constrained by NUMLKUS ,  Application A would fail with 00C900096. 
------
DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00C90096, TYPE OF RESOURCE
00000304, AND RESOURCE NAME TD1 .TS3 .X'00000050'.X'CB'
DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRINS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -110 13172746 0 13230791 -639430653 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF92' X'00C9000A' X'00000000' X'00C9E2C7' X'D9E31003' X'00000000' SQL DIAGNOSTIC
INFORMATION
------ 

Before V12R1M507, users have below options. 
1.Add more commit points to the application if appropriate and application logic allows. 
2.Increase subsystem parameter NUMLKUS, which raise the maximum lock limitation for all the other applications as well.    

After V12R1M507, user can set the new built-in global variable SYSIBMADM.MAX_LOCKS_PER_USER to 11000 in application A to allow higher lock limitation for this specific application only while all the other applications would still be constrained by the original limitation.   Application A would complete successfully afterwards.  


Scenario 2

NUMLKTS = 2000
NUMLKUS = 10000
TABLESPACE TS1 has LOCKMAX SYSTEM 
Application A needs to insert 5000 records into TS1 in one commit scope and trigger lock escalation. 

1. If there're other applications holding locks on this object and those locks are not released in time,   lock escalation could time out  and application A would fail.   
-----
DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR TIMEOUT. REASON 00C9008E,
TYPE OF RESOURCE 00000210, AND RESOURCE NAME TD1 .TS1 .00000001
DSNT418I SQLSTATE = 40001 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRINS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -190 13172746 13172878 13226962 -975040510 536870912 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF42' X'00C9000A' X'00C9008E' X'00C9D3D2' X'C5E21002' X'20000000' SQL DIAGNOSTIC
INFORMATION
----- 


2.  If lock escalation is successful,  DSNI031I message would be issued to indicate that high level lock has been escalated.  
-----
DSNI031I -DB2A DSNILKES - LOCK ESCALATION HAS 738
OCCURRED FOR
RESOURCE NAME = TD1.TS1
LOCK STATE = X
PLAN NAME : PACKAGE NAME = DSNTEP3 : DSNTEP3
COLLECTION-ID = DSNTEP3
STATEMENT NUMBER = 00001685
CORRELATION-ID = BATCH1
CONNECTION-ID = BATCH
LUW-ID = USIBMSY.SYEC1DB2.DA0D230BC26D
THREAD-INFO = ADMF001:BATCH:ADMF001:EXETES2:DYNAMIC:27:*:<BATCH1
PARTITION-INFO = PART 1 AND 0 OTHER PARTS ESCALATED
-----

Concurrency is compromised after lock escalation.   After lock escalation and before the S/X high level lock is released ,  the other applications could time out when they need to access the same object even though the records they want to access are different from application A.  
For example,  application B could time out when it also tries to insert 1 record into TS1 after application A triggers lock escalation. 
-----
DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR TIMEOUT. REASON 00C9008E,
TYPE OF RESOURCE 00000210, AND RESOURCE NAME TD1 .TS1 .00000001
DSNT418I SQLSTATE = 40001 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRINS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -190 13172746 13172878 13226962 -991424511 536870912 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF42' X'00C9000A' X'00C9008E' X'00C9D3D2' X'C4E81001' X'20000000' SQL DIAGNOSTIC
INFORMATION
-----


Before V12R1M507,  in order to allow concurrency when the application is running,  users have below options. 
1.Add more commit points to the application if appropriate and application logic allows. 
2.Increase LOCKMAX of TS1, which raise the lock escalation limits for all the other applications as well.    

After V12R1M507, user can set the new built-in global variable SYSIBMADM.MAX_LOCKS_PER_TABLESPACE to 5000 in application A to allow higher lock escalation limits for this specific application only while all the other applications would still be constrained by the original limitation.   After that, application A is able to run concurrently with all the other applications without lock escalation.  


0 comments
293 views

Permalink