When my current Silverlight 3 project went into QA environment, I got one more chance to met my old friend.Yes It’s deadlock.But unfortunately we were not able to reproduce it in the dev environment.There started the problem.
But after a big battle in my dev machine I was able to reproduce the deadlock.Steps were simple.I just need to do the normal operations in the application at lightening speed.
Then we knew its a problem with the WCF service calls from the client to server.There were 2 calls going from client to server while saving where it is supposed to be one by according to the design of the application.But application is in QA.So we tried to resolve that by introducing some mechanisms in the DB.To continue that we needed the information about the SPs which are in deadlock.
Since our application implements the transaction from code and that transaction contains so many sp calls one by one it was very difficult to figure out which sp is in trouble.If we try debug mode there won’t be any deadlock.Finally our DBA given me 2 methods to find out the sps.
The below one give the details about the things happening inside the sql server including the SPID.
Execute the above command in SQL Server Management Studio on your DB.Once you get the SPID use that to find out exactly what is deadlocked in the below method.
dbcc inputbuffer (<SPID>)
I know for a DBA this is not a big thing.But for a developer I think this will help him a lot.