We already tried to update the rows of the same table in order but why are we still getting deadlocked to the same...
program? We use DB2 V6 for OS/390 and CICS TS V1.3 on OS/390 V2.9. The transaction came from RS6K CICS and routed to CICS/390 using DPL.
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 [email protected]/search390>discussion forums.
Dig Deeper on IBM DB2 management
Related Q&A from Craig S. Mullins
To export data from a DB2 table to a flat file, you need to run an export specifying the proper file format. The export utility exports data from a ... Continue Reading
Craig Mullins recommends two specific resources for learning how to create and support Binary Large Objects (BLOB) in DB2. Continue Reading
Craig Mullins explains various definitions of a data mart and sheds some light on the complexity of its relation to a data warehouse. Continue Reading