Q

Why deadlocks occur and what to do about them

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 .VO7aaqqaAFk.0@/search390>discussion forums.

This was first published in November 2003

Dig deeper on IBM DB2 management

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSOA

SearchSQLServer

Close