Contents
Can a SELECT statement be deadlocked with an update?
Two SELECT statements are not going to deadlock, but a SELECT can deadlock with an UPDATE. When such deadlock occurs, the SELECT is usually the victim as it did not perform any update so is always going to loose the draw.
Why does SQL transaction deadlock for SELECT query Stack Overflow?
Like Remus says, you are getting the deadlocks because SELECT and UPDATE (or other) operations deadlocking each other, not SELECT vs SELECT. You will have to look at all your queries touching that table and create proper covering indexes for those queries and that will solve your problems.
Who is the victim of a select deadlock?
When such deadlock occurs, the SELECT is usually the victim as it did not perform any update so is always going to loose the draw. As with any deadlock, you need to post the exact schema of the tables involved, the exact T-SQL statements and the deadlock graph. See How to: Save Deadlock Graphs (SQL Server Profiler).
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 write lock occur in select for update?
If using select-for-update (UPDLOCK) then it will aquire a write-lock from the beginning and then you don’t have the deadlock issue. Locks between single queries can happen as they lock single rows, not the entire table:
How to rerun deadlock on insert and select on same table?
One of the connections should get a message like this: Msg 1205, Level 13, State 51, Line 1 Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. When you are selecting use WITH (NOLOCK) .
Who is involved in the deadlock in the SELECT statement?
The deadlock graph is not rendered properly, but if you look at the Deadlock XML, you can see that two threads from the SELECT statement (SPID 690) are involved in the deadlock . The consumer thread is holding a SHARED lock on PAGE 1219645 and waiting on the producer on port801f8ed0 (e_waitPipeGetRow).