GSquared (11/7/2012)

---

sqldba_newbie (11/7/2012)

---

GSquared (11/7/2012)

---

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.

If i don't use any snapshot option shouldn't it have read committed isolation level anyways?

Not sure what you're asking.

Read Committed is the default isolation level for SQL Server.

If you set a database to use Read Committed Snapshot Isolation, then that becomes the default. It modifies Read Committed so that it uses snapshot technology as part of the isolation mechanism. That makes it so Insert/Update/Delete operations won't block Selects on the same scope of data. Same behavior as Snapshot Isolation, for the most part, but as the default instead of on a connection-by-connection basis.

If you don't set an isolation level, it will use Read Committed, unless the database has been set for RCSI, and then it will use that instead.