You use Microsoft SQL Server 2012 to write code for a transaction that contains several
statements.
There is high contention between readers and writers on several tables used by your
transaction.
You need to minimize the use of the tempdb space. You also need to prevent reading
queries from blocking writing queries.
Which isolation level should you use?
A.
SERIALIZABLE
B.
SNAPSHOT
C.
READ COMMITTED SNAPSHOT
D.
REPEATABLE READ
Explanation:
Reference:
http://msdn.microsoft.com/en-us/library/ms173763.aspx
In case of A & D, READ will block WRITE.
In B, we use up tempdb space.
C is the one. It only allow reading committed data, but not blocking WRITE.
Thank you for explanation.
READ COMMITTED SNAPSHOT: This is actually not a new isolation level; it is an optional way of using the default READ COMMITTED isolation level, and 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.
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.
Hence, the only difference here is, the SNAPSHOT isolation level enforces stricter controls over what data can be read between transactions.