Consider Application-Level Lock Control in Db2 13 for z/OS

It has been close to a year since Db2 13 for z/OS has been generally available. It was announced in April 2022 and delivered for GA on May 31, 2022.

As you think about migrating from Db2 12 to Db2 13, it is inevitable that you will consider the new functionality and capabilities that comes with the new version. I've discussed the AI functionality of SQL Data Insights delivered in Db2 13, but haven't really dug into some of the other interested new features.

Today, I want to briefly discuss application-level lock control. This new feature enables applications to take more control over Db2 locking. If you have applications that could benefit from different lock parameters than the system-wide settings used by Db2, then this new capability could be useful for at least some of your applications and tasks.

The first thing to note is that you must be at Function Level 500 before you can use application-level lock control. Using application-level lock control then requires setting a special register using the SET CURRENT LOCK TIMEOUT statement. This statement can be included in application programs to control the lock wait duration in seconds. The data type is INTEGER with a range of acceptable values from -1 to 32,767. Setting the CURRENT LOCK TIMEOUT to -1 indicates an indefinite wait, setting it to 0 indicates no waiting. 

Most DBAs reading that last sentence will shudder at the possible implications of waiting forever! Fortunately, there is another new DSNZPARM called SPREG_LOCKTIMEOUT_MAX that can limit the upper bound that an application can use for CURRENT LOCK TIMEOUT.

Nevertheless, in order to implement application-level lock control you will need to modify your application code. So, if you want to wait for locks for up to 50 seconds, you would issue

    SET CURRENT LOCK TIMEOUT = 50

Before the SQL that should wait for that duration. 

Of course, any applications using application-level lock control should be monitored for lock contention. This can be done using Db2 monitoring tools, such as Omegamon or Mainview. The trace record IFCID 437 can also be monitored to discover the specific applications and authorization IDs that use this special register.

Deadlock Resolution Control

Similar to controlling the lock timeout duration, Db2 13 also introduced the ability to manage deadlock resolution control at the application level. This is accomplished using a new system built-in global variable. Instead of just relying on the system setting to control deadlock detection, applications can choose to set the new global variable: SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY 

Valid values range from 0 and 255. The higher the value, the less likely that locks requested by the application will deadlock when the application is involved in a deadlock situation. Applications and users require the WRITE privilege on this global variable in order to be able to issue it successfully.

So, if you want to set this to the max, you would issue

    SET SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY = 200

And then issue the statement(s) you are concerned about deadlocking. 

Of course, using this global variable does not guarantee that the application won't experience a deadlock because there are other considerations involved that Db2 still must negotiate and consider. 

One final note, you can use Profile tables to set CURRENT LOCK TIMEOUT and SYSIBM.DEADLOCK_RESOLUTION_PRIORITY as this support has been added in Db2 13.

1.