EXPERT RESPONSE
A deadlock occurs when two separate processes compete for resources held by one another. DB2 performs deadlock detection for both locks and latches. For example, consider the following processing sequence for two concurrently executing application programs:
Program 1 Program 2
Update Table B/Page 1 Update Table A/Page 1
Lock established Lock established
Intermediate processing Intermediate processing
Update Table A/Page 1 Update Table B/Page 1
Lock (wait) DEADLOCK Lock (wait)
A deadlock occurs when Program 1 requests a lock for a data page held by Program 2, and Program 2 requests a lock for a data page held by Program 1. Both programs are waiting on the other to finish before they can proceed. A deadlock must be resolved before either program can perform subsequent processing. DB2's solution is to target one of the two programs as the victim of the deadlock and deny that program's lock request by setting the SQLCODE to -911. (NOTE: Program 1 and Program 2 can be different instances of the same program running at the same time.)
The length of time DB2 waits before choosing a victim of a deadlock is determined by the DEADLOK IRLM parameter. This parameter also can be set by using the RESOURCE TIMEOUT field on the DB2 installation panel DSNTIPJ. Of course, there is no way to completely avoid deadlocks all of the time (other than single-threading DB2 processes - and no one wants to do that). If you are experiencing a lot of deadlocks, try the following techniques.
Firstly, make sure that your program(s) modify data in DB2 tables in the same order in every program you write. For example, in alphabetical order by table name. Doing this decreases the chance of deadlocking. You indicate that you have done this already.
You also can think about reducing the number of rows per page in the table(s) being accessed - this can result in fewer deadlocks because fewer rows are locked with each page lock. You can control the number of rows per page using the MAXROWS parameter of CREATE TABLESPACE. If you are truly desperate, you might consider row level locking (but that will likely add a LOT of overhead to your application and subsystem). Another thing to check is to make sure you are issuing frequent COMMITs.
Good luck.
Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our discussion forums.
|
