Dead Lock SQL SERVER 2000

Question

Post reply

Dead Lock SQL SERVER 2000

SQLPain

Hall of Fame

Points: 3522
More actions
December 14, 2015 at 3:40 pm

#306158

I am getting a following error while running a very basic query:
Msg 1205, Level 13, State 2, Line 1
Transaction (Process ID 91) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction
The problem occurs only for couple of Customer Reps and not all:
INNER JOIN dbo.Channels AS ch ON a.SourceID = ch.ChannelID AND ch.C_RepID = XXXX
It just doesn't work for couple of cutomer reps and works perfectly fine for the rest. SELECT statement has 28 columns and I am joining 8 tables.
Does that mean those customer reps are busy updating any of those 8 tables and forgot to close their screen? How do I figure out what is causing the deadlock? This has never happened before. Its on sql server 2000

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLPain Hall of Fame Points: 3522 More actions · Answer 1

So I figured it out the select statement that is causing the problem:

SELECT

CASE WHEN ch.assignedbranch = ch.channelid THEN '' ELSE (SELECT namelong FROM channels WHERE ch.channelid = ch.assignedbranch) END AS Fac

SQLPain Hall of Fame Points: 3522 More actions · Answer 2

SQLPain

Hall of Fame

Points: 3522

December 14, 2015 at 4:47 pm

#1845953

Can somebody help me with the case statement?

Sergiy SSC Guru Points: 110209 More actions · Answer 3

Post the whole query.

With the fragment you posted I can only suggest to replace the correlated subquery with a join.

_____________
Code for TallyGenerator

SQLPain Hall of Fame Points: 3522 More actions · Answer 4

Here you go:

SELECT DISTINCT

a.AppID

, CONVERT(varchar(10), a.DateAppRec, 101) AS DateAppRec

, ch.NameLong AS Dealer

, CASE WHEN ch.assignedbranch = ch.channelid THEN '' ELSE (SELECT namelong FROM channels WHERE channelid = ch.assignedbranch) END AS Facilitator

FROM dbo.Applications AS a

INNER JOIN dbo.Channels AS ch ON a.SourceID = ch.ChannelID AND ch.MarketRepID = XX /*This is the market rep number to change*/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

SQLPain (12/15/2015)
Here you go:
SELECT DISTINCT
a.AppID
, CONVERT(varchar(10), a.DateAppRec, 101) AS DateAppRec
, ch.NameLong AS Dealer
, CASE WHEN ch.assignedbranch = ch.channelid THEN '' ELSE (SELECT namelong FROM channels WHERE channelid = ch.assignedbranch) END AS Facilitator
FROM dbo.Applications AS a
INNER JOIN dbo.Channels AS ch ON a.SourceID = ch.ChannelID AND ch.MarketRepID = XX /*This is the market rep number to change*/

SQLPain (12/14/2015)
SELECT statement has 28 columns and I am joining 8 tables.

So, which one is it?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SQLPain Hall of Fame Points: 3522 More actions · Answer 6

I gave only the select statement and the join that is causing the problem,

If you want I can give you the whole code also