Solved

Deadlock on Lock

Posted on 2003-10-25
9
1,125 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Nope they are all showing 0
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Whoops, sorry thought I saw two different users.  Arbert you will be awarded all the points!
0
 

Author Comment

by:TranscendMedia
Comment Utility
Brett,

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

0
 
LVL 34

Expert Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

743 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