Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How is the deadlock graph below possible? How did two SELECT statements simultaneously acquire an exclusive lock on the exact same resource?

The exclusive locks were on the primary key index for sfcs_state (id column) - PK__sfcs_sta__3213E83FC57BE1D2. They were both accessing the same value sfcs_state.id = 2708.

I'm including a link to the graph image, because I don't have the reputation to post images.

enter image description here

Here are the offending SELECT statements (they are exactly the same):

SELECT TOP 1 (1) AS [a] 
FROM [sfcs_unitstate] 
INNER JOIN [sfcs_state] ON ( [sfcs_unitstate].[state_ptr_id] = [sfcs_state].[id] ) 
WHERE (
    [sfcs_unitstate].[unit_id] = @P1 AND 
    [sfcs_state].[key] LIKE @P2 AND 
    [sfcs_state].[value] LIKE @P3 
)

SELECT TOP 1 (1) AS [a] 
FROM [sfcs_unitstate] 
INNER JOIN [sfcs_state] ON ( [sfcs_unitstate].[state_ptr_id] = [sfcs_state].[id] ) 
WHERE (
    [sfcs_unitstate].[unit_id] = @P1 AND 
    [sfcs_state].[key] LIKE @P2 AND 
    [sfcs_state].[value] LIKE @P3 
)
share|improve this question

migrated from serverfault.com May 28 at 18:50

This question came from our site for professional system and network administrators.

2  
Is it possible that they were both waiting to escalate the lock from row -> page or page -> table? If so, the other select would prevent that from happening. Also, what's the transaction isolation level of each process? –  Ben Thul May 28 at 16:49
    
The transaction isolation level of both processes is READ COMMITTED. –  Charlie May 28 at 19:06
    
Can you upload the xdl somewhere? –  Martin Smith May 28 at 19:06
1  
But what else runs in the same transaction? Why are they holding X locks anyway? –  Martin Smith May 28 at 19:35
1  
You should be able to capture all the SQL statements that run by using either Profiler or Extended Events (2008+). –  Jon Seigel May 28 at 19:45
show 13 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.