I have been aways from blogging for quite a long time, the office hours have been really hectic and then some personal engagements. The weather is getting awesome with Maunsoon approaching.
So here I am back with all my energy to share some piece of knowledge which I myself acquired during this hectic office schedule. So let me start with "a story'.
There was some part of application which was facing lock timeout issues, people blamed the application code and tried to figure out/locate the piece of code which may be causing this, no one could succeed. Later it could be observed in other places as well. Now it was more important to find out the set of queries which may be causing this.
To find out details of lockwaits you can query the view SYSIBMADM.MON_LOCKWAITS to get details of locks which may be causing lockwaits...
These details though will be only level one, and will give only immediate blocked query, if the actual query which is suffering is in-turn dependent on this query, you will have to again query the same view. There are very good chances that by the time you get to second level of query the query would have either committed/forcefully rolledback (in case of a deadlock victim) or timed out.
So I decided to write a piece of SQL which can give me details of the lock and chain of lock wait.
So I wrote a view (please download the definition from here).
So I tried to test the dead lock scenario. I created below tables:
| Tables : TEST and TEST2 |
create table test2 (col1 int, col2 int); create unique index ind_test2 on test2 (col1); commit; |
create table test (col1 int, col2 int); create unique index ind_test on test (col1); commit; |
Once the tables were created, I fired some queries from three different session so that each one would block other:
Session1 | Session2 | Session3 |
insert into test2 (col1 , col2) values (1,2); | insert into test (col1 , col2) values (1,2); insert into test2 (col1 , col2) values (1,2); | insert into test (col1 , col2) values (1,2); |
Then from a forth session I fired "select * from lockChain_INFO"
Which gave an output similar to below one:
Then from session1 I fired one more query which was:
insert into test (col1 , col2) values (1,2);
After this when I again queried the same view I got below result:
These are insert statements and are static ones, and can easily give you the cause of locking (i.e. clashing index keys). Imagine if you are trying to update the same row from two session (and that in application which uses dynamic query so you are not sure of the row).
To help you there is a function available which will provide you the lock info: MON_FORMAT_LOCK_NAME
SELECT SUBSTR(NAME,1,20) AS NAME, SUBSTR(VALUE,1,50) AS VALUE FROM TABLE(MON_FORMAT_LOCK_NAME('LOCK_ID')) as LOCK_DTL;
when lock_id is BLOCKING_LOCK received from view. This query gave descriptive info about the lock as in which row_id and which table etc:
This granular info helped me figure out that the issue was not actually in application or database, there was an issue out of configuration created by the client, which forced usage/update of same row for multiple purpose.
Once this issue was identified, we suggested configuration changes which smoothed the things.
hope this will be equally useful for you guys! :-)
I had worked on couple of more similar snapshot views, which I will be sharing in blogs to come!
| Download View: | lockChain_INFO |
