Clustered PK and deadlocks

• GARCIA

  SSC Enthusiast

  Points: 111

  More actions

  May 11, 2006 at 2:08 am

  #114634

   

  Hi everybody,

  we are living a strange issue with clustered indexes onto PK.

  we are maintaining a SQL 7 database converted into SQL 2000 for a few month.

  During conversion our client made a hand made script who recreate all objects from scratch in order to change collation. into this script he forgot to recreate Clustered PK Indexes.

  At now we want to recreate clustered indexes onto PK, but our application hangs up and causes deadlocks.

  With clustered PK under SQL 7.0 as far as we remind, no problem.

  With non clustered PK under SQL 2000, no problem.

  With non clustered PK + another clustered index onto ID under SQL 2000, no problem.

  With clustered PK onto SQL 2000 -> deadlocks caused by Index use.

  does anyone have the same problem or could help me ?

  thanks for your replies

  Fred.

• Yelena Varshal

  SSC-Dedicated

  Points: 34286

  More actions

  May 11, 2006 at 10:05 am

  #637319

  That may be because the clustered index is the actual data, sorted. I don't have the explanation why it did not happen in 7.0. I am not sure I had a similar issue but I was asked to help out to troubleshoot the issue when the database hanged with locks and deadlocks (traced by Profiler). It happened several times when someone was running a particular report with particular parameters, no other usage of the same reports ever cause any problem. Reading your post, it does looks like it may be an index issue because the difference between running and hanging was not due to the data amout, say, report run with a certain day as a parameter and the database would hang when a report is run with another day as a parameter (consistently), showing deadlocks.

  Regards,Yelena Varsha

• Todd Stu

  Say Hey Kid

  Points: 690

  More actions

  May 12, 2006 at 12:49 pm

  #637673

  are deadlocks during inserts?

  perhaps lower fill factor.

  if clustered pk has to move a lot of pages to insert new rows it may take a while.

  sometimes good not to have the PK be clustered...

  ---

  Cheers,

  Todd
  

• GARCIA

  SSC Enthusiast

  Points: 111

  More actions

  May 13, 2006 at 2:13 am

  #637713

  Hi Todd,

  deadlocks aren't during insert,

  I have a table A(IDA, ColA)  and a table B(IDB, FKA, ColB) which reference table A through a FK FKA.

  My deadlock appears when I have the following instructions

  Begin Transaction A

  Transaction A = Update ColA from TableA Where IDA = x

  When IDA have a clustered FK -> deadlocks, otherwise no problems.

  Transaction B onto a different connection = Update ColB from TableB Where FKA = x

  Commit A

  I'd changed my queries in order to not generate deadlocks, but as our application is quite large (400 tables and 600 screens), I would understand how clustered PK can impact my code and which parts I have to check

  Thanks for your Help,

  Fred.