• It depends on what end result you want.

    Read Committed Snapshot Isolation will help prevent blocking, but allows "old data" to be seen by concurrent transactions. Doesn't have the "read the same row multiple times" issues that NoLock/Uncommitted can have, but reading pre-update data can be a problem in some situations. Check with business users if that's okay.

    "If Bob runs a search on customers, while Joe is adding a customer and Sally is deleting one, Bob will still see the deleted one and won't see the added one, till those finish and he runs the search again. Is that acceptable?"

    Usually, it's just fine. It largely parallels sequential blocking anyway. But be sure.

    On the other hand, if the main load on a table is just Select statements, with infrequent data changes changes, Selects take Shared Locks anyway, and won't block each other. So the default Read Committed works just fine for that. Selects in that level can be blocked by Insert/Update/Delete operations that lock the scope the Select is supposed to run in, but not by other Selects. That's default behavior.

    Tables with a heavy load of concurrent Insert/Update/Delete operations can often get a huge speed increase by using Read Committed Snapshot Isolation. Or even just Snapshot Isolation, but the Read Committed version has some imporant benefits to it. (Bing/Google those, and you'll find the differences easily enough.)

    The main drawback is it can add substantially to the load on tempdb. Make sure before you move ahead on RCSI that you're not going to blow up tempdb. 99% of the time, it's not a problem unless your server is already chronically low on drive space.

    - 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