Post reply

Index Causing Deadlock on thread - HELP

  • 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???

  • January 15, 2004 at 4:43 pm

    #490327

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

    Not sure, this is a strange one.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 16, 2004 at 4:09 am

    #490381

    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. 

  • January 22, 2004 at 7:33 am

    #491121

    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

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

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