July 26, 2007 at 12:26 am
Hi,
Whenever I am executing a procedure with different parameters at the same time (we need to execute at same time), I am getting this following Deadlock error.
Example
-------
procedure_name '20070630','681','Y',''
procedure_name '20070630','755','Y',''
---------
Server: Msg 1205, Level 13, State 1, Procedure proc_create_fsa_export, Line 224
Transaction (Process ID 132) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
-------------
How can I resolve this...
I have used DBCC_TRACEON ('1204') and found the line number of update statement and included 'with (ROWLOCK)'. It is not working properly.
Kindly suggest some other alternatives.
This procedure has more than 13000 lines. It is highly impossible to change the logic for the time being, as the issue is High priority issue and has to be sorted out immediately.
Regards,
E Ezhilan
July 26, 2007 at 2:57 am
It's kinda hard to say anything specific, other than that in general, regarding deadlock issues, a deadlock is often a result of a flawed design. More than one process wants to 'touch' different things, but they do not do it in the same order, thus causing a state where they both wait for eachother indefinetly because they each hold locks on what the other wants. Then you have a deadlock.
There are cases when it's not quite that way, though. It's possible that a deadlock may occur not on the datapages, but in the index(es) instead. Another place where deadlock may happen is when parallellism is used or between threads.
Here's a starting point for troubleshooting deadlocks.
http://msdn2.microsoft.com/en-US/library/aa213030(sql.80).aspx
/Kenneth
July 26, 2007 at 7:12 am
Could you post the procedures and the details of the deadlock graph?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply