SQL to View Locks

In my last posting I showed a query that you can use against the administration views to find the longest running queries in the system. In the example you could see an application that was in LOCKWAIT state and was trying to run an UPDATE statement on the ORG table. In this posting I'll show you another administrative view that you can use to see who is waiting on locks and who are the holders of those locks.

There is a new view called SYSIBMADM.LOCKWAITS in DB2 9 that can show you which applications are waiting on locks, what locks they are waiting on, and what application is holding the lock that the requester is waiting on. Note that you have to turn the LOCK monitor switch on in order to see this level of detailed lock information. To do that you can dynamically update the DBM CFG using 'UPDATE DBM CFG USING DFT_MON_LOCK ON'. Once that is done, you can run the following command to find who is waiting on locks and who is holding those locks being waited on.

In this case we can see that user EATON running from the command line processor (db2bp.exe) with agent id 830 is trying to get a U row lock on a resource but is waiting because application id 831 currently holds that resources with an X lock.

You can drill further using other snapshot views to see who is the holder:

13 Comments

Hi
Anything that you post is always informative, and enlightening. After reading each and every post, i wait with more enthusiasm thinking what is going to come next. Please continue your superb posts which is so much useful for underdogs like me.
Thanks & Regards
Siva

Hi chris,
Ok i know ur with IBM so u will highlight the best of the new product.But still we have good tools like db2pd and i dont see much about it anywhere.It will be really nice if u can unravel its secrets before us.

Thanks Apuna, I'm glad you like it.
Hi Nishant, I love db2pd, I think it's a great addition to DB2 over the last few years. I'll definitely add it to my list of stuff to blog about. Watch for it here in the future.

Hi Sri, thanks.
By migration do you mean migrating from Oracle, Microsoft SQL Server, MySQL to DB2? If so there is already some useful information at the Migrate Now website.
If you mean upgrading from v8 to DB2 9 then there is a good upgrade manual. I can add some of my insights in a future posting.

hi chris,
thanks for providing valueable tips . but this is for V 9 is there something parallel to this is V 8 so that we can check out which are the applications currently waiting for locks.
my project has been recently migrated from oracle to db2 and we face lot of issues regarding locks .
i can explain the whole scenario to you over the mail if you can kindly mail me your mail id .
thanks and regards,
saurabh

This has been a pain-point for me for a long time. I have been unable to figure out how to create a DB2 view or table so that a user with SELECT privledge can not lock the table unintentionally. There are various clients (and apps, and users, ...) which can open up a cursor via a select statement and never close it and therefore locking the table via an updateable select. I have been supplying the clients the "for read only" clause, but I want to be able to fix this on my side. How can I create a VIEW which will not allow a SELECT to lock down the table? Is this possible in DB2

Hi Charlie, you can't create a VIEW that has different isolation levels than the tables it is create on. You can get uses to use UR isolation if you want their queries to bypass locks. Now two SELECT statements at any isolation level won't cause each other to wait. It would only be when one user is sitting waiting on a row (with an S lock for example) and another user wants to update that row (and therefore needs an X lock) then they are incompatible and the second one in has to wait (similar for two applications that both want to update the same row).

If the problem you have is that users run a transaction but then go for coffee or lunch before committing their work (and you can't get the developers to code their transactions better), then you could use the DB2 governor to solve the problem. With the DB2 governor (or Workload Manager in DB2 9.5) you can force off an application that has been sitting idle for a period of time. So for example if an application has not committed in 2 minutes, you can force it off to avoid others from queuing up behind it. A user only needs to be forced off a few times before they start coding their SQL a bit better :-)

Hey Chris, Thanks for the help. I can't believe this is the first time I've heard of the DB2 governor. This tool is great. It even logs the application detail for the apps you force off. This will help me alot!

Hi Chris,I have a question about deadlocks. On what criteria is the victim application chosen? If i have a long running batch that at the end is involved in a deadlock i don't want that application to be rolled back. Does DB2 take in concideration the duration of the applications involved in a deadlock?

Hi Anders, DB2 does not take into consideration the duration of the applications involved (but I will pass that on to development to see if there is anything that can be done here). For the most part you can consider the victim to be chosen at random. There is some logic to not choose things like load, set integrity, index recreation or reorg if for some strange reason they get involved in a deadlock but there is no way to influence the victim if its just two applications involved. I'll discuss it with development.

Disclaimer: Blog contents express the viewpoints of their independent authors and
are not reviewed for correctness or accuracy by
Toolbox for IT. Any opinions, comments, solutions or other commentary
expressed by blog authors are not endorsed or recommended by
Toolbox for IT
or any vendor. If you feel a blog entry is inappropriate,
click here to notify
Toolbox for IT.

Chris is a DB2 for Linux, UNIX and Windows lifer having worked at IBM on DB2 since its inception. Follow along as Chris shares ...
more

Chris is a DB2 for Linux, UNIX and Windows lifer having worked at IBM on DB2 since its inception. Follow along as Chris shares his experiences and provides tips and techniques for getting the most out of DB2 including how to exploit new features and releases as they come on-line.
less

Receive the latest blog posts:

Share Your Perspective

Share your professional knowledge and experience with peers. Start a blog on Toolbox for IT today!