Thursday, May 25, 2017

TABLOCK hint in an Update SQL Query

TABLOCK is one of several table hints in SQL Server which includes the following:

   FORCESCAN 
   FORCESEEK 
   HOLDLOCK  
   NOLOCK  
   NOWAIT 
   PAGLOCK  
   READCOMMITTED  
   READCOMMITTEDLOCK  
   READPAST  
   READUNCOMMITTED  
   REPEATABLEREAD  
   ROWLOCK  
   SERIALIZABLE  
   SNAPSHOT  
   SPATIAL_WINDOW_MAX_CELLS = integer 
   TABLOCK  
   TABLOCKX  
   UPDLOCK  
   XLOCK 

Table hints can be used with any of the following operations:
Applies to:
DELETE
INSERT
SELECT
UPDATE
MERGE

Here is a query that updates a value in the Northwind Shippers table:
----------
Use Northwind
GO
UPDATE  Shippers
WITH (TABLOCK)
SET CompanyName='Speedy Gonsalez'
WHERE ShipperID=1

--
SELECT * from Shippers
------------
The query updates the name of the shipping company whose ID=1 to a new name. When TABLOCK is used in the above manner it should be within the parenthesis.


Read this recommendation from Microsoft:

"An UPDATE statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes. With an exclusive lock, no other transactions can modify data. You can specify table hints to override this default behavior for the duration of the UPDATE statement by specifying another locking method, however, we recommend that hints be used only as a last resort by experienced developers and database administrators"