Home » Microsoft » MB6-886 » Which isolation level should you use?
You are a database developer. You design a database solution by using SQL Server 2008.
Your company has offices in Europe, Asia, North America, and Africa. The company data is updated for each region after business hours.
The queries used by reports on the database are blocked when the data is being updated. Occasionally, the data is updated simultaneously for the Africa and Europe regions.
You need to ensure maximum concurrency for the database by using minimum possible system resources.
Which isolation level should you use?
A. READ COMMITTED
B. REPEATABLE READ
C. SERIALIZABLE
D. SNAPSHOT
Correct Answer: D
Explanation/Reference:
Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.
SNAPSHOT transactions do not request locks when reading data. SNAPSHOT transactions reading data do not block other transactions from writing data.
Transactions writing data do not block SNAPSHOT transactions from reading data.
A transaction running under SNAPSHOT isolation level can view changes made by that transaction. For example, if the transaction performs an UPDATE on a table and then issues a SELECT statement against the same table, the modified data will be included in the result set.
Snapshot isolation uses an optimistic concurrency model. If a snapshot transaction attempts to commit modifications to data that has changed since the transaction began, the transaction will roll back and an error will be raised.
