Solved

Deadlock on Lock

Posted on 2003-10-25
9
1,130 Views
Last Modified: 2007-12-19
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!

0
Comment
Question by:TranscendMedia
  • 5
  • 4
9 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 9620454
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
 

Author Comment

by:TranscendMedia
ID: 9620481
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
 
LVL 34

Expert Comment

by:arbert
ID: 9620498
If there is a number greater than 0 under the BLK column--that's the process that SPID that is blocking....
0
 

Author Comment

by:TranscendMedia
ID: 9620506
Nope they are all showing 0
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
ID: 9620569
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
 

Author Comment

by:TranscendMedia
ID: 9620614
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
 

Author Comment

by:TranscendMedia
ID: 9620616
Whoops, sorry thought I saw two different users.  Arbert you will be awarded all the points!
0
 

Author Comment

by:TranscendMedia
ID: 9620621
Brett,

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

0
 
LVL 34

Expert Comment

by:arbert
ID: 9620622
Cool, if you post the stored proc, maybe we could improve that a bit too?
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now