Home » Microsoft » MB6-886 » Which action should you take?
You are a database developer on an instance of SQL Server 2008. You are creating a stored procedure that implements transactions. Your stored procedure will be used by a production application to request supplies from inventory.
The application will allow users on the plant floor to query the parts available in the database from the Product table. Then, the application will allow the user to select the product and the quantity of that product that should be delivered to the user’s station. When the inventory request is finalized by clicking the Save button in the application, the request is added as an internal pick order to be processed in the warehouse.
After a user clicks the Save button and the user is in the process of finalizing a request, no other users, internal or external, should be able to make requests on the same product. You want to accomplish this with minimal locking.
Which action should you take?
A. Configure the application to use a transaction isolation level of REPEATABLE READ.
B. Specify the TABLOCKX table hint when initially querying the Product table.
C. Issue the SET XACT_ABORT ON statement at the beginning of the application.
D. Set the ALLOW_SNAPSHOT_ISOLATION database option to OFF.
Correct Answer: A
Explanation/Reference:
You should configure the application to use the REPEATABLE READ transaction isolation level.
REPEATABLE READ specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.
Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that have been read by the current transaction. Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads. Because shared locks are held to the end of a transaction instead of being released at the end of each statement, concurrency is lower than the default READ COMMITTED isolation level.
You should not specify the TABLOCKX table hint when initially querying the Product table. The TABLOCKX table hint is used to specify that a table should be exclusively locked for a given statement. In this scenario, you want to minimize locking, and other users should be able to access information for other products for which a request is not being made.
You should not issue the SET XACT_ABORT ON statement at the beginning of the application. The XACT_ABORT option determines how SQL Server handles statements within transactions when runtime errors occur. When the XACT_ABORT option is set to ON and a Transact-SQL statement raises an error at run time, the entire transaction is terminated and rolled back. When the XACT_ABORT option is set to OFF, only the Transact-SQL statement that raised the error is rolled back.
You should not set the ALLOW_SNAPSHOT_ISOLATION database option to OFF. The ALLOW_SNAPSHOT_ISOLATION database option controls whether or not the SNAPSHOT isolation level can be used for transactions.
