how to get rid of Deadlock



IBM's flagship relational database management system

how to get rid of Deadlock

Postby vijayverdia » Fri Sep 26, 2008 11:44 am

In our Night batch stream we come across that there are two DB2 jobs which are failing frequently due to -911 sqlcode error. What should be our approach to avoid these abends?
vijayverdia
 
Posts: 2
Joined: Fri Sep 26, 2008 11:29 am
Has thanked: 0 time
Been thanked: 0 time

Re: how to get rid of Deadlock

Postby dick scherrer » Sat Sep 27, 2008 4:17 am

Hello,

You need to determine if the -911 is due to a deadlock or a timeout. This information is available in the reason-code returned fomr sql.

If this is due to a deadlock, you could:

Change the schedule so that one is the successor/predecessor of the other - this way they will never need the same set of resources at the same time.

You have the deadlock because of the way 2 processes are trying to lock rows. Right now, process1 locks tableA row(s), then tableB row(s). At nearly the same time, process2 locks tableB row(s), then tableA row(s). If one process or the other could be changed so that they both lock rows the sme way, the 911


If this is due to a timeout, you could also change the schedule to make sure the jobs do not run at the same time.


If the jobs to not issue timely checkpoints, adding them may help, but you need to make sure you are "in control" and can properly recover/restart if some other problem occurs.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post