Deadlock Priority

Kenneth Fisher tells a story of a deadlock:

Why does it matter that they were system sessions? The important thing to remember here is that these sessions can not be KILLed. So because they were holding locks on the database (And somehow even though it was in single user there were multiple sessions with locks in the database. Don’t ask me how.) I wasn’t able to get that exclusive access I needed.

Interestingly when I tried to do the ALTER instead of just hanging I immediately got a deadlock error. I spent a little while trying various things and searching through forums before I went for help on twitter using the #SQLHELP hashtag.

Read on for the answer, including how deadlock priorities saved the day.

Related Posts

Defining Intent Locks

David Fowler explains what an intent lock is and why it’s useful: Let’s just imagine a World without intent locks for a moment. In that World, a user has just decided to select a row from our database. SQL at that point is going to put down a shared lock against the row. Now what’s […]

Read More

Sleeping Sessions Holding Locks

Jonathan Kehayias walks us through an interesting scenario: A recent consulting engagement was focused on blocking issues inside of SQL Server that were causing delays in processing user requests from the application. As we started to dig into the issues being experienced, it became clear that from a SQL Server standpoint, the problem revolved around […]

Read More

Categories

February 2017
MTWTFSS
« Jan Mar »
 12345
6789101112
13141516171819
20212223242526
2728