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
 
How to handle deadlock in DB2

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

New User


Joined: 13 Nov 2005
Posts: 29
Location: pune,mumbai

PostPosted: Fri Feb 27, 2009 1:53 pm    Post subject: How to handle deadlock in DB2
Reply with quote

My Db2 table is accessed and updated from a large number of programs daily and because of that table got deadlocks on a daily basis. Can any one explain me how can I resolve this issue, what are the possible solution to prevent it from deadlock and also in order to improve access time of table.
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Feb 27, 2009 3:09 pm    Post subject:
Reply with quote

Pradyutonnet,

Quote:
how can I resolve this issue, what are the possible solution to prevent it from deadlock and also in order to improve access time of table.

For a DEADLOCK to occur, basic reason - RESOURCE N/A @ the current moment.

Consider these things,
    Do Frequent Commits.

    In the tablespace defination LOCKSIZE should be ROW.

    Instead of UPDATE statement, prefer SELECT FOR.... UPDATE OF for proper data locking in application where single row updates are processed.

    Implement multi-(FETCH, INSERT)

    CLUSTER INDEX is prefered on columns used on the ORDER BY clause
Preventing a deadlock - Starts from the design
Improve the access time - Get only the data you need. avoid calculations & stuff regarding in it in a query, which you can do in application program itself.

Wait for the EXPERT advise...... .... .... .. ......................
And ask your DBA, he will point you to the right things you need.

Thank You,
Sushanth Bobby
Back to top
View user's profile Send private message
pradyutonnet
Currently Banned

New User


Joined: 13 Nov 2005
Posts: 29
Location: pune,mumbai

PostPosted: Fri Feb 27, 2009 3:41 pm    Post subject: Practical Scenario
Reply with quote

Actually the above DB2 table was defined long year back and then release by release new programs used the same table for there processing and this is becoming further exacerbated with new releases putting even greater pressure on this table. Do we have possible solution to prevent it from deadlock and also in order to improve access time of table.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Feb 27, 2009 3:49 pm    Post subject:
Reply with quote

Pradyutonnet,

What i learnt from using DB2 is, there is no button called as 'IMPROVE PERFORMANCE'. So............. few things has to be worked out, taking your environment into considerations.

Sushanth
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Feb 27, 2009 4:56 pm    Post subject:
Reply with quote

Do Frequent Commits. - when multiple programs are updating this is not a good design

Instead of UPDATE statement, prefer SELECT FOR.... UPDATE OF for proper data locking in application where single row updates are processed. ---- search update is always better than positioned update because of the overhead of (creation, open and position) and complexity of the later ..


Increase the lock list capacity and lock escalation threshold, modify the LOCKLIST and MAXLOCKS parameters, respectively. this should enable DB2 to handle more locks without running into a deadlock ... As sushanth mentioned do a brainstorming over your application design too ...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Feb 27, 2009 5:04 pm    Post subject:
Reply with quote

when you receive notification of a deadlock/timeout rollback/deadlock

instead of dying (program end) because of a nasty -sqlcode,
if the sqlcode does not indicate an auto rollback, then issue one yourself and then restart the last unit of work.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Feb 27, 2009 5:04 pm    Post subject:
Reply with quote

Also if possible issue data modification SQL statements as close to the end of the UOW as possible ...the later in the UOW the update occurs, the shorter the duration of the lock ...
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: Fri Feb 27, 2009 10:39 pm    Post subject:
Reply with quote

Hello,

Have you verified that you are dealing with a deadlock rather than a timeout. Sometimes, they are mistakenly viewed as the same problem. They are not.

If there is a deadlock problem it is not the number of programs and "hits" that causes your deadlock problem. You have deadlocks because of the design used.

I suppose there is a reason you posted the same topic twice in only an hour or so. . . The other has been deleted.
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 hot to get details when "EXEC CI... Andi1982 CICS 11 Tue Sep 20, 2016 5:01 pm
No new posts How to handle AISS abend ? robin ma 2015 CICS 1 Mon Nov 09, 2015 1:47 pm
No new posts Can a select query participate in a d... apvardhini DB2 1 Fri May 29, 2015 12:39 pm
No new posts possible to write synad to only handl... Lynne Schuler Testing & Performance analysis 3 Sat Apr 11, 2015 10:55 pm

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