Friday, June 22, 2012

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

1 comment:

  1. Locked out? No problem! Our technicians can open most locks without causing any damage, and if damaged we can repair whilst on site.

    Lost your keys? Worried about someone entering your home with the lost keys? Most locks can be altered by one of our technicians, this process changes the lock so a completely different key works the lock, we then supply you with new keys and the old key can no longer be used.

    Lock broken or not working? All of our technicians are experienced in the service and repair of most locks available in Australia. The majority of locks can be repaired, however if yours cannot be repaired we can replace it to fix your problem.

    Tired of carrying around too many keys? flat rate lock n key denver Locksmiths can in most cases alter locks so your entire house, or even multiple houses are opened by a single key. If your locks are not compatible with such a system we can offer you replacement locks or parts to make it happen.

    Lightning Locksmiths technicians are available if you have any residential lock problem. We can offer emergency after hours service.

    ReplyDelete