0

We've got a db that has been pretty much deadlock free for over 6 months, but a day or two ago we had over 60, all on the same two or three pages of the same table (~12,000,000 rows, ~250 bytes per row.)

From the deadlock graph I can see what pages are common in all the deadlocks, and part of the query that was running (always an INSERT vs a SELECT) but I have no idea what rows or data this involves. Is there any way to find out?

MDCCL
  • 8,520
  • 3
  • 30
  • 61
GoatInTheMachine
  • 143
  • 1
  • 6
  • 2
    see if my script - deadlock detector helps you. – Kin Shah Jun 15 '18 at 16:14
  • we already get notifications - unfortunately this happens at 1am in the morning as the result of a batch process – GoatInTheMachine Jun 15 '18 at 16:17
  • 1
    My script stores it to a table for later analysis. Check it out. – Kin Shah Jun 15 '18 at 16:18
  • this isn't a duplicate of your question - I do not need notification, and I'd prefer not to have this script running on production. – GoatInTheMachine Jun 15 '18 at 16:38
  • Please study the script. It is event based. So it fires only when a deadlock occurs. You can remove the part of sending email. It even shows you how to shred the deadlock XML . – Kin Shah Jun 15 '18 at 16:40
  • I understand - but I still want to find out how to find rows belonging to a page, so I can see exactly what happened the other day, and also because I am just interested. – GoatInTheMachine Jun 15 '18 at 16:42
  • For page lock deadlocks, we don't really need to worry about what data is on the page. Instead, capture the INSERTS and SELECTS being called and check with your app team/vendor to make sure the newly occurring deadlocks aren't due to a coding error. If the application(s) are working as expected, then you can look into disabling Page Locks for the specific table (this would assume that the INSERT and SELECT aren't trying to access the same ROWS). If they need to access the same rows, you can look into implementing Read-Committed Snapshot Isolation (RCSI). – Colin Douglas Jun 16 '18 at 05:54

0 Answers0