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.
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.
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
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.
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.
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.