doobya (2/3/2009)
Jonathan Kehayias (2/3/2009)
Take a look at the article from yesterdy. A missing/incorrect index can most definitely be the cause of a deadlock. In fact most deadlocks are simple index issues. Very rarely do I see a deadlock that doesn't involve cross locking indexes on a single table, though it does happen at times.
That is an interesting article.
I am surprised that the lock acquisition against a single table for a single statement isn't batched and serialized!
I am not sure what you mean in this statement? The SH locks are required to ensure consistency during the lookup process across resources. You can certainly change issolation levels and affect they types of locks taken by the SELECT, but that isn't necessarily a solution to the problem depending on the issolation level that you decide to use.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]