kevin77 (6/5/2010)
Does anyone have any suggestions or comments? Either on my example scenario or why the database isn't smart enough to acquire the locks that it needs all at the same time. I understand that in order to have a highly performant, highly concurrent system, you need to minimize the use of locks, but your article just surprised me on how seemingly simple it is to generate deadlock in SQL Server by doing something so common and what the database is supposed to be designed to handle, that is, concurrent INSERTs and SELECTs.
While I'm not the most experienced when it comes to deadlocks (although reading this article has caused to be become smarter in the subject for some reason:cool:), I do support a database that gets them frequently. Now most of them are short timed deadlocks but every now and then one occurs that will bring the database to a hault. Some instances (well most) are hardware related, which it is on a VM and shouldn't be (which you can check Jonathan's blog on plenty of articles to support this for me;-))
However, this application does 10 times more reads than it does writes (updates/inserts/etc), which the major tables have millions of rows of data. So to prevent heavy deadlocking they simply add (NO LOCK) on their SELECT queries since they are reading from a few tables that are consistently being updated or inserted into.
Which the software is a BMC product, I leave it to the peanut gallery to guess which one. :hehe:
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton