2

I'm looking at a deadlock, which happens when two processes execute an UPDATE statement on the same table. Both transactions are implicit, meaning that they span only the current statement only, which in turn means that locks I'm seeing are not a carry over from a prior statement.

There are two locks and two processes involved in the deadlock. One of the two locks is a page lock in the table (One process has update lock the other one wants Intent Exclusive lock) and the second one is the key lock on the table clustered index (one process has Exclusive lock and the other wants the update lock).

Now, the fields being updated in the both UPDATE statements are not a part of the clustered index.

Still, I'm wondering can the fact that this clustered index has tonnes of duplicate values contribute to the deadlock?

I'm sorry for not posting the deadlock trace, the update statements, the definition of the table and of the index, there is too much to obfuscate.

My question is not how to solve my deadlock, my question is about effect of low cardinality clustered index on probability of deadlocks in principle.

3
  • How are the rows that are updated being found? Through NCIs? The CI gets a uniqueifier added so even if the CI key apparently has loads of dupes this doesn't alter the uniqueness of the row locator. Can you explain why you think it would increase the probability of deadlocks as your description is somewhat abstract. Mar 14, 2012 at 23:38
  • @Martin Smith: I think that it may increase the probability of a deadlock as I think a read it somewhere but there was no explanation and I can't find this article any longer. If I would to reason, I'd say that it could be that the rows are found via clustered index scan which can result in more locks that could have been otherwise. But I can be off the mark here completely. How the rows are found is a good question, let me get back to you on this one. Mar 15, 2012 at 0:15
  • @Martin Smith: one of the updates in question finds rows with NCI and CI key lookup, the other update has 6 clustered index scans in it. I should note that this scans are from different tables as the query is complex. The table being updated has only 1 clustered index scans in 1 non clustered index scan. Mar 15, 2012 at 0:18

1 Answer 1

8

One of the two locks is a page lock in the table (One process has update lock the other one wants Intent Exclusive lock)

This mean that the table is big enough so that the engine has decided to use page granularity locks in order to discover the rows to be updated. Then as it 'drills' in' to actually apply the update to the rows, the deadlock occurs because the two updates both located the row-to-update on the same page. You could force rowlock, or you could disable page locks on the table, but the real problem is that it looks like the WHERE clause of the UPDATE is non-sARG-able.

4
  • Is this a common problem with updates, regardless to indexes? Can any two updates on the same table deadlock like this? Mar 15, 2012 at 0:24
  • Updates that have to scan the table, yes. Mar 15, 2012 at 0:31
  • What if one update has to scan and the other does not. Same thing? Mar 15, 2012 at 0:34
  • Yup. In your case, that seems to be the case (the other conflict is on row resource, not on page). Is just probabilities, the presence of the scan pushes the probabilities way higher, approaching 1, because the scan is guaranteed to look at every row, thus is guaranteed to conflict with any other update somehow. Mar 15, 2012 at 0:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.