Skip to main content
added 272 characters in body
Source Link
Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619

This definitely seems to be a bad combination of isolation level, lock escalation, and many sessions each issuing multiple queries inside the same transaction.

IfUsing Plan Explorer to open the deadlock file, if you expand the waiter/owner list, you see they are all trying to access the same resource (presumably the entire table):

enter image description here

Also a slightly less scary way to see this is to optimize layout and use force directed:

enter image description here

If you replay the deadlock (I wrote about this functionality here, back when I was a Technical PM), you will see all of these different sessions holding their transactions open for relatively long periods of time (5+ minutes, which is crazy, though the animation really hides that), and issuing multiple queries intermittently. You need to drastically reduce the amount of time you spend inside those transactions (or get rid of them altogether), collapse multiple queries for different parameter values into a single, point-in-time query, build better indexes so that escalation doesn't happen, stop forcing repeatable read, created a clustered index (if I'm reading the resource descriptor right, that's a heap!?), or all of the above.

This definitely seems to be a bad combination of isolation level, lock escalation, and many sessions each issuing multiple queries inside the same transaction.

If you expand the waiter/owner list you see they are all trying to access the same resource (presumably the entire table):

enter image description here

Also a slightly less scary way to see this is to optimize layout and use force directed:

enter image description here

If you replay the deadlock you will see all of these different sessions holding their transactions open for relatively long periods of time (5+ minutes, which is crazy, though the animation really hides that), and issuing multiple queries intermittently. You need to drastically reduce the amount of time you spend inside those transactions (or get rid of them altogether), collapse multiple queries for different parameter values into a single, point-in-time query, build better indexes so that escalation doesn't happen, stop forcing repeatable read, created a clustered index (if I'm reading the resource descriptor right, that's a heap!?), or all of the above.

This definitely seems to be a bad combination of isolation level, lock escalation, and many sessions each issuing multiple queries inside the same transaction.

Using Plan Explorer to open the deadlock file, if you expand the waiter/owner list, you see they are all trying to access the same resource (presumably the entire table):

enter image description here

Also a slightly less scary way to see this is to optimize layout and use force directed:

enter image description here

If you replay the deadlock (I wrote about this functionality here, back when I was a Technical PM), you will see all of these different sessions holding their transactions open for relatively long periods of time (5+ minutes, which is crazy, though the animation really hides that), and issuing multiple queries intermittently. You need to drastically reduce the amount of time you spend inside those transactions (or get rid of them altogether), collapse multiple queries for different parameter values into a single, point-in-time query, build better indexes so that escalation doesn't happen, stop forcing repeatable read, created a clustered index (if I'm reading the resource descriptor right, that's a heap!?), or all of the above.

Source Link
Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619

This definitely seems to be a bad combination of isolation level, lock escalation, and many sessions each issuing multiple queries inside the same transaction.

If you expand the waiter/owner list you see they are all trying to access the same resource (presumably the entire table):

enter image description here

Also a slightly less scary way to see this is to optimize layout and use force directed:

enter image description here

If you replay the deadlock you will see all of these different sessions holding their transactions open for relatively long periods of time (5+ minutes, which is crazy, though the animation really hides that), and issuing multiple queries intermittently. You need to drastically reduce the amount of time you spend inside those transactions (or get rid of them altogether), collapse multiple queries for different parameter values into a single, point-in-time query, build better indexes so that escalation doesn't happen, stop forcing repeatable read, created a clustered index (if I'm reading the resource descriptor right, that's a heap!?), or all of the above.