Which isolation level should you use?

C is incorrect. READ COMMITTED SNAPSHOT is not an isolation level, it’s a database option.

http://www.aiotestking.com/microsoft/which-isolation-level-should-you-use-2/#comments

The most commonly used isolation levels are:

**READ COMMITTED: This is the default isolation level. All readers in that session
will only read data changes that have been committed. So all the SELECT statements
will attempt to acquire shared locks, and any underlying data resources that are being changed by a different session, and therefore have exclusive locks, will block the READ COMMITTED session.

**READ UNCOMMMITED: This isolation level allows readers to read uncommitted
data. This setting removes the shared locks taken by SELECT statements so that readers no longer are blocked by writers. However, the results of a SELECT statement could read uncommitted data that was changed during a transaction and then later was
rolled back to its initial state. This is called reading dirty data.

**READ COMMITTED SNAPSHOT: This is actually not a new isolation level; it is an optional way of using the default READ COMMITTED isolation level; it is the default isolation level in Windows Azure SQL Database. This isolation level has the following traits:

++Often abbreviated as RCSI, it uses tempdb to store original versions of changed
data. These versions are only stored as long as they are needed to allow readers (that is, SELECT statements) to read underlying data in its original state. As a result, SELECT statements no longer need shared locks on the underlying resource while only reading (originally) committed data.

++The READ COMMITTED SNAPSHOT option is set at the database level and is a persistent
database property.
++RCSI is not a separate isolation level; it is only a different way of implementing READ COMMITTED, preventing writers from blocking readers.
++RCSI is the default isolation level for Windows Azure SQL Database.

When you actually want greater isolation for transactions, beyond the default
isolation levels, the following isolation levels enforce stricter controls but they can result in even blocking, or more overhead, and are not used nearly as often as the weaker isolation levels:

**REPEATABLE READ: This isolation level, also set per session, guarantees that whatever data is read in a transaction can be re-read later in the transaction. Updates and deletes of rows already selected are prevented. As a result, shared locks are kept until the end of a transaction. However, the transaction may see new rows added after its first read; this is called a phantom read.

**SNAPSHOT: This isolation level also uses row versioning in tempdb (as does RCSI). It is enabled as a persistent database property and then set per transaction. A transaction using the SNAPSHOT isolation level will be able to repeat any reads, and it will not see any phantom reads. New rows may be added to a table, but the transaction will not see them. Because it uses row versioning, the SNAPSHOT isolation level does not require shared locks on the underlying data.

**SERIALIZABLE: This isolation level is the strongest level and is set per session. At this level, all reads are repeatable and new rows are not allowed in the underlying tables that would satisfy the conditions of the SELECT statements in the transaction.

Have you ever considered creating an ebook or guest authoring on other websites? I have a blog centered on the same subjects you discuss and would love to have you share some stories/information. I know my viewers would enjoy your work. If you are even remotely interested, feel free to send me an email.|