Lock Type Hints
You can use the following optimizer hints to override the lock type that SQL Server uses:
• 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 deadlocks that can result when using HOLDLOCK
.
• XLOCK
—This option places an exclusive lock on the resource for the duration of the transaction. This prevents other processes from acquiring locks on the resource.
Get Microsoft® SQL Server 2008 R2 Unleashed now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.