Index Causing Deadlock on thread - HELP

Question

Post reply

Index Causing Deadlock on thread - HELP

ccundy

SSCommitted

Points: 1913
More actions
January 15, 2004 at 12:51 pm

#160701

I have the following query and it will not execute with a clustered index
Select GridPoint, Convert(varchar(20), DateTime, 101) As Date, DatePart(hh, DateTime) + 1 As Hour, PriceCategory, PriceType, Price
From PriceHistory eph Join PriceHistoryLocations ephl On eph.LocationID = ephl.LocationID
Where (DateTime Between '5/1/2002' And '12/31/2003 23:00')
And eph.LocationID In (40, 41, 42, 46, 47)
Order By GridPoint, Convert(varchar(20), DateTime, 101), DatePart(hh, DateTime) + 1, PriceCategory, PriceType
I get the following error
Server: Msg 1205, Level 13, State 2, Line 1
Transaction (Process ID 58) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
There is no one else in the database and every time I change the PK to be non-clustered the statement works. When I change it to clustered I get the same error.
What does this error mean? If I take the date range down it will work, just not with lots of data. I had also updated the statistics
There is about 5 million rows in the table, so I would think clustering the Primary key would be the right action, especially with the above queries.
The PriceHistory table definition is
CREATE TABLE [dbo].[PriceHistory] (
[LocationID] [int] NOT NULL ,
[DateTime] [smalldatetime] NOT NULL ,
[Price] [money] NOT NULL ,
[TimeZone] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Version] [int] NOT NULL ,
[LastModified] [datetime] NULL ,
[LastChangedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[esi_PriceHistory] WITH NOCHECK ADD
CONSTRAINT [PK_esi_PriceHistory] PRIMARY KEY CLUSTERED
([LocationID], [DateTime]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
ALTER TABLE [dbo].[esi_PriceHistory] ADD
CONSTRAINT [FK_esi_PriceHistory_esi_PriceHistoryLocations] FOREIGN KEY
([LocationID]) REFERENCES [dbo].[esi_PriceHistoryLocations] ([LocationID])
GO
Any suggestions???

Viewing 4 posts - 1 through 3 (of 3 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 1

maybe change to specifically mention "inner join" in the query?

Not sure, this is a strange one.

psribeiro Old Hand Points: 390 More actions · Answer 2

Do you have more then one processor at this server ? If so, may be the optimizer choose paralelism to resolve the query. If that´s true, try to rewrite the query especifiing the hint (MAXDOP=1). I´ve already have problems with deadlocks in queries that use parelelism for execution, and I solve with this trick.

ccundy SSCommitted Points: 1913 More actions · Answer 3

psribeiro, you nailed it

I added the OPTION MAXDOP=1 to my query and it no longer used parralelism in the estimated execution plan and the query worked bringing back a large amount of data, which was the problem.

Thanks a lot