Informix table is locked

Our J2EE web application runs against Informix 9.40.

Sometimes one table is locked and then any JSP having SQL code accessing this table will retur开发者_如何转开发n an error. I know how to use onstat -k to see current locks, but I want to know is there any way to track all locks happened in history and the related session and SQL statement that caused the table lock?

Because I don't know when the same error will happen next...

First, you should be aware that IBM Informix Dynamic Server (IDS) version 9.40 is no longer supported by IBM; neither is the successor version, 10.00. You should be planning to upgrade, probably to 11.70, possibly just to 11.50.

There isn't a way of comprehensively tracking locks. One reason for this is that such tracking would impose considerable slowdown on the system - very considerable slowdown. The locks are usually the second biggest component of shared memory (behind the buffer pool(s) containing data pages), so there are lots of them, and they are often very active, and the locks are often held fleetingly (for small fractions of a second, or even fractions of a millisecond). The volume of data generated from such logging would be extraordinarily large and very difficult to sift through.

If you were on a later version, there might be some options to help with DB-Cron and the Admin API (not certain of that, but the chances would be better), but with 9.40 the options are rather strictly limited.

The nearest approach I can think of is snapshotting with onstat -k -u -r 1. The -k reports on the lock table (as you already know); the -u reports on users (sessions), and the -r 1 repeats the command every second. You can add other data requests as you see fit; -g ses would provided a more detailed report on sessions (if you are the DBSA, usually user informix, when running the command). But this is 1 second sampling...the only good news is that when the lock is placed on the table, it is likely to hang around for a while (because you notice the problem), so the -g ses or -u output will tell you who or what applies the lock. But the volume of data in the interim will be large.

You need to review all the SQL around the system, looking for table locking stuff. Is there any consistency to the timing? Could it be UPDATE STATISTICS being run by a cron job, for example? That acquires table locks fleetingly, I believe; it is also something that has been improved in the releases since your version of IDS.