October 23, 2006 at 1:09 pm
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
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
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
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
October 24, 2006 at 12:55 pm
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