Informix assert file lock analysis

Abstract

This article provides a script to analyse what database objects were locked when IBM Informix Dynamic Server (IDS) “asserts” (saves an evidence file) including what sessions were waiting on another, which is particularly useful to diagnose deadlocks.

Content

In an actual recent case, an overnight batch job was sometimes failing with the following SQL and ISAM error numbers:

In order to ascertain what other session was causing the problem, we set a trap for any deadlock with “onmode -I -143” as documented here.

The $ONCONFIG file contained “DUMPSHMEM 0” so that a shared memory dump would not be written, but only an assert file (AF) and stack trace (with file name extension “.rawstk”).

We then waited until the next night, when an alarm program email was received and this was written into the message log (names have been anonymised in italics throughout this article):

An AF contains a section listing locks produced with “onstat -k” (which can also be run directly) with the output format described here. That section is located and aggregated by the following shell script:

assert_locks.sh

Copy to Clipboard

# Sum locks in an Informix assert file or "onstat -k" output
# Doug Lawry, August 2021

PROG=$(basename $0 .sh)

awk '
        /^Locks$/ {
                state = 1 ; next
        }
        state < 1 {
                next
        }
        /^address/ {
                state = 2 ; next
        }
        state < 2 {
                next
        }
        /^ / {
                state = 0 ; next
        }
        {
                sub("HDR\\+", "")

if ($5 ~ "I") # intent lock
                        next

if ($7 > 0)
                        type = "R" # row lock
                else
                        type = "T" # table lock

if ($8 ~ "K")
                        type = "K" # index key lock

pn = strtonum("0x" $6)

printf("%s,%s,%s%s,%s\n", $3, $2, type, $5, pn)
        }
' "$@" |
sort |
uniq -c |
awk '
        {
                printf("%s,%s\n", $2, $1)
        }
' > $PROG.1.csv

(dbaccess sysmaster 2>&1 | egrep .) <<!

CREATE TEMP TABLE temp_locks
        (
                owner   VARCHAR(10),
                waiter  VARCHAR(10),
                type    VARCHAR(10),
                partnum INTEGER,
                locks   INTEGER
        )
        WITH NO LOG;

LOAD FROM '$PROG.1.csv' DELIMITER ','
        INSERT INTO temp_locks;

UNLOAD TO '$PROG.2.csv' DELIMITER ','
        SELECT a.owner, waiter, dbsname, tabname, type, locks
        FROM temp_locks AS a, systabnames AS b
        WHERE a.partnum = b.partnum
        AND dbsname != 'sysmaster'
        ORDER BY 1, 2, 3, 4;
!

It was run as follows:

This saved two files in the current directory:

assert_locks.1.csv

assert_locks.2.csv

The SQL in the shell script shows that the columns each contains are:

assert_locks.1.csv

assert_locks.2.csv

The first character of the lock category constructed by the script is one of these letters:

T	table
R	row
K	index

(K for “key” is used so as not to be confused with I for “Intent”.)

The type from “onstat -k” is then appended but with “HDR+” suppressed. The documentation lists the possible combinations as:

B	Bytes
S	Shared
X	Exclusive
I	Intent
U	Update
IX	Intent-exclusive
IS	Intent-shared
SIX	Shared, intent-exclusive

Note that stage 1 of our shell script skips Intent locks which are rarely useful. If you do need them, comment out these lines:

The results from two stages are saved so that file 1 can be used when analysing an AF off-line or file 2 when on-line with “partnum” values decoded into database and table names. Note that two other solutions exist to perform this decoding:

Upgrade to IDS 14.10.FC6 or above which has an extra “table_name” column added to “onstat -k” output;
Compile the “partn” open source C program from IIUG which can append objects names to a listing.

The second file also has “sysmaster” database objects excluded. In the actual case, this was then read into Excel and:

column headings added in bold;
Excel table inserted;
rows resorted using column controls;
index locks hidden using the “type” column control;
borders specified.

The result was:

Owner	Waiter	Database	Object	Type	Number
56131a68	56154988	database	current_table	RX	2
56131a68	0	database	current_table	RB	2
56131a68	0	database	history_table	RX	2
56154988	0	database	current_table	RX	1
56154988	56131a68	database	current_table	RX	1
56154988	0	database	history_table	RX	2

Both “owner” and “waiter” are hexadecimal user thread addresses. You can look for these in the first column of “onstat -u” output (described here) either in an AF or at the command line. In the actual case, relevant lines were:

The entire details for each of those session IDs in column 3 above could then be found by searching for them in the “onstat -g ses 0” section. That enabled us to determine that an entirely different application was unexpectedly using the same stored procedure concurrently. The solution was to reschedule jobs to run at different times, though recoding the order of statements in the procedure might also have worked.

Caveats

Access at the command line as a privileged user – typically “informix” – is required.

Conclusion

With that proviso, the script given in this article is all you need to extract details of problem locks in assert files or “onstat -k” output.

Disclaimer

Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.