Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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. –  Martin Smith Mar 14 '12 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. –  zespri Mar 15 '12 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. –  zespri Mar 15 '12 at 0:18
add comment

1 Answer

up vote 8 down vote accepted

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.

share|improve this answer
    
Is this a common problem with updates, regardless to indexes? Can any two updates on the same table deadlock like this? –  zespri Mar 15 '12 at 0:24
    
Updates that have to scan the table, yes. –  Remus Rusanu Mar 15 '12 at 0:31
    
What if one update has to scan and the other does not. Same thing? –  zespri Mar 15 '12 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. –  Remus Rusanu Mar 15 '12 at 0:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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