Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to resolve a deadlock

Posted on 2007-11-29
6
Medium Priority
?
846 Views
Last Modified: 2012-08-14
I have been experiencing deadlock errors in my sql server 2000 database.  I have run DBCC TRACEON(1204) to identify whats causing the problem and the results are shown in the code snippet below.  I am now trying to identify what the problem is.  It seems that under Node 2, the stored procedure SnapPriceRateBuffers has requested an exclusive lock on TableA.  Under Node 1, stored procedure remote_GetDataAll is performing a SELECT INTO a temporary table (lets say #TableB).  This SELECT INTO statement is sourcing data from TableA in the FROM clause but I don't understand why this situation would cause a deadlock.  Is it possible to explain what is going on from the results of the error log?

Thanks
2007-11-29 11:13:09.16 spid2     Wait-for graph
2007-11-29 11:13:09.16 spid2     
2007-11-29 11:13:09.16 spid2     Node:1
2007-11-29 11:13:09.16 spid2     RID: 7:1:25386:11              CleanCnt:1 Mode: U Flags: 0x2
2007-11-29 11:13:09.16 spid2      Grant List 0::
2007-11-29 11:13:09.16 spid2      Grant List 1::
2007-11-29 11:13:09.16 spid2        Owner:0x5164a880 Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:53 ECID:0
2007-11-29 11:13:09.16 spid2        SPID: 53 ECID: 0 Statement Type: SELECT INTO Line #: 34
2007-11-29 11:13:09.18 spid2        Input Buf: RPC Event: remote_GetDataAll;1
2007-11-29 11:13:09.18 spid2      Requested By: 
2007-11-29 11:13:09.18 spid2        ResType:LockOwner Stype:'OR' Mode: X SPID:52 ECID:0 Ec:(0x76AFD578) Value:0x467028c0 Cost:(0/17A58)
2007-11-29 11:13:09.18 spid2     
2007-11-29 11:13:09.18 spid2     Node:2
2007-11-29 11:13:09.18 spid2     RID: 7:1:1830:27               CleanCnt:1 Mode: X Flags: 0x2
2007-11-29 11:13:09.18 spid2      Grant List 0::
2007-11-29 11:13:09.18 spid2        Owner:0x1ade6200 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:52 ECID:0
2007-11-29 11:13:09.18 spid2        SPID: 52 ECID: 0 Statement Type: UPDATE Line #: 63
2007-11-29 11:13:09.18 spid2        Input Buf: RPC Event: SnapPriceRateBuffers;1
2007-11-29 11:13:09.18 spid2      Requested By: 
2007-11-29 11:13:09.18 spid2        ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec:(0x6A09D578) Value:0x462bdce0 Cost:(0/0)
2007-11-29 11:13:09.18 spid2     Victim Resource Owner:
2007-11-29 11:13:09.18 spid2      ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec:(0x6A09D578) Value:0x462bdce0 Cost:(0/0)

Open in new window

0
Comment
Question by:nicksbell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 20375991
Deadlocks happens when a query is locking a resource and tries to work with other resource that is already locked by another process and that process at same time tries to access the resource locked by 1st process. (I hope isn't confusing) :)

This is a programming issue and DBA can't do nothing about it. SQL Server will choose one of the process as victim and you kill it, freeing resources to the process that's kept alive.

It needs to change queries to use locking hints or create indexes to speed up queries.

Good luck
0
 
LVL 2

Author Comment

by:nicksbell
ID: 20376123
What I am trying to understand is why the following 2 sql statements might cause a deadlock.  This is a simplified example of what is happening in my actual situation.

Statement 1

UPDATE
    TableA
SET
    Column1 = 'xxx'

Statement 2

SELECT
    *
INTO
   #TempTable
FROM
    TableA

These are the 2 processes that are causing the deadlock and I am trying to understand how this could fit into the the scenario you describe for deadlocks to occur.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 20376155
Well, UPDATE don't have a WHERE clause, so SQL Server will lock all table.
SELECT doesn't have a WHERE clause either, so to SQL Server guarantee that all records that will being copied to TempTable will be the same that are in TableA, will need to lock all table as well.

But that scenario should only cause a blocking lock and not a deadlock.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 2

Author Comment

by:nicksbell
ID: 20376245
The situation is actually a lot more complicated than I described above as I was trying to simplify things.  There are more tables (e.g. TableC and TableD) involved through joins in the SELECT statement 2 and TableA has triggers that update TableC and TableD.  Is it possible to identify exactly which table is the locked resource from the error log?
0
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 20376304
Ok. If there's more tables than it's like that you are having really deadlocks.
You should check if UPDATE and SELECT are using the best indexes. Usually deadlock happens when processing a big amount of data. Having the correct indexes, will be faster enough to process requests avoiding deadlocks.
If indexes are ok, then you can use a workaround, that's locking hints. The problem is that you never know if you'll work with dirty data.

Use SELECT columns FROM TableA with (nolock), TableB with (nolock), ...

This avoid locks on table, but will bring all data that wasn't be commited and can be rolled back.

Good luck
0
 
LVL 2

Author Closing Comment

by:nicksbell
ID: 31411748
ok, I will try that.  It might take a while before I can be sure it worked as the deadlocks don't happen all the time but you have been very helpful so you can have the points.

Thanks for your help.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

604 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