SQL Server Wait Types - Professional SQL Server 2005 Performance Tuning - page 83

escalate to a table lock. SQL Server will also try to escalate locks when coming under memory
pressure. Escalation is always to a table lock, but most of the time the Query Optimizer will choose
the most appropriate granularity of locking when it creates the execution plan, so you shouldn't see
escalations often. The Microsoft Knowledge Base article ''How to Resolve Blocking Problems that Are
Caused by Lock Escalation in SQL Server'' at http://support.microsoft.com/kb/323630 discusses
what you can do if they are a problem on your system.
LockModes
There are two main locking modes: Shared and exclusive. Shared locks are used when reading data to
prevent a process from changing the data while it is being read, but they don't prevent other readers. An
exclusive lock is required to perform an insert, update, or delete and only one exclusive lock can be held
on data at a time.
The other locking modes either are hybrids of these two or are used in support of them. The main two
you will see are update locks and intent locks.
Update locks are used to prevent deadlocks due to updates and are taken when a processes needs to read
data before updating it. Conversion from an update lock to an exclusive lock will then occur. Only one
process can hold an update lock, and the one that does will be the only one able to get an exclusive lock.
Update locks do not block shared locks.
Intent locks are used to indicate that locks are present at a lower level of granularity. For example, a shared
lock on a row will also hold an intent shared lock on the page and table it belongs to. Anyone trying to get
an exclusive lock on the table will be blocked by the intent shared lock, which indicates that a resource
in the table is already locked. Exclusive locks are not compatible with any other locks. Searching SQL
Server 2005 Books Online for ''Lock Compatibility'' reveals a matrix that shows which locks can be held
together on a resource.
LockingProblems
Locking can be a problem in one of two scenarios. The first is a blocking lock whereby one process is
blocked from locking a resource because another process has already locked it. This behavior is obviously
''as designed'' but it creates a performance problem if the first process has to wait too long to acquire
a lock. It can also create a chain of blocked processes each waiting to acquire a lock. This can occur
thousands of times in a busy system, and you'll know it's a problem when users complain and you start
to see locking high on your wait statistics.
The second issue is called a deadlock . This occurs when two processes each hold a lock that the other
needs to continue and if left alone they would wait on each other indefinitely. Fortunately, SQL Server
has built-in deadlock detection and resolution. Every five seconds the lock monitor checks for deadlock
situations and this check becomes more frequent if deadlocks are found. SQL Server resolves deadlocks
by choosing the process that is the least expensive to roll back and terminates it. This is known as the
deadlock victim.
LockinginAction
To review the core concepts, you're going to use the people database again. Open a new window and
run the following statement:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO