Tuning Locks through Query Hints
From SQLServerPedia
|
See Also: Main_Page - Code Management - Transactions & Locking Query hints, also referred to as optimizer hints, can dramatically affect the execution of a query, its index choices, as well as its locking behavior. In general SQL Server 2000, will choose the query plan that provides the best possible performance, so using optimizer hints should not be exercised "just in case". Instead, it is recommended that you troubleshoot and examine the query execution, then see if you can modify any of your table / index design to optimize the query. Then, as the last resort of a very troubled query, you can evaluate and test using query hints. Most of the optimizer hints are used directly within SELECT statements; however, you can also use some of them with INSERT, UPDATE and DELETE statements. The following table describes the usage of each available optimizer hints: <img border="0" src="TRAN_42.gif" width="704" height="670"> Suppose we had an e-commerce application selling titles from the pubs database. Users are allowed to select the titles by type. Since our sales were skyrocketing, we decided to reserve titles as soon as users made their selection on the web site. When the users gave us their credit card number, we consider the sale complete and mark the title as sold. The point is, we'd have to ensure that no user would be able to select the title if another user has already selected it. The following procedure would accomplish this goal using the optimizer hint XLOCK: CREATE PROCEDURE reserve_type_title @type VARCHAR(12) AS BEGIN TRAN DECLARE @title_id VARCHAR(6) -- SELECT statement with optimizer hint SELECT TOP 1 @title_id = title_id FROM titles WITH (XLOCK) WHERE type = @type AND reserved_flag IS NULL UPDATE titles SET reserved_flag = 1 WHERE title_id = @title_id IF @@error <> 0 BEGIN RAISERROR ('couldn''t reserve the title', 16, 1) WITH LOG ROLLBACK TRAN RETURN END COMMIT TRAN RETURN Note the usage of the reserved_flag column , which was added to check whether a particular title was selected. To make the example simple we also presumed that each user could only select a single title of each type. The XLOCK optimizer hint lets us make sure that no other user can select the same title. Once we update the reserved flag, we can release the locks since the users can only select the titles having a NULL in the reserved_flag field. |