Large number of record locks held impacts system performance

Use one of the two methods below to determine which jobs(s) are holding a large number of record locks:

Method 2: Query  the collection services performance data: 
Query the collection services performance data to find the job(s) holding a large number of locks.  This method will work whether or not commitment control is being used. 

An application, that uses commitment control, can more easily hold many more record locks than an application that doesn't. For example, an application without commitment control will read a record, lock it for update, update the record, then read the next record. The record lock only lasts until the next record is read. Commitment control, on the other hand, provides the ability to hold many resources as a single transaction. For example, an application can read, lock, and update 1,000 rows and keep them locked until the application does a COMMIT or ROLLBACK operation. Also, keep in mind that commitment control can be used in both High Level Language programs as well as in SQL. For example, an RPG program could use commitment control and read a group of records, one at a time, perform an update on each, and choose not to issue a COMMIT or ROLLBACK to release the locks until some number of records have been processed. With SQL, a single statement running under commitment control can be the source of many records being locked. For example, UPDATE FILEA SET X=5 WHERE X=8 could update millions of rows. SQL will lock the records as the statement runs. Once the statement completes, the locks will continue to be held until a COMMIT or ROLLBACK operation is issued.

However, one primary example where large number of record locks can be held without the use of commitment control is the SQL MERGE statement.  An SQL MERGE statement will place an update lock on all records in the target table that will be updated or deleted. Even when running with a commit level of *NONE, these locks are obtained and held until the end of the MERGE statement processing. Once all rows affected by the MERGE have been processed, the rows locked in the target table for delete or update operations will be unlocked.  

A large number of locks in the seize/lock table can cause all jobs accessing the database to consume more CPU. A single job or XA transaction holding several hundred thousand record locks can be enough to cause performance issues. And, one or more transactions holding millions of record locks can cause a performance outage. 

The maximum number of records which can be locked to a commit transaction for a job is 500,000,000. The maximum can be set to a lower value via the COMMITMENT_CONTROL_LOCK_LIMIT QAQQINI setting.  

A commitment control transaction holding a large number of record locks can be associated with a job or an XA transaction. The locks may be record update locks due to pending changes or record read locks.

select
last_change_timestamp,
JOB_NAME,
SBS_NAME,
JOB_STATUS,
AJSTATUS,
CURRENT_VALUE,
MAXVAL,
Sizing_Name
From QSYS2/syslimits
Where limit_ID = '16200'
Order by last_change_timestamp desc

select
last_change_timestamp,
JOB_NAME,
SBS_NAME,
JOB_STATUS,
AJSTATUS,
CURRENT_VALUE,
MAXVAL,
Sizing_Name
From QSYS2.syslimits
Where limit_ID = '16200' and
job_name in (
SELECT JOB_NAME 
FROM TABLE(QSYS2.ACTIVE_JOB_INFO()))
Order by last_change_timestamp desc;

System health services provides a way to monitor for large commitment control transactions.  You can add a trigger program over the System Limits Table and monitor for the maximum number of rows locked to a unit of work.  An entry will be put out for every 10,000 rows that are locked.  Action can be taken based on the number of rows locked. Notification can be sent through email or a message to sent to QSYSOPR.  Find more information here:  https://www.ibm.com/docs/en/i/7.3?topic=services-system-health
 
Apply the PTF for APAR MA49150 and monitor for warnings that the lock table is nearing capacity.

Keyword: Seize/Lock table