You have a Prod database on an instance of SQL Server 2008. Your Prod database contains the following table, PurchaseOrderHistory, which contains many rows:
You have two stored procedures that have the following characteristics:
StoredProc1 has a default isolation level and issues multiple DML statements that perform modifications to the PurchaseOrderHistory table.
StoredProc2 has a default isolation level and issues a SELECT query to return all rows in the PurchaseOrderHistory table.
You want to ensure that StoredProc2 uses row versioning and retrieves the last committed version of each row. Which action should you take?
A. Set the transaction isolation level of StoredProc1 to SERIALIZABLE.
B. Include the NOLOCK table hint for the query in StoredProc2.
C. Set the ALLOW_SNAPSHOT_ISOLATION database option to OFF.
D. Set the READ_COMMITTED_SNAPSHOT database option to ON.
Correct Answer: D
Explanation/Reference:
This setting allows SQL Server to use row versioning instead of locking for all transactions with an isolation level of READ COMMITTED. This provides read consistency at the statement level using row versions stored in the tempdb database. Each time a DML statement within the transaction executes, a new snapshot of the row versions is stored in the tempdb database and is used if other transactions access the modified rows. Using row versioning can significantly increase the size of the tempdbdatabase. Therefore, you should closely analyze your requirements before using row versioning. You might choose to use statement-level row versioning when you are running queries against data that must be accurate as of the time that other queries modifying the data started. For transactions with other isolation levels, you might consider using a rowversion column to implement row-versioning capability manually.
You should not set the transaction isolation level of StoredProc1 to SERIALIZABLE. The SERIALIZABLE transaction isolation level completely isolates transactions from each other, and is the most restrictive isolation level. If you used a SERIALIZABLE transaction isolation level, StoredProc2 could not access the data until StoredProc1 completed. The SERIALIZABLE transaction isolation level increases the possibility of blocking and should be avoided unless it is absolutely necessary. In this scenario, there was no requirement to modify StoredProc1’s transaction isolation level.
You should not include the NOLOCK table hint for the query in StoredProc2. The NOLOCK table hint is specified for a single table in a query to ignore any locked rows in the table. Using this hint in a query will allow the query to execute, even if locks exist, but the query may return uncommitted rows.
You should not set the ALLOW_SNAPSHOT_ISOLATION database option to OFF. The ALLOW_SNAPSHOT_ISOLATION database option controls whether transactions with a transaction isolation level of SNAPSHOT are allowed. In this scenario, both transactions used the default transaction isolation level, which is READ COMMITTED. Therefore, modifying this option would have no effect. When this option is turned on, transactions with the SNAPSHOT isolation level use row versioning at the transaction level, rather than at the statement level.
