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

COLUMN NAME DESCRIPTION

TRANSACTION

CURSOR

SESSION

SHARED _ TRANSACTION _ WORKSPACE

EXCLUSIVE _ TRANSACTION _ WORKSPACE

Request _ owner _ id Used when the owner type is TRANSACTION and represents the

transaction ID

Request _ owner _ guid Used when the owner type is TRANSACTION and the request has

been made by a distributed transaction. In that circumstance, the
value equates to the MSDTC GUID for that transaction.

Lock_owner_address Represents the in-memory address of the request. Use this column

to join to the resource_address column in sys.dm_os_waiting_
tasks to see blocking lock information.

The DMV sys.dm_os_wait_stats shows the wait stats for the locks by their mode (the list of
lock modes is shown later in this chapter), and you can see these in the wait_type column, with
values such as LCK _ M _ IX for IX-locks , and LCK _ M _ S for S-locks . For each wait _ type , the num-
ber of times waits have been required is shown, along with the total and maximum wait times and
the total signal wait time. Using this DMV can highlight when the Database Engine must wait to
acquire the various locks.

Lock Resources
Table 6-2 describes the many different types of things that can be locked, known as lock resources.
It also gives an example of what each type of resource might look like.

TABLE 6-2: List of Lock Resources and Examples

RESOURCE TYPE EXAMPLE OF RESOURCE DESCRIPTION

RID

1:8185:4 A row identifi er used to lock a single row when

the table in question is a heap

The RID format can be understood as:
<File : Page : Slot ID>

The lock resource RID can be retrieved with the
undocumented %%lockres%% function.

continues