Locking and Concurrency - SQL Server 2012 Internals and Troubleshooting - page 163

LOCKS

You've just read about blocking other users from seeing what's going on, or jumping in with other
changes that stop a transaction from being able to behave in proper isolation. The mechanism in SQL
Server that is used for this is a lock. By locking a piece of data, SQL Server prevents access to it. As
you might expect, there are a variety of lock types (known as lock modes), and a variety of types of
access they are designed to prevent. A lock is needed for almost every kind of data access, even reads,
which means that locks actually do their blocking against other lock types. We don't say that an
exclusive lock blocks reads; we say that an exclusive lock is incompatible with a shared lock — but
the effect is the same. To picture the kind of blocking that will occur, imagine the kind of access that
needs to take out the incompatible locks.

There are also many different types of things that can be locked. These are known as lock resources.
By identifying what is locked, what caused it to be locked, and the type of lock that is taken out, you
have the full set of information about the lock.

Monitoring Locks
Two main dynamic management views (DMVs) are used to monitor locks: sys.dm_tran_locks and
sys.dm_os_wait_stats . The former lists all the locks that have currently been taken, and includes
information identifying the lock resource and more, while the latter lists information about how
often processes have had to wait when acquiring the various lock types.

The DMV sys.dm_tran_locks returns a lot of useful information about the locks currently held
in the SQL Server instance. It shows not only the lock resource (as per the list of lock resources
described next) and lock mode (also discussed later), but also the ID of the database in which the
lock is located, plenty of information to identify the resource that has been locked, whether the lock
was actually granted (it could be being converted, or it could be waiting), how many locks of that
type are on the resource, the sessionid , and more.

There are a lot of columns, as described in Table 6-1.

TABLE 6-1: Currently Active Lock Resource Information Returned by sys.dm_tran_locks

COLUMN NAME DESCRIPTION

resource _ type

The type of lock resource that a transaction is trying to take a lock
on, such as OBJECT , PAGE , KEY , etc.

Resource _ subtype Provides a subclassifi cation of the resource requested. Not manda-

tory, but good for qualifying the resource; for example, if you create a
table in a transaction you will get a subtype of DDL on the
DATABASE resource_type lock.

Resource _
database _ id

The database in which the resource was requested

continues