Solved

Deadlock on Lock

Posted on 2003-10-25
9
1,137 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:TranscendMedia
ID: 9620506
Nope they are all showing 0
0
 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error when creating an UPDATE Trigger in SQL 6 22
TSQL convert date to string 4 37
Are triggers slow? 7 14
MSSQL - Lock Row from reading by other programs 9 39
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

830 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