1068
Part VIII: Performance Tuning and Optimization
Exclusive Lock (X)
An exclusive lock means that the transaction is performing a write to the data. As the
name implies, an exclusive lock means that only one transaction may hold an exclusive
lock at one time, and that no transaction can view the data during the exclusive lock.Update Lock (U)
An update lock means that a transaction is getting ready to perform an exclusive lock and
is currently scanning the data to determine the row(s) it wants for that lock. You can think
of the update lock as a shared lock that can blossom into an exclusive lock after the data is
found that needs to be modifi ed.To help prevent deadlocks, only one transaction may hold an update lock at a given time.Intent Locks (various)
Intent locks serve to stake a claim for a shared or exclusive lock without actually being a
shared or exclusive lock. In doing so they solve two performance problems: hierarchical
locking and permanent lock block.The primary purpose of an intent lock is to improve performance. Because an intent lock
is used for all types of locks and for all lock granularities, SQL Server has many types of
intent locks. The following is a sampling of the intent locks:■ (^) Intent Shared Lock (IS)
■ (^) Shared with Intent Exclusive Lock (SIX)
■ (^) Intent Exclusive Lock (IX)
Without intent locks, if transaction 1 holds a shared lock on a row and transaction 2 wants
to grab an exclusive lock on the table, then transaction 2 would need to check for table
locks, extent locks, page locks, row locks, and key locks.
Instead, SQL Server uses intent locks to propagate a lock to higher levels of the data’s hier-
archical levels. When transaction 1 gains a row lock, it also places an intent lock on the
row’s page and table.
The intent locks move the overhead of locking from the transaction needing to check for a lock
to the transaction placing the lock. The transaction placing the lock needs to place three or
four locks, that is, Key, Page, Object, and Database. The transaction checking needs to check
only for locks that contend with the three or four locks it needs to place. That one-time write
of three locks potentially saves hundreds of searches later as other transactions check for locks.
The intent locks also prevent a serious shared-lock contention problem. As long as a trans-
action has a shared lock, another transaction can’t gain an exclusive lock. What would
happen if someone grabbed a shared lock every 5 seconds and held it for 10 seconds while a
transaction was waiting for an exclusive lock? The update transaction could theoretically
wait forever. However, when the transaction has an intent exclusive lock (IX), no other
c47.indd 1068c47.indd 1068 7/31/2012 10:23:30 AM7/31/2012 10:23:30 AM
http://www.it-ebooks.info