#SQL DEADLOCK UPDATE#

We could fix this by expanding the index to also have PostId in it: CREATE INDEX dethklok ON dbo.Votes(VoteTypeId, PostId)

#SQL DEADLOCK UPDATE#

The update query needs to update both indexes on the table, the read query needs to read from both indexes on the table, and they end up blocking each other: kriss kross The reason, of course, if that these two queries compete for the same indexes: who’s who Next, get a couple queries that should be able to co-exist ready to run in a loop.Īfter several seconds, the select query will hit a deadlock. There are ways to repro this sort of deadlock that rely mostly on luck, but the brute force approach is easiest.įirst, create an index that will only partially help out some of our queries: CREATE INDEX dethklok ON dbo.Votes(VoteTypeId) It’s quite a tedious dilemma, and in the case of blocking and, as we’ll see, deadlocks, one that can be avoided with an optimistic isolation level Read Committed Snapshot Isolation, or Snapshot Isolation. And quite often, they lead to people creating very wide indexes to make sure particular queries are covered. On the other hand, they can really exacerbate parameter sniffing problems, and can even lead to read queries blocking write queries. There are times when hinting a nonclustered index, or re-writing a query to get it to use a nonclustered index can really help performance. On the one hand, I don’t think the optimizer uses them enough. I go back and forth when it comes to Lookups.

Sql deadlock