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

migration rejected from serverfault.com 12 hours ago

This question came from our site for professional system and network administrators. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

put on hold as unclear what you're asking by Paul White, Mark Storey-Smith, RolandoMySQLDBA, Kin, RLF 12 hours ago

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

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