Home » Microsoft » MB6-886 » Which action should you take?
You have a database that resides on an instance of SQL Server 2008. You are creating a stored procedure that will update a critical table. The stored procedure reads data in the Master table, performs aggregation calculations on the table’s data, and populates a table variable with the result.
You must ensure that the variable always contains the most up-to-date information from the Master table, and you want to lock the table exclusively while the query runs.
Which action should you take?
A. Set the ALLOW_SNAPSHOT_ISOLATION database option to ON.
B. Perform the aggregate calculations within a transaction, and set the transaction’s isolation level to REPEATABLE READ.
C. Include the READPAST query hint in the query containing the aggregate calculations.
D. Add the TABLOCKX hint to the query containing the aggregate calculations.
Correct Answer: D
Explanation/Reference:
In this scenario, you wanted to lock the Master table exclusively for a given query. To do so, you can use the TABLOCKX query hint.
You should not set the ALLOW_SNAPSHOT_ISOLATION database option to ON. The ALLOW_SNAPSHOT_ISOLATION database option controls whether transactions with a SNAPSHOT isolation level are allowed. A value of ON indicates transactions with a SNAPSHOT isolation level are allowed.
You should not include the READPAST query hint in the query containing the aggregate calculations because this would not ensure the query always received the most up-to-date data. The READPAST query hint ignores any current locks and reads past those rows. Any locked rows would not be included in your aggregations.
You should not perform the aggregate calculations within a transaction, and set the transaction’s isolation level to REPEATABLE READ. In this scenario, you only wanted to lock the table during the duration of a query. Therefore, using a table hint would be sufficient.
