Listing Informix Locks

Abstract

With any database product, it is important to ensure data is only locked when necessary and for as brief a time as possible, or other sessions may crash or freeze if they try to read that data.

This does still inevitably happen, so you need a fast and convenient way to determine what is locked and by which applications. An easy interface for this on IBM Informix Dynamic Server (IDS) is provided in AGS Server Studio but not IDS itself. This article describes a solution using native SQL.

Content

When designing applications for an Informix database, you must decide on optimal connection properties for isolation level and lock mode. The defaults (expressed as SQL statements) are as follows, assuming that the database is logged:

SET ISOLATION TO COMMITTED READ;
SET LOCK MODE TO NOT WAIT;

That combination can be problematic: if a session tries to read data involved in changes by another session which have not yet been committed, it will immediately produce an error.

You normally need to set a timeout (in seconds) during which a session will wait for locked data to be released, for example:

SET LOCK MODE TO WAIT 60;

For read-only applications such as reports, it may be acceptable to ignore the problem of open transactions entirely, and just assume they will be committed. The following enables SELECT statements to traverse such locked data and return the updated values anyway, even if the changes could subsequently be rolled back:

SET ISOLATION TO DIRTY READ;

Even with optimal settings, there may still be occasional problems when data is left locked for a longer period, such as a user walking away from a screen with data entry in progress, or a background job getting stuck. In those situations, you may need to locate the problem quickly or devise an automated alert. There are two interfaces provided with IDS to list locks, namely shell command "onstat -k" and pseudo-table view "sysmaster:syslocks", as illustrated below.

For clarity in this example run only, the IDS internal scheduler was first stopped as otherwise there would have been several shared locks on the "sysadmin" database as well:

$ dbaccess sysadmin –
 
Database selected.
 
> EXECUTE FUNCTION task ('scheduler shutdown');
 
(expression)  Successfully shutdown scheduler
 
1 row(s) retrieved.

> ^C

An insert into a new table was performed in a transaction but not committed (leaving the row exclusively locked), and then a sub-shell opened:

$ dbaccess test –
> 
Database selected.
 
> CREATE TABLE lock_test (id INT, name CHAR(8));
 
Table created.
 
> BEGIN WORK;
 
Started transaction.
 
> INSERT INTO lock_test VALUES (1, 'One');
 
1 row(s) inserted.
 
> !sh

The results from listing locks using both available interfaces could then be compared:

$ onstat -k

 

IBM Informix Dynamic Server Version 12.10.FC11DE -- On-Line -- Up 25 days 21:05:06 -- 189044 Kbytes

 

Locks address          wtlist           owner            lklist           type     tblsnum  rowid    key#/bsiz 44250420         0                44d8fd48           44272398         HDR+X    10022b   101         0    I 442506c8         0                44d8fd48           0                HDR+S    100002   20b         0 44272398         0                44d8fd48           442506c8         HDR+IX   10022b   0           0  3 active, 40000 total, 16384 hash buckets, 1 lock table overflows   $ dbaccess sysmaster -   Database selected.   > SELECT * FROM syslocks;   dbsname  test tabname  lock_test rowidlk  257 keynum   0 type     X owner    285 waiter   dbsname  sysmaster tabname  sysdatabases rowidlk  523 keynum   0 type     S owner    285 waiter   dbsname  test tabname  lock_test rowidlk  0 keynum   0 type     IX owner    285 waiter   dbsname  sysmaster tabname  sysdatabases rowidlk  513 keynum   0 type     S owner    287 waiter   4 row(s) retrieved.   > ^C

Note the extra row in the SQL output with "owner" 287 which would have been its own session ID.

It's obvious that "onstat -k" is harder to interpret, and would at least need the "partn" tool from IIUG to append object names for part numbers to each output line. However, there are essential columns missing from "sysmaster:syslocks" which are present in the following replacement view:

CREATE VIEW v_locks
    (
        session,
        process,
        connected,
        host,
        user,
        database,
        table,
        lock,
        number,
        duration
    )
AS
 
SELECT  x5.sid,
        x5.pid,
        (CURRENT - DBINFO('utc_to_datetime', x5.connected))
            :: INTERVAL HOUR(4) TO SECOND, -- connection duration
        x5.hostname,
        x5.username,
        x1.dbsname,
        x1.tabname,
        DECODE(x0.rowidn,0,'T','R') || x4.txt[1,3],
        COUNT(*) :: INTEGER,
        MAX(CURRENT - DBINFO('utc_to_datetime', x0.grtime))
            :: INTERVAL HOUR(4) TO SECOND -- lock duration
 
