4

This question pertains purely to deadlock caused due to lock resources.

I am reading this article: Using a Clustered Index to Solve a SQL Server Deadlock Issue

They have explained how adding nonclustered index, or clustered index solves the deadlock problem.

The general idea is that - the UPDATE query will not block because of the index seek that will result in only few rows being locked.

However, the way SQL server works is - the engine at any point (for example after 5000 row level locks [Source: https://www.youtube.com/watch?v=EqfAPZGKifA at 30:25]) decide to elevate the lock to page or table level lock, thus locking the entire object (table for example). So is the solution given in this article - that adding clustered index is a solution to deadlocks - reliable?

Improve this question
2
  • Indexes can help reduce the number of deadlocks but bear in mind that the transaction isolation level will also dictate the number of deadlocks that you get. The options read committed snapshot isolation and snapshot isolation can reduce further the number of deadlocks by using row versioning with previous versions being stored temporarily in TempDB. You should write retry logic in your code and can use the option deadlock priority to determine which transaction is chosen as the deadlock victim if needed. See sqlshack.com/snapshot-isolation-in-sql-server
    – Steve
    Sep 8, 2021 at 22:32
  • Comments should only be used for asking for clarification, or to leave constructive criticism that guides the author in improving the post, or to add relevant but minor or transient information to a post (e.g. a link to a related question, or an alert to the author that the question has been updated), or to provide site usage guidance. See the help for details.
    – Hannah Vernon ♦
    Sep 8, 2021 at 22:36

2 Answers 2

Reset to default
6

At the root of it all, deadlocks are fundamentally a performance problem. After all, regardless of the order of the code and all the other common deadlock causes, if all the transactions complete before any other transaction can start, you won't see a deadlock.

Fundamentally, you're still best off by fixing the root causes causing the deadlock (usually, but not always, accessing tables in different orders in different transactions), but simply doing performance tuning fixes a lot of deadlocks. With that in mind, yeah, the right index could absolutely mitigate the number of deadlocks you experience.

Although, as with anything else, it does depend on your code, the extent of the problem, the indexes chosen, load, changes over time, statistics, all the things that can affect performance.

Improve this answer
10
  • 1
    Deadlocks are fundamentally a performance problem - do you mean this is a database performance problem? I was thinking that it is more of problem with way application (that uses the database) logic is designed.
    – variable
    Sep 8, 2021 at 11:51
  • 1
    Yeah, databases. The root cause of the deadlock is usually application logic, yes. That's where the real problem is. However, you'll never see it if everything goes really fast. It's all about the locks being held by transactions. Get them out of the way faster, reduced, or even eliminated, chances for collisions. As I said above, I'd still focus on the code, but making it run faster will absolutely help.
    – Grant Fritchey
    Sep 8, 2021 at 11:54
  • By faster you mean the system RAM? Or indexes? Because as shown in the youtube video, even if index is added, and thus it makes the query perform fast, the engine might still choose to lock entire table or page anyways (if more than a certain number of rows need updating). So indexing may not be the solution at all.
    – variable
    Sep 8, 2021 at 12:02
  • 1
    I'm pretty sure I was careful to put in caveats in the statement above, "...you're still best off by fixing the root causes...". I didn't say it was a magic bullet. Just that it can help.
    – Grant Fritchey
    Sep 8, 2021 at 12:22
  • @variable: No they're not. I got a deadlock from updating two rows that happened to be adjacent to each other because SQL server couldn't handle it whereas in turn these updates had to be in two different transactions because SQL server couldn't handle a different thing elsewhere. But the actual lock diagram showed no possible deadlock.
    – Joshua
    Sep 8, 2021 at 20:47
3

To rephrase Tolstoy "All happy databases are alike, each unhappy database is unhappy in its own way". (stolen from someone but can't remember to give the credit - sorry). By which I mean there are many sorts of deadlock and each will require their own solution, but certainly adding an index is one of the more common remedies. Note that you can only get deadlocks with exclusive locks & it ought to be pretty rare in an OLTP system to be modifying over 5000 rows.

Improve this answer
5
  • I have updated question to convey that I am referring to deadlocks caused due to lock resources. It maybe rare for OLTP system to be modifying over 5000 rows. So are you saying that for few rows this is a fine solution and for 5000+ rows this is not a solution?
    – variable
    Sep 8, 2021 at 11:45
  • 2
    a classic example of when an index will fix a deadlock is if there is no supporting index then the query will scan the table & as Grant says this will make the transaction take a long time, therefore more chance that another session will request conflicting locks. If you add an index so that the query is fast it REDUCES the chance of a deadlock, note that you can't always eliminate deadlocks so it is important that the application code can detect and retry the transaction if a deadlock is encountered.
    – Stephen Morris - Mo64
    Sep 8, 2021 at 11:59
  • As shown in the youtube video, even if index is added, and thus it makes the query fast, the engine might still choose to lock entire table or page anyways (if more than a certain number of rows need updating). So indexing may not be the solution at all.
    – variable
    Sep 8, 2021 at 12:01
  • 1
    Nope. Not always. The single best solution is to fix the underlying code. However, indexing can absolutely help. Yes, given the right index, etc., all the caveats.
    – Grant Fritchey
    Sep 8, 2021 at 12:21
  • Deadlocks can happen between readers and writers: Lookups, Deadlocks, and You
    – Erik Darling
    Sep 8, 2021 at 14:52

Your Answer

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

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