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
 

 

dead lock while updating the DB2 table

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

Active User


Joined: 14 Sep 2009
Posts: 135
Location: Coimbatore

PostPosted: Tue Oct 22, 2013 7:02 pm    Post subject: dead lock while updating the DB2 table
Reply with quote

Hi,

In my application I do have Batch jobs and CICS program running.

Same table is being updated by Batch jobs and CICS programs as well.

At some point I am getting deadlock when Batch jobs are running. I am getting this issue in production for the past few days and I am trying to put UPDATE FOR so that it will update only that row and not have a table lock. But this also does not prevent the deadlock.

I am stuck with this. Could you please help me in resolving this deadlock.
I could not run the Batch jobs when the online is down as there are users who use the online system and at the background we need the Batch jobs to run.

Thanks
Abdul rafi.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Oct 22, 2013 8:29 pm    Post subject:
Reply with quote

Hello,

You need to look at the code and make sure that all of the update processes lock rows in the same order.

The code is now locking things in such a way that the deadlock (fatal embrace) occurs. There is no generic fix - it will be specific to the code that is running.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Oct 23, 2013 1:11 pm    Post subject:
Reply with quote

1.Check LOCKMAX parm
2.Is both batch and online doing frequent commits?
3.If there is no SLA for CICS you could try introducing a wait logic before abending the program
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Oct 23, 2013 9:02 pm    Post subject:
Reply with quote

Hello,

Quote:
If there is no SLA for CICS you could try introducing a wait logic before abending the program
Better to fix the code rather than cause even more bottlenecks . . . IMHO.
Back to top
View user's profile Send private message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 135
Location: Coimbatore

PostPosted: Thu Oct 24, 2013 10:35 am    Post subject:
Reply with quote

Hi,

I dint get your point on 'make sure that all of the update processes lock rows in the same order'.

I changed my code to UPDATE FOR, but its of no use, its still facing the same issue.

Can you please help in what way should I chnage my code to avoid deadlock.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Oct 24, 2013 11:46 am    Post subject:
Reply with quote

Quote:
Better to fix the code rather than cause even more bottlenecks . . . IMHO.


Yes,atleast that some deadlocks could be removed and in meantime TS could dig further to find the root cause
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Oct 24, 2013 6:36 pm    Post subject:
Reply with quote

Hello,

Quote:
I dint get your point on 'make sure that all of the update processes lock rows in the same order'.
The problem is because one process locks something in tableA and then later will lock something in tableB.

At the same time, another process locks something in tableB (the same tableB something that first process is going to need next) and then first process tries to lock the something in tableB. It is already locked and has to wait.

Now the deadlock. The second process tries to lock the already locked row in tableA and there is the deadlock.

The processes all need to lock rows in the same order (i.e. lock tableA row, then tableB row or lock tableB row, then tableA row.)

Often someone will say "we cannot do that". It can be done with some thought and possibly a bit of process redesign.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Oct 24, 2013 8:35 pm    Post subject:
Reply with quote

1) Never trust an unexperienced and sometimes not even experienced programmers when they come to you with "a deadlock". Often they stop reading the errormessage before the OR:
Quote:
-911 THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR TIMEOUT
-913 UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT.

Always ask/check the reasoncode :
Quote:
•00C90088 - deadlock
•00C9008E - timeout


2) Deadlock doesn't have to be 2 tables. In my experience : a lot of times it is a row or page with another row or page from the same table.

3) establishing a wait and retry the statement is of no use with a -911. a rollback has occured and you'll have to restart your complete logical unit of work.

4) If you do get a lock escalation to table , you really need to commit more (and/or check LOCKMAX like Pandora-Box said).
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 Updating the counters after eliminati... PANDU1 DFSORT/ICETOOL 12 Mon Nov 21, 2016 9:47 am
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm


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