Can indexing be a solution to SQL deadlocks that are caused due to lock resources?

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?

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.

StackExchange Database Administrators Q#299241, answer score: 6