Deadlock issue -913

Rahul_kumar · New User Joined: 24 Jun 2006 Posts: 50

Hi,

I have 4 jobs which updates the same table based on the branch number and account number.The account and branch numbers updated are different for all the 4 jobs.I am getting deadlock issue -913 every now and then as the jobs run parallely.I do not want the jobs to be scheduled sequentially as it would consume a lot of time causing the batch cycle to be delayed.The commit frequency is already very less and can't be reduced further.

Can anyone please suggest what can be done to avoid deadlock.

Thanks in advance!

Rahul

sushanth bobby · Posted: Thu Jul 14, 2011 1:33 pm

Rahul,

Last week there has already been a thread regarding deadlocks and suggestions are there in that thread, please go through that may give you an idea where to look and you have to give us more details.

DB2 Contention with same PLAN for partitioned table spaces.

Thanks,
Sushanth

dbzTHEdinosauer · Posted: Thu Jul 14, 2011 1:44 pm

Rahul_kumar · New User Joined: 24 Jun 2006 Posts: 50

Hi,

The branch and account nos are the primary index.

So,in case of overlapping ranges what should be done.

Is it something to do with the DB2 locks,I don't have much idea how I can check the lock on the table through BMC tools.

sushanth bobby · Posted: Thu Jul 14, 2011 3:00 pm

Rahul,

Rahul_kumar · New User Joined: 24 Jun 2006 Posts: 50

Hi,

The source is also 4 table for 4 jobs.Actually,these 4 jobs are existing jobs and we did'nt face contention during that time but recently we added few columns to the target table and now it gives deadlock frequently.

gylbharat · Posted: Thu Jul 14, 2011 4:56 pm

How many indexes do you have on the target table? And are the new columns part of index?

dbzTHEdinosauer · Posted: Thu Jul 14, 2011 5:04 pm

an easy way to look at this:

your updated table has a primary index.

if the table is reorg'd sufficiently, the data in the table is relatively well sequenced,
so that,
if you designed you 4 update processes to only update 1/4 of the table, you won't have any contention.

introduction of new columns, and luck in the past, have contributed to the bad mix.

find out which columns comprise the primary index,
divide the values that would/could populate these columns into 4 groups,
and insure that each of the 'trigger tables' are only populated by values that adhere to its quarter of the base table ranges.

this is obviously not being done now. you need to modify the 4 trigger table population process.

Rahul_kumar · New User Joined: 24 Jun 2006 Posts: 50

Hi Dick,

Many Thanks!

I will try and keep you posted about the same.

dbzTHEdinosauer · Posted: Fri Jul 15, 2011 3:49 pm

Rahul_kumar,

glad we were able to shed some light on the issue.

another thing to keep in mind is what Dick Scherrer has often mentioned,
and what I consider a golden rule with db2:

if you have 2 (or more) programs that are responsible for issuing locks
(updates etc...)
on the same db2 tables, make sure the sequence is the same in both modules.