|
View previous topic :: View next topic
|
| Author |
Message |
pradyutonnet
Currently Banned
New User
Joined: 13 Nov 2005
Posts: 29
Location: pune,mumbai
|
|
|
|
| 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 |
|
 |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
pradyutonnet
Currently Banned
New User
Joined: 13 Nov 2005
Posts: 29
Location: pune,mumbai
|
|
|
|
| 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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
|
|
