July 27, 2010 at 3:27 pm
For over a year of production usage our tables worked fine. If they were taxed, the inserts or updates queued up if need be, but everything got done.
Then about a month ago, we start getting deadlocks like crazy... Like crazy being 20+ per hour, from 3 sources mainly.
The perplexing part is that I'm not sure what locks an insert caused by default before, but now at this point the inserts are requesting IX (Intent Exclusive) and SS2005 is granting multiple at once and then all records take the exclusive a deadlock happens. Odd to see this on a same table.
Solved by adding a WITH (TabLockX) to the insert. Pretty much only inserts in the table anyways, selects should be coming off of the READ_COMMITTED_SNAPSHOT.
This said, is there a setting that would have changed this behavior in the database? A setting for changing how tables handle locks? Or how SS handles blocks?
I was out on vacation when I believe this started and nobody knows or admits to doing anything. It seems to be on the DB level (maybe just those few tables) because restoring backups onto other servers reproduced the deadlocks.
July 30, 2010 at 12:27 pm
It could be that an index was dropped at some stage or the amount of data changed execution plan and another index is used/not used. Lock escalations could probably lead to deadlocks in certain scenarios as well.
Regards
Piotr
...and your only reply is slàinte mhath
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply