1086
Part VIII: Performance Tuning and Optimization
TABLE 47-5 Locking HintsLocking Hint DescriptionReadUnCommitted Isolation level. Shared locks are not taken as data is read.
ReadCommitted Isolation level. Uses the default transaction-isolation level, Read
Committed.
RepeatableRead Isolation level. Holds share and exclusive locks until
commit transaction.
Serializable Isolation level. Applies the serializable transaction isolation–level
durations to the table, which holds the shared lock on a necessary
range of data until the transaction is complete.
ReadPast Skips locked rows instead of waiting.
RowLock Requests row-level locks be taken instead of page, extent, or table
locks.
PagLock Requests the use of page locks instead of row or table locks.
TabLock Automatically escalates row, page, or extent locks to the table-lock
granularity.
NoLock Shared locks are not used to read data.. Same as
ReadUnCommitted.
TablockX Forces an exclusive lock on the table. This prevents any other trans-
action from working with the table.
HoldLock Holds the share lock until the commit transaction. (Same as
Serializable.)
Updlock Uses an update lock instead of a shared lock and holds the lock.
This blocks any other reads or writes of the data between the initial
read and a write operation. This can be used to escalate locks used
by a SELECT statement within a serializable isolation transaction
from causing deadlocks.
XLock Holds an exclusive lock on the data until the transaction is
committed.The following query uses a locking hint in the from clause of an update query to prevent
the lock manager from escalating the granularity of the locks:USE AdventureWorks2012
UPDATE Person.Person WITH(ROWLOCK)
SET LastName = 'Chapman'
WHERE BusinessEntityID = 1If a query includes subqueries, don’t forget that each query’s table references can generate
locks and can be controlled by a locking hint.c47.indd 1086c47.indd 1086 7/31/2012 10:23:33 AM7/31/2012 10:23:33 AM
http://www.it-ebooks.info