You maintain a database named Manufacturing on an instance of SQL Server 2008.
You have created a stored procedure named usp_WorkOrderUpdate that is frequently used to update the status of work orders as they are completed. The usp_WorkOrderUpdate stored procedure performs related updates to several tables in the database when a work order is marked as complete. You have used transactions within the procedure to ensure that all table updates are successful.
You need to issue a query against the WorkOrderHistory table, which is one of the tables updated in the usp_WorkOrderUpdate stored procedure. You want your query to execute as quickly as possible, ignoring any locks that the usp_WorkOrderUpdate procedure has acquired, and reading all rows even if they contain uncommitted data.
Which action should you take?
A. Include the NOLOCK table hint in your query.
B. Include the READPAST table hint in your query.
C. Modify the transaction isolation level for usp_WorkOrderUpdate.
D. Set LOCK_TIMEOUT to 0 before executing your query.
Correct Answer: A
Explanation/Reference:
When you include the NOLOCK table hint in a query, any locks currently held on the table are ignored. This will allow your query to execute more quickly, but the query will allow dirty reads. The query would return rows that theusp_WorkOrderUpdate stored procedure has updated but not yet committed. For example, in this scenario, the following query could be used to query the WorkOrderHistory table and ignore any locks currently held on the table:
SELECT * FROM WorkOrderHistory (NOLOCK);
You can specify separate locking hints for each table if a query joins multiple tables, and only ignore locks on specific tables in the query. You should note that the NOLOCK hint can only be used with SELECT statements. If you attempt to include a NOLOCK hint in an INSERT, UPDATE, DELETE, or MERGE statement, an error similar to the following occurs:
Msg 1065, Level 15, State 1, Line 15The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.
You should not include the READPAST table hint in your query because in this scenario, you wanted to read uncommitted data. The READPAST hint will continue to process rows, but will not return any locked rows. This will allow the query to execute quickly, but the query will bypass any rows thatusp_WorkOrderUpdate has modified but not yet committed.
You should not modify the transaction isolation level for usp_WorkOrderUpdate because this might adversely affect the modifications made by the usp_WorkOrderUpdate stored procedure.
You should not set LOCK_TIMEOUT to 0 before executing your query. The LOCK_TIMEOUT setting specifies how long a SQL statement should wait for a lock to be released before it returns an error. If you set LOCK_TIMEOUT to 0, the query would immediately return the following error if the usp_WorkOrderUpdate stored procedure had rows locked:
Msg 1222, Level 16, State 45, Line 1Lock request time out period exceeded.
You can use the @@LOCK_TIMEOUT function to determine the current LOCK_TIMEOUT setting. Including the NOWAIT hint would have the same result as setting the LOCK_TIMEOUT to 0.