FROM    sysmaster:syslcktab   AS x0,
        sysmaster:systabnames AS x1,
        sysmaster:systxptab   AS x2,
        sysmaster:sysrstcb    AS x3,
        sysmaster:flags_text  AS x4,
        sysmaster:syssessions AS x5
 
WHERE   x1.partnum  = x0.partnum
AND     x2.address  = x0.owner
AND     x3.address  = x2.owner
AND     x4.flags    = x0.type
AND     x5.sid      = x3.sid
AND     x4.tabname  = 'syslcktab'
AND     x1.dbsname != 'sysmaster'  -- real databases only
AND     x1.tabname  NOT LIKE '% %' -- real tables only
AND     x4.txt      NOT LIKE '%I%' -- ignore "intended" locks
 
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8;
 
GRANT SELECT ON v_locks TO PUBLIC;

Note that date/time values are typically stored in sysmaster tables as integers containing the number of seconds since 1st January 1970, and have to be converted to DATETIME with the "utc_to_datetime" option of the DBINFO function. The result can be converted to a duration by subtracting it from the current DATETIME, and the required precision specified with the INTERVAL cast.

Output in our example is:

$ dbaccess test -
 
Database selected.
 
> SELECT * FROM v_locks;
 
session    285
process    2700
connected  0:07:44
host       localhost
user       informix
database   test
table      lock_test
lock       RX
number     1
duration   0:07:18
 
1 row(s) retrieved.
 
> ^C
  • The "lock" column contains "R" for row or "T" for table plus "S" for shared, "X" for exclusive or "U" for update (details here).
  • Extraneous rows have been excluded, leaving only actual locks on physical tables.
  • Results are aggregated in case sessions have a large number of rows locked in the same table.

The following view lists which other sessions are waiting for a lock to be released:

CREATE VIEW v_waiters
    (
        session,
        process,
        connected,
        host,
        user,
        database,
        locker
    )
AS
 
SELECT  x1.sid,
        x1.pid,
        (CURRENT - DBINFO('utc_to_datetime', x1.connected))
            :: INTERVAL HOUR(4) TO SECOND, -- connection duration
        x1.hostname,
        x1.username,
        x6.odb_dbname,
        x5.sid
 
FROM    sysmaster:sysscblst AS x1,
        sysmaster:sysrstcb  AS x2,
        sysmaster:syslcktab AS x3,
        sysmaster:sysrstcb  AS x4,
        sysmaster:sysscblst AS x5,
        sysmaster:sysopendb AS x6
 
WHERE   x2.scb           = x1.address
AND     x3.address       = x2.lkwait
AND     x4.txp           = x3.owner
AND     x5.address       = x4.scb
AND     x6.odb_sessionid = x1.sid
AND     x6.odb_iscurrent = 'Y';
 
GRANT SELECT ON v_waiters TO PUBLIC;

To illustrate this, in a second terminal window, an attempt to scan the same table entered a wait state due to the exclusively locked row:

$ dbaccess test –
 
Database selected.
 
> SET LOCK MODE TO WAIT;
 
Lockmode set.
 
> SELECT * FROM lock_test;

The details of the waiting session could then be seen in the first terminal window as follows:

$ dbaccess test –
 
Database selected.
 
> SELECT * FROM v_waiters;
 
session    295
process    2745
connected  0:01:57
host       localhost
user       informix
database   test
locker     285
 
1 row(s) retrieved.
 
> ^C

Caveats

If there are a very large number of locks, such as when an inexperienced user attempts to delete millions of rows in a single SQL statement (see article here on how to avoid that), it might be extremely slow to get a result back from the "v_locks" view. You can check for that scenario with "onstat -k | tail" which shows the total number of locks, take a sample from "syslocks" to determine the session ID holding most locks, and then use "onstat -g ses session-id" to get the full details and see what it's doing.

Conclusion

The provided views list what data is currently locked, by whom, and which other sessions are waiting on those locks. They can be used in any free graphical SQL environment such as SQuirreL SQL Client to provide a readable aggregated list, or within an alerting framework such as Nagios when the lock duration exceeds a specified threshold.

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.