• What you need to do is trace the deadlocks and find what's competing with what. The deadlocks portion of Books Online has instructions on how to do that. If you have problems with that (BOL isn't always crystal clear on stuff), ask and we'll try to help, but it's a pretty simple process. Once you have the exact data on what it deadlocking with what, you'll have a better idea of what to handle.

    A word of warning on all of this: Most of the time, factually almost always, it's better to let SQL Server handle your locking itself rather than forcing the issue with a lot of "hints". Don't let using the locking hints become a standard practice. Lots of developers do, and they almost always make the database worse rather than better. In 9 years, I've used locking hints twice to good effect, and seen them mess things up more times than I can count.

    On the question about 50 separate row locks, SQL Server can handle that. If some of them conflict with each other, you'll end up with block/deadlock issues.

    On the question about nolock selects being deadlocked, first, they can be deadlocked by something else that's locking resources they need, second, if the select is part of an update/delete/insert, nolock won't actually do what you expect.

    Say, for example, that row 1 is being updated, and a query wants to select it. It won't be able to, even if it uses "nolock", until the update is done. What you would need to do to make it read the partially updated row, is set the query to "read uncommitted" isolation. That would work, but is, again, almost always a bad idea.

    So, trace the deadlocks, find the exact things that are causing them, and you'll be most of the way to solving this. Let us know if you need help on that. Okay?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon