How to resolve this Deadlock Issue?
-
Hi,
We are facing Deadlock issues, when we try to execute a proceddure for different parameters simulataneously,
Example:
proc_name '20070630','755','Y','html'
proc_name '20070630','681','Y','html'
------
When we are executing the above procedure simultaneously..we are getting the Deadlock error..
Here is the trace file for your reference.. We could not find the KEY parameter in this Trace file and we are not able to find the object name which is creating the Deadlock.
------
2007-07-16 13:59:49.70 logonLogin failed for user 'JUnitCustomer'.
2007-07-16 14:00:11.01 logonLogin failed for user 'JUnitCustomer'.
2007-07-16 14:00:11.06 logonLogin failed for user 'JUnitCustomer'.
2007-07-16 14:00:11.17 logonLogin failed for user 'JUnitCustomer'.
2007-07-16 14:00:11.21 logonLogin failed for user 'JUnitCustomer'.
2007-07-16 14:00:39.90 spid4ResType:LockOwner Stype:'OR' Mode: IX SPID:78 ECID:0 Ec
0x6B133538) Value:0x6a2007-07-16 14:00:39.90 spid4Victim Resource Owner:
2007-07-16 14:00:39.90 spid4ResType:LockOwner Stype:'OR' Mode: IX SPID:78 ECID:0 Ec
0x6B133538) Value:0x6a2007-07-16 14:00:39.90 spid4Requested By:
2007-07-16 14:00:39.90 spid4Grant List 2::
2007-07-16 14:00:39.90 spid4Input Buf: Language Event: proc_create_fsa_export '20070630','755','Y','html'
2007-07-16 14:00:39.90 spid4SPID: 74 ECID: 0 Statement Type: UPDATE Line #: 252
2007-07-16 14:00:39.90 spid4Owner:0x219ed520 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:74 ECID:0
2007-07-16 14:00:39.90 spid4Grant List 0::
2007-07-16 14:00:39.90 spid4PAG: 10:1:3325 CleanCnt:2 Mode: SIU Flags: 0x2
2007-07-16 14:00:39.90 spid4Node:2
2007-07-16 14:00:39.90 spid4
2007-07-16 14:00:39.90 spid4ResType:LockOwner Stype:'OR' Mode: IX SPID:74 ECID:0 Ec
0x7BB75538) Value:0x542007-07-16 14:00:39.90 spid4Requested By:
2007-07-16 14:00:39.90 spid4Input Buf: Language Event: proc_create_fsa_export '20070630','284','Y','html'
2007-07-16 14:00:39.90 spid4SPID: 78 ECID: 0 Statement Type: UPDATE Line #: 224
2007-07-16 14:00:39.90 spid4Owner:0x1c109a00 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:78 ECID:0
2007-07-16 14:00:39.90 spid4Grant List 2::
2007-07-16 14:00:39.90 spid4Grant List 0::
2007-07-16 14:00:39.90 spid4PAG: 10:1:45083 CleanCnt:2 Mode: SIU Flags: 0x2
2007-07-16 14:00:39.90 spid4Node:1
2007-07-16 14:00:39.90 spid4
2007-07-16 14:00:39.90 spid4Wait-for graph
2007-07-16 14:00:39.90 spid4
2007-07-16 14:00:39.90 spid4...
2007-07-16 14:00:41.29 logonLogin failed for user 'JUnitCustomer'.
2007-07-16 14:00:41.32 logonLogin failed for user 'JUnitCustomer'.
----------
Can any one of you please help to solve this issue.
Regards,
E Ezhilan
-
If you could provide the DDL for proc_create_fsa_export it would be helpful as well.
Also, you might want to investigate the failed logins for JUnitCustomer. 7 failures in a little over a minute is a bit excessive
--------------------
Colt 45 - the original point and click interface
-
Thanks for your reply..
Actually..that procedure contains more than 20 called procedures (sub-procedure) which in turn will call some other procedures. So it is not possible to share the entire code.
Entire logic comprises of more than 15.000 lines. Could you please guide me how can I resolve this one.
Regards,
E Ezhilan
-
Yikes!
First of all I would look at locking (begin tran) in general, and see if there is a way to reduce the number of transactions. I would assume with that much code that this takes a while to run, so a dead lock could easily happen. With that much code maybe one of the sub-sub procedures is called multiple places?
I would also say that working on optimizing the execution time on something that complex might severely reduce the amount of dead locks happening. I have dealt with one system I took over that had about 150 dead locks per day, where all it took was looking at some of the query plans to decide on some new indexes. Turned out that one new index sped up execution enough (inside a transaction they did an update to a status field) to reduce the number of dead locks to about 3 per week over the last 6 months.
-
Agree with Anders.
You really need to look at some process optimisation.
"PAG: 10:1:45083" will give you the page resource thats being locked. You can use DBCC PAGE to look at whats on the page.
--------------------
Colt 45 - the original point and click interface -
We had several procs with such lineage... the problem was that the main routine (forgive me for calling it that) had the BEGIN TRANS in it making for a very long winded transaction against many, many tables.
We fixed them by moving the BEGIN TRANS to the very end of the main routine when we did writes and tweaked the sub-routines to write their interim results to temp tables.
Lemme guess, though... you have a cursor that steps through all 20 procs for each row ....
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Must be ultra-high priority
26th July - http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=385309
16th July - http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=382197 ( Jeff - this is the one you contributed to )
And now this post on the 1st August. In that period of time some serious re-working of the process could have been undertaken.
--------------------
Colt 45 - the original point and click interface -
One other thing, if your statements are structured like the one you posted in this thread http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=387258
Try removing the ROWLOCK hint. Amazingly enough you might find that with an appropriate index the query will find and update the record without locking the page (Which is what's happening now).
--------------------
Colt 45 - the original point and click interface -
Guess it does help to click on the Author's name and see previous posts... especially if you remember something similar. Thanks for identifying the dead horse, Phil.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply