Hi Gurus,

I have been to trying find the cause of lock waits in one of the databases. The OS is AIX 5.3 and DB2 UDB v8.2 FP 7.

I am pasting the output of db2pd -locks waits -db <database name>

Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HldCnt Att Rlse
0x078000002070B8C0 33 00060004000000000000000054 Table NON G 33 0 0 0 0x40
0x0780000020712480 8 00060004000000000000000054 Table NON G 8 0 0 0 0x40
0x07800000206E98C0 33 00060010000000000000000054 Table NON G 33 0 0 0 0x40
0x07800000206F3740 16 00060010000000000000000054 Table NON G 16 0 0 0 0x40
0x0780000020726C00 33 00060012000000000000000054 Table NON G 33 0 0 0 0x40
0x0780000020710D00 8 00060012000000000000000054 Table NON G 8 0 0 0 0x0
0x078000002070B740 36 00060012000000000000000054 Table NON G 36 0 0 0 0x0
0x07800000206FDE80 36 00060016000000000000000054 Table NON G 36 0 0 0 0x40
0x0780000020726380 31 00060016000000000000000054 Table NON G 31 0 0 0 0x40
0x07800000207175C0 29 00060016000000000000000054 Table NON G 29 0 0 0 0x40
0x07800000206F28C0 36 0003000c000000000000000054 Table NON G 36 0 0 0 0x40
0x07800000207293C0 31 0003000c000000000000000054 Table NON G 31 0 0 0 0x40
0x0780000020717C80 29 0003000c000000000000000054 Table NON G 29 0 0 0 0x40
0x07800000206FB440 36 0003000e000000000000000054 Table NON G 36 0 0 0 0x7f
0x07800000206EBDC0 31 0003000e000000000000000054 Table NON G 31 0 0 0 0x7f
0x07800000206F0B00 29 0003000e000000000000000054 Table NON G 29 0 0 0 0x7f
0x078000002070AB40 8 0003000e000000000000000054 Table NON G 8 0 0 0 0x7f
0x07800000206FCF80 24 0003000e000000000000000054 Table NON G 24 0 0 0 0x7f
0x078000002070B180 33 0003000e000000000000000054 Table NON G 33 0 0 0 0x0
0x07800000206F3B40 16 0003000e000000000000000054 Table NON G 16 0 0 0 0x0
0x078000002070C140 36 00030010000000000000000054 Table NON G 36 0 0 0 0x40
0x07800000207266C0 31 00030010000000000000000054 Table NON G 31 0 0 0 0x40


And here is the db cfg output:

db2 get db cfg | grep LOCK

Max storage for lock list (4KB) (LOCKLIST) = 35000
Percent. of lock lists per application (MAXLOCKS) = 60
Lock timeout (sec) (LOCKTIMEOUT) = 300

Even though the locklist is pretty big why the row locks are getting converted into table locks is what i fail to understand.

Thanks
Anks

Are you experiencing lock escalation? What is the SQL that is causing the locks?

Andy

Yes, I do see lock escalations in the db2daig.log. How do I find out the SQL which is causing the locks?

Thanks

Anks

I am not 100% sure, but I think you need to set up an Event Monitor.

Andy

Doesn't the default event monitor catches the SQL causing lock problems? or it just records the deadlocks?

Thanks

Anks

There is a default deadlock event monitor, but not one for lock escalation, although lock escalation can cause deadlocks.

To minimize lock escalation, increase to LOCKLIST to at least 4096 in the db config.

I am curious, are you getting the deadlock/timeout error, or are you just seeing some time spent in lock wait? The reason, I am asking is that your timeout time is 5 minutes. That is a long time to wait for a lock and have to time out. With you locklist so big, my guess is that you have some statements that affect a hugh amount of rows, which is what is causing your lock escalations.

If you are not getting the deadlock/timeout error, I would not worry about it too much. You should expect some amout of time waiting for locks. If you are getting the error and the reason code = 68 (timeout) then you have a big issue that needs to be resolved. Something is holding locks for over 5 minutes, and that is bad for concurrency. If you are getting reason code = 2 (deadlock), use the event monitor to track down the cause.

Andy

Thanks Andy for the response. Well the application did report of an error saying "current transaction rolled back because of deadlock or lock time out". When I look at db2diag.log i don't see anything. I checked the default event monitor to look for a deadlock but nothing in there. So i thought its a lock time out situation.

I took the database snapshot and here's the output:

Locks held currently = 47
Lock waits = 167
Time database waited on locks (ms) = 117236
Lock list memory in use (Bytes) = 39104
Deadlocks detected = 1
Lock escalations = 0
Exclusive lock escalations = 0
Agents currently waiting on locks = 0
Lock Timeouts = 0
Number of indoubt transactions = 0

Now here the snaphot says that it has detected a deadlock, which i presume its a lock time out. I fail to understand why in the database snapshot i am seeing this.

I am really confused as to what is going on.

Thanks,
Anks

This is what I think you should do.

1) setup an event monitor that track everything (not just the "default") -- In my opinion, it is better to have too much info than not enough.

2) Start the event monitor.

3) run the application

4) Use the snapshot monitor to watch behavior

5) record the entire error messages in the app. You need to know whether it is encountering timeouts or deadlocks. Make sure you have the timestamp of when these occurred--you will need this to compare to data from event monitor.

6) run the entire thing until you are experiencing your problem.

7) analize all the data gathered to determine where problem is occurring.

Note: here is a good GUI snapshot monitor: http://chuzhoi_files.tripod.com/index.html

Andy

Hi,
I got "ERROR: The requested URL could not be retrieved" and error message said: "Illegal character in hostname; underscores are not allowed". I use Firefox 2.0 on Windows XP SP2.

I know Tripod Web pages structure because I have a web page on it for at least a decade. Tripod has two addresses to one web page. So the same address that is working on my browser is: http://members.tripod.com/chuzhoi_files/index.html

Hope this helps,
Grofaty