Update query with TABLOCK hint

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"