Microsoft® SQL Server 2012 Unleashed

Lock Type Hints

You can use the following optimizer hints to override the lock type that SQL Server chooses to use:

UPDLOCK—This option is similar to HOLDLOCK except that whereas HOLDLOCK uses a shared lock on the resource, UPDLOCK places an update lock on the resource for the duration of the transaction. This allows other processes to read the information but not acquire update or exclusive locks on the resource. This option provides read repeatability within the transaction while preventing potential deadlocks that can result when using HOLDLOCK.

XLOCK—This option places an exclusive lock on the resource for the duration of the transaction. ...