Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 147
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: 1542
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: 147
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: 1542
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: 1281
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 Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am
No new posts Updating the Trailer count in variabl... satheshbabur DFSORT/ICETOOL 6 Wed Aug 30, 2017 9:49 pm
No new posts Adding a userid to SMTP Security table Yolanda Harvey JCL & VSAM 1 Sun Aug 13, 2017 6:16 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us