Sql server® 2012 t-sql fundamentals

 

CHAPTER 9

 

Transactions and Concurrency

 

 305

shows a screen shot of SQL Server Management Studio, where the SPID 53 appears to the right of the 

logon name K2\Gandalf.

FIGuRE 9-1

  The SSID shown in SQL Server Management Studio.

As you can see in the output of the query against sys.dm_tran_locks, four sessions (51, 52, 53, and 

54) are currently holding locks. You can see the following:

■

■

The resource type that is locked (for example, KEY for a row in an index)

■

■

The ID of the database in which it is locked, which you can translate to the database name by 

using the DB_NAME function

■

■

The resource and resource ID

■

■

The lock mode

■

■

Whether the lock was granted or the session is waiting for it

Note that this is only a subset of the view’s attributes; I recommend that you explore the other at-

tributes of the view to learn what other information about locks is available.

In the output from my query, you can observe that process 53 is waiting for a shared lock on a row 

in the sample database TSQL2012. (The database name is obtained with the DB_NAME function.) No-

tice that process 52 is holding an exclusive lock on the same row. You can determine this by observing 

that both processes lock a row with the same res and resid values. You can figure out which table is 

involved by moving upward in the lock hierarchy for either process 52 or 53 and inspecting the intent 

locks on the page and the object (table) where the row resides. You can use the OBJECT_NAME func-

tion to translate the object ID (133575514 in this example) that appears under the resid attribute in 

the object lock. You will find that the table involved is Production.Product.

www.it-ebooks.info

---

306  

Microsoft SQL Server 2012 T-SQL Fundamentals

The sys.dm_tran_locks view only gives you information about the IDs of the processes involved in 

the blocking chain and nothing else. To get information about the connections associated with the 

processes involved in the blocking chain, query a view called sys.dm_exec_connections, and filter only 

the SPIDs that are involved.

SELECT -- use * to explore 

  session_id AS spid, 

  connect_time, 

  last_read, 

  last_write, 

  most_recent_sql_handle 

FROM sys.dm_exec_connections 

WHERE session_id IN(52, 53);

Note that the process IDs that were involved in the blocking chain in my system were 52 and 53. 

Depending on what else you are doing in your system, you might get different process IDs. When you 

run the queries that I demonstrate here in your system, make sure that you substitute the process IDs 

with those you find involved in your blocking chain.

This query returns the following output (split into several parts for display purposes here).

spid   connect_time              last_read 

------ ------------------------- ----------------------- 

52     2012-06-25 15:20:03.360   2012-06-25 15:20:15.750 

53     2012-06-25 15:20:07.300   2012-06-25 15:20:20.950 

 

spid   last_write                most_recent_sql_handle 

------ ------------------------- -------------------------------------------------- 

52     2012-06-25 15:20:15.817   0x01000800DE2DB71FB0936F05000000000000000000000000 

53     2012-06-25 15:20:07.327   0x0200000063FC7D052E09844778CDD615CFE7A2D1FB411802

The information that this query gives you about the connections includes:

■

■

The time they connected.

■

■

The time of their last read and write.

■

■

A binary value holding a handle to the most recent SQL batch run by the connection. You 

provide this handle as an input parameter to a table function called sys.dm_exec_sql_text, 

and the function returns the batch of code represented by the handle. You can query the 

table function passing the binary handle explicitly, but you will probably find it more conve-

nient to use the APPLY table operator described in Chapter 5, “Table Expressions,” to apply 

the table function to each connection row like this (run in Connection 3).

SELECT session_id, text  

FROM sys.dm_exec_connections 

  CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST  

WHERE session_id IN(52, 53);

When I run this query, I get the following output, showing the last batch of code invoked by each 

connection involved in the blocking chain.

www.it-ebooks.info

---