Post reply

Deadlocking on the cluster

  • October 23, 2006 at 1:09 pm

    #170750

    I have a heavily used third party application (which will remain nameless) which does not have the most polite SQL statements I've seen. This app was recently moved to a cluster and now we are receiving deadlocks.

    Most of the deadlocks occurred due to parrallelism. No problem, that was easy enough to shut down.

    Now their is one remaining deadlock issue. Here are the queries:

    Query 1:

     select  distinct TABLE1.CallID

     from  TABLE1,TABLE2

     where  (TABLE1.ID1 = TABLE2.ID1)

      and (TABLE1.field1  <> 'something'  

      AND  TABLE2.field2 = 'something'  

      AND ((TABLE2.field3 = '' or TABLE2.field3 is NULL)  

      OR TABLE2.field3 is NULL ))

     order  by TABLE1.ID1

     

    Query 2:

     set implicit_transactions on

     

     insert  into TABLE1({every field in the table})

     values ({values for every field in the table})

     

     select something from another table

     

     insert  into TABLE2({every field in the table})

     values ({values for every field in the table})

     

     commit tran

    Query 1 gets the 1205 error.

    Now, here's the strange part. The problem only occurs on the server that is on the cluster. I can reproduce the error consistently on the cluster. When I run it on a server that is not on the cluster, it runs fine.

    Changing the SQL is not an option because it is third party. The company provides no support for deadlock issues (their knowledgebase says ALL deadlock issues are a result of the database and must be resolved by the DBA.).

    Has anyone ever seen this type of issue in the past? Am I going to be forced to move this wonderful application off our cluster?

    EdM

     

  • October 24, 2006 at 7:34 am

    #667563

    Are you sure this is caused by clustering?

    Is your other server taking the same load?

    Also have you checked all the server wide settings to ensure the database environment is the same?

  • October 24, 2006 at 7:43 am

    #667566

    The database environment is the same. I have tested the query extensively with no load on the cluster and with creating a load on my test (non-clustered) server. The only difference between the two databases is one is on the cluster and the other isn't.

    EdM

     

  • October 24, 2006 at 10:31 am

    #667640

    Do you reindex the tables regularly on the cluster?  Are the databases the same size (the test server & cluster)?  Are there cluster errors in the NT logs?  Do you get deadlocks immediately following reindexing?  If so, you may want to run a Profiler trace to see if something else is going on that you are not aware of.

  • October 24, 2006 at 11:28 am

    #667649

    what is fill factor on the db on the cluster server?

    if fill factor is too high, inserts may take a long time

    Cheers,

    Todd

  • October 24, 2006 at 12:55 pm

    #667670

    Here is the moral of the story... NEVER TRUST A VENDOR!

    The indexes were supposedly rebuilt within the application every night. However, then vendors definition of "index" is different than what you would expect.

    After rebuilding ALL of the indexes in the database (not just the indexes on the objects that were deadlocking) the error stopped occurring.

    Thank you everyone who responded.

    EdM

Viewing 6 posts - 1 through 5 (of 5 total)

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