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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
arbertCommented:
If there is a number greater than 0 under the BLK column--that's the process that SPID that is blocking....
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

TranscendMediaAuthor Commented:
Nope they are all showing 0
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
TranscendMediaAuthor Commented:
Whoops, sorry thought I saw two different users.  Arbert you will be awarded all the points!
TranscendMediaAuthor Commented:
Brett,

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

arbertCommented:
Cool, if you post the stored proc, maybe we could improve that a bit too?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.