Abstract
Listing Informix locks is a convenient and easy way to manage data. In 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. Listing Informix locks is 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:
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:
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:
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:
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:
The results from listing Informix locks using both available interfaces could then be compared:
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:
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:
- 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:
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:
The details of the waiting session could then be seen in the first terminal window as follows:
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
Listing Informix locks is a valuable tool. 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.
