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