Finding record locks using SQL

One of the new SQL Views introduced as part of IBM i 7.2 TR3 and 7.1 TR11 was one that allows us to check for record locks. Now I can execute a SQL query and return all of the jobs locking the file I inquired about. No longer do I need to use APIs or the CL command Display Member Record Locks, DSPRCDLCK.

The new View, RECORD_LOCK_INFO, is found in the library QSYS2. It returns one row of every lock in your IBM i. This is why I should be careful how I use this View. I am sure there are many thousands of record locks in any medium to large size applications at any one time, most of them are harmless as the record or row is locked, updated and released. Every once in a while something goes "bump" and a tries to lock a record/row that is already locked by another. An error message occurs and I have to work out what the lock is and how to release it, in a timely manner, before I get more record locks to the same record/row.

I am not going to list of the available columns, instead I am going to refer you to IBM's Knowledge Center for the full list here. The ones I care about are:

Column	Description
SYS_DNAME	Library name
SYS_TNAME	File/Table name
SYS_MNAME	Member name
RRN	Relative record number of the record/row
LOCK_STATE	Lock condition for the record/row READ – Lock for read, another job may read the record but cannot lock the record for update. UPDATE – Record is locked for update. Another job may read the record, but cannot lock it for update until the original lock is released. INTERNAL – Locked internally for read. For a short time the operating system holds an internal lock to access the record. Another job can read the record and may even have the record locked for update. If another job does hold the row locked for update the change of the record will not proceed until the internal lock is released.
LOCK_STATUS	Status of the lock HELD – Lock is held by this job WAITING – Job is waiting for the lock
LOCK_SCOPE	Scope of the lock JOB, THREAD, LOCK SPACE
JOB_NAME	Full job name

I can use these columns to build a SQL statement I can use to find what is locking my record:

SELECT SYS_DNAME,SYS_TNAME,SYS_MNAME,RRN,LOCK_STATE, LOCK_STATUS,LOCK_SCOPE,JOB_NAME FROM QSYS2.RECORD_LOCK_INFO WHERE SYS_DNAME = 'MYLIB' AND SYS_TNAME = 'TESTFILE'

I have used the WHERE clause to give the library and file names to reduce the number of returned results, and the system resources need to generate them.

If my file, TESTFILE, was locked by this program:

01 dcl-f TESTFILE usage(*update) ; 02 chain 3 TESTFILER ; 03 update TESTFILER ; 04 *inlr = *on ;

If I caught the program after line 2 had executed and before line 3, then my SQL statement would retrieve the following:

SYSTEM_TABLE_SCHEMA SYS_TNAME SYS_MNAME RRN LOCK_STATE MYLIB TESTFILE TESTFILE 3 UPDATE LOCK_STATUS LOCK_SCOPE JOB_NAME HELD JOB 321545/SIMON/QPADEV0001

The equivalent using the Display Member Record Locks command, DSPRCDLCK, gives me:

Display Member Record Locks System: MYIBM_I File . . . . . . : TESTFILE Member . . . . . : TESTFILE Library . . . : MYLIB Record Lock Number Job User Number Status Type 3 QPADEV0003 RPGPGM 321545 HELD UPDATE

As this is output from a display command I cannot retrieve it into a program. The output from my SQL select statement can easily be incorporated into to a program to send the operator or locking user a message that there is a record lock error.

I could cause a record locking in a RPG program with embedded SQL:

01 dcl-s Fld1 packed(3) ; 02 exec sql DECLARE C0 CURSOR FOR SELECT FLD1 FROM TESTFILE FOR UPDATE OF FLD1 ; 03 exec sql OPEN C0 ; 04 exec sql FETCH NEXT FROM C0 INTO :Fld1 ; 05 exec sql UPDATE TESTFILE SET FLD1 = 10 WHERE CURRENT OF C0 ; 06 exec sql CLOSE C0 ; 07 *inlr = *on ;

If I managed to catch this program between the FETCH, line 4, and the UPDATE, line 5, I would see:

SYSTEM_TABLE_SCHEMA SYS_TNAME SYS_MNAME RRN LOCK_STATE MYLIB TESTFILE TESTFILE 1 UPDATE LOCK_STATUS LOCK_SCOPE JOB_NAME HELD JOB 321545/SIMON/QPADEV0001

I am sure you can come with several programs that cause record locks like my two examples. When the record lock occurs you can use a SQL statement similar to mine to see what job is causing the record lock, and then decide what corrective action to take.

I do not have IBM i 7.2 TR3 loaded onto any of the IBM i servers I use at work. When it is I can see myself writing a tool to quickly retrieve the details of the jobs with record locks on a file to determine which job is locking the record that is causing a record lock error message.

 

You can learn more about the RECORD_LOCK_INFO View from the IBM website here.

 

This article was written for IBM i 7.2 TR3 and 7.1 TR11, and will not work with earlier releases or TRs.