Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Deadlock issue -913

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Rahul_kumar
Warnings : 2

New User


Joined: 24 Jun 2006
Posts: 50

PostPosted: Thu Jul 14, 2011 1:04 pm    Post subject: Deadlock issue -913
Reply with quote

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
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Jul 14, 2011 1:33 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jul 14, 2011 1:44 pm    Post subject:
Reply with quote

Quote:
The account and branch numbers updated are different for all the 4 jobs


that my be true,

but the issue is that there are overlapping ranges.
otherwise, you would not have the -913.

unless of course that branch no, account number is not the primary index.
Back to top
View user's profile Send private message
Rahul_kumar
Warnings : 2

New User


Joined: 24 Jun 2006
Posts: 50

PostPosted: Thu Jul 14, 2011 2:55 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Jul 14, 2011 3:00 pm    Post subject:
Reply with quote

Rahul,

Quote:
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

Where is the input coming for this four jobs like,
1. Four different input datasets which four jobs use...
2. Inputs are coming from a different table

Post the full error message

Thanks,
Sushanth
Back to top
View user's profile Send private message
Rahul_kumar
Warnings : 2

New User


Joined: 24 Jun 2006
Posts: 50

PostPosted: Thu Jul 14, 2011 3:28 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Thu Jul 14, 2011 4:56 pm    Post subject:
Reply with quote

How many indexes do you have on the target table? And are the new columns part of index?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jul 14, 2011 5:04 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Rahul_kumar
Warnings : 2

New User


Joined: 24 Jun 2006
Posts: 50

PostPosted: Fri Jul 15, 2011 2:58 pm    Post subject:
Reply with quote

Hi Dick,

Many Thanks!

I will try and keep you posted about the same.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jul 15, 2011 3:49 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts DFHRESPONSE returns issue divated CICS 3 Wed Nov 02, 2016 6:32 pm
No new posts Can sending 5 MB data between cobol p... Kevin Vaz CICS 12 Tue Oct 18, 2016 4:50 pm
No new posts REXX Screen not working due to LINKED... sundarkudos CLIST & REXX 1 Mon May 09, 2016 1:44 pm
No new posts Issue in sending zip file as mail att... ajithajt JCL & VSAM 8 Thu Apr 07, 2016 9:11 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us