lockwt - My go to utility for finding locking problems

If you haven't already added lockwt to your arsenal of Informix DBA tools, you need to immediately.

lockwt is a free utility written by Eric Herber of the The Informix Zone, planetIDS and Herber Consulting that can be downloaded here and is infinitely helpful when you get the midnight call to find out why a table is locked and everyone is screaming.



I recently received one of these calls and when I fired up the lockwt utility I quickly confirmed that there was a locking issue. A session had a hold of a row that other sessions needed and wouldn't let go which was causing all kinds of problems. I have a love/hate relationship with locking problems. I love the fact that they are usually not caused by a problem with the engine and it is the application is doing something screwy, but I hate the fact that they can be incredibly tricky to track down because you have to figure out why the application is holding on to locks longer than you would like.

Using lockwt I was able to see what session was holding the lock and therefore could find out which application was causing the problem and what SQL it was running. Curiously enough the application wasn't running any SQL at the moment, it wasn't doing anything with the engine. It was just sitting there greedily holding on to the resources everyone else wanted. Using the last SQL reported in onstat -g ses, the owner of the application was able to pin point where in the code we were having problems. It was somewhere in the following block of code, can you guess what was causing the problem?

try:
   BEGIN WORK;

   INSERT INTO TABLE A;

   UPDATE TABLE B;

   call_third_party_api();

   COMMIT WORK;

except:
   ROLLBACK WORK;

Normally the third party API call would complete in less than a second, but today they were experiencing some issues and requests were taking over 20 seconds to complete and causing that transaction to remain open way longer than normal.

There isn't much you can do as a DBA at this point to help, the engine is doing what the application asked it to do by preventing other sessions from messing with the data modified in the transaction before the transaction is committed or rolled back.

Lucky for us in this situation we could associate a timeout to the API request that would abort the call if it took longer than we like, rollback the transaction and free up the locked resources.