Contents
What causes SQL deadlock?
A deadlock occurs when 2 processes are competing for exclusive access to a resource but is unable to obtain exclusive access to it because the other process is preventing it. SQL Server automatically detects when deadlocks have occurred and takes action by killing one of the processes known as the victim.
How can we avoid update deadlock in SQL Server?
When two transactions are waiting on each other to convert Shared locks on resources to Exclusive locks, a deadlock occurs. Update lock (U) is used to avoid deadlocks. Unlike the Exclusive lock, the Update lock places a Shared lock on a resource that already has another shared lock on it.
Can a SELECT query be deadlocked with an update?
SELECT queries take shared locks on the rows they analyze. Shared locks may conflict exclusive locks from update/delete/insert statements. Two SELECT statements are not going to deadlock, but a SELECT can deadlock with an UPDATE.
How does deadlock work in multiple UPDATE statements?
In your case you have 2 sessions waiting for each other. Each session puts X lock on (different) rows and tries to issue (U) lock and read the row updated by other session (with (X) lock held). It’s a bit more complicated actually – you have more than 2 sessions involved but I hope you get an idea.
When do you get a deadlock in SQL Server?
All of the documentation on SQL Server deadlocks talks about the scenario in which operation 1 locks resource A then attempts to access resource B and operation 2 locks resource B and attempts to access resource A. However, I quite often see deadlocks between a select and an update or even between multiple selects in some of our busy applications.
When does a single query get a read lock?
Locks between single queries can happen as they lock single rows, not the entire table: The update query gets an update lock on a few rows in a table, and the select query gets a read lock on some other rows in the table.