|
View previous topic :: View next topic
|
| Author |
Message |
abdulrafi
Active User
Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
abdulrafi
Active User
Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
|
|
