Hello List,

I'm encountering Deadlock problems on my DB2 server 8.1 running on AIX. I'm trying to do a virtual test with 150 users running from Win-runner. After some time, It says the transaction failed due to Deadloacl. Sqlstate=40001. native error = - 911. The current transaction has been rolled back because of deadlock or timeout.

I've reset modified the DB2 configuration by LOCKTIME_OUT set to 0. But this doesnt seem to work.

Can somebody please advice, how can we get rid of this deadlock error?

Thanks,

See if you can find the reason code to determine if it is a deadlock or timeout. These are two different conditions. Deadlocks are not helped by changing locktimeout.

Are you seeing any lock escalations in the db2diag.log?

You can setup an event monitor to get some details on why the deadlock is occuring, ie. the statements that are being issued.

create event monitor mydeadlockmon
for DEADLOCKS WITH DETAILS
WRITE TO FILE '/valid/directory'
autostart global maxfiles 20 maxfilesize 512 buffersize 17 blocked append
;
set event monitor mydeadlockmonstate 1;

You can also try a different isolation level.

Hello,

My application is placing a lock on one of the table which is heavily used. So I believe, Its trying to do insert/delete and update simantenously. thats why I assume, its happening.

any suggestions how to avoid this?

What do you mean "placing a lock on the table."?

Is an SQL lock table statement issued? Is the lock level escalating to table level?

If you are getting lock escalation, increase locklist (the number of pages used to store locks) and increase maxlocks (the percent of the locklist that any one application can use before escalation occurs). I would dramatically increase the locklist (and increase the dbheap at the same time). I would increase maxlocks to 60 if escalation is problem.

Hello Fedman,

I'm not that much familar with DB2. The error what i'm getting is Deadlock error. The other parameters what you have listed are i guess not related to my case. But again, If Deadlock can be avoided by switching to the parameters what you have suggested. then i'll be happy to go with it.

Thanks for your help.

A deadlock is different from a timeout. The error message says deadlock OR timeout, which means you need to investigate further to see which one it is.

However, a deadlock or a timeout can be caused by lock escalation (or other reasons), in which case the suggestions I gave may help.

Hello Feldman,

Thanks for your email.

My error is pure "Deadlock". I did modified some of the parameters as you suggested. But as i said. I'm new to DB2 and not that much familiar with the configuration pages. I was wondering, what is the exact configuration name for locklist? Is this under database configuration or the instance configuration?

Also the heapsize. I did see a heap size at the database configuration level and one at instance level? Dont know which one to modify??? Also whats the different between instance level heap and database level? any other

Any other suggestion what you advice on this deadlock?

Thanks for all your help.

Have you created an event monitor as I suggested? This will tell you what statements are involved... that will be very helpful I suspect!

I did not created the event monitor. I'll create it now and will see, whats going on.

Will keep posted.

Thanks,

locklist, maxlocks, and dbheap are in the database configuration (one for each database). See the "Administration Guide: Performance" on how to update these. Or you can use the Control Center GUI to change these.