Deadlock on Lock

Okay was updating a table in design view enterprise manager crashed on my client machine, so I ended the task.  Remote Desktop connection in to server used enterprise manage to make the change, work done. Table seems to function fine, go to site, get this error:

Transaction (Process ID 53) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Read up on MSDN find no solution, restart SQL on DB server and IIS on web server (ASP/VB).  Page same issue.  Go into query analyzer and execute query, still an error.

Here is the query:
SELECT     TOP 100 PERCENT affId, affParentId,
                          (SELECT     COUNT(trkId)
                            FROM          trackingData
                            WHERE      trkCD = 'O' AND trkDate >= CONVERT(smalldatetime, CONVERT(varchar(10), MONTH(GetDate())) + '/01/' + CONVERT(varchar(10),
                                                   YEAR(GetDate()))) AND trkDate <= CONVERT(smalldatetime, CONVERT(varchar(10), MONTH(GetDate()) + 1) + '/01/' + CONVERT(varchar(10),
                                                   YEAR(GetDate()))) AND trackingdata.trkAffiliateid = affiliates.affid) AS AffiliateOrders,
                          (SELECT     COUNT(trkId)
                            FROM          trackingData
                            WHERE      trkCD = 'C' AND trkDate >= CONVERT(smalldatetime, CONVERT(varchar(10), MONTH(GetDate())) + '/01/' + CONVERT(varchar(10),
                                                   YEAR(GetDate()))) AND trkDate <= CONVERT(smalldatetime, CONVERT(varchar(10), MONTH(GetDate()) + 1) + '/01/' + CONVERT(varchar(10),
                                                   YEAR(GetDate()))) AND trackingdata.trkAffiliateid = affiliates.affid) AS AffiliateClicks
FROM         dbo.affiliates


All help is greatly appreciated and look forward to a response.  Thank you to all who help in advance!

TranscendMediaAsked:
Who is Participating?
 
arbertCommented:
Ok, you have to do it when you get that error.  If you don't get the deadlock error, you won't see the blocking.  HOWEVER, when you get the above error, your process has already been "killed" and you won't see it, so you kind of have to watch when you start running your processs.

You can also look in the SQL Server log--sometimes it will record lock and latchio errors.
0
 
arbertCommented:
do a SP_WHO in query analyzer and find the process that's blocking.  Then, you can issue a DBCC INPUTBUFFER(blockingprocessSPIDhere) to see what action it's performing.

Also, you can do the same thing in enterprise manager by looking at the current activity.

Let us know what process is causing the block....

Also, there is a hint you can add on your select statement to not acquire locks  with(nolock) after the table name.  HOWEVER, you have to realize there is a good chance you'll read data that hasn't been committed to the database yet.

Brett
0
 
TranscendMediaAuthor Commented:
Brett,

Thank's for your response,  how can I tell via SP_Who which process is blocking, which column returned will have this issue?

Thanks again,
Greg
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
arbertCommented:
If there is a number greater than 0 under the BLK column--that's the process that SPID that is blocking....
0
 
TranscendMediaAuthor Commented:
Nope they are all showing 0
0
 
TranscendMediaAuthor Commented:
Okay the site was being accessed heavly with a stored procedure that handled site tracking via an Insert.  The data is a summary and does not need to be 100% accurate as the full report shows all accurate data.

Thank you both for your help, I went with the nolock solution in the select, but both solutions helped so I will split the points.
0
 
TranscendMediaAuthor Commented:
Whoops, sorry thought I saw two different users.  Arbert you will be awarded all the points!
0
 
TranscendMediaAuthor Commented:
Brett,

Thank you again for your solution! Have a great weekend!

0
 
arbertCommented:
Cool, if you post the stored proc, maybe we could improve that a bit too?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.