|
CHAPTER 9
Transactions and Concurrency
307
session_id text
----------- -------------------------------------
52 BEGIN TRAN;
UPDATE Production.Products
SET unitprice += 1.00
WHERE productid = 2;
53 (@1 tinyint)
SELECT [productid],[unitprice]
FROM [Production].[Products]
WHERE [productid]=@1
The blocked process—53—shows the query that is waiting because that’s the last thing that the
process ran. As for the blocker, in this example, you can see the statement that caused the problem,
but keep in mind that the blocker might continue working and that the last thing you see in the code
isn’t necessarily the statement that caused the trouble.
You can also find a lot of useful information about the sessions involved in a blocking situation in
the DMV sys.dm_exec_sessions. The following query returns only a small subset of the attributes avail-
able about those sessions.
SELECT -- use * to explore
session_id AS spid,
login_time,
host_name,
program_name,
login_name,
nt_user_name,
last_request_start_time,
last_request_end_time
FROM sys.dm_exec_sessions
WHERE session_id IN(52, 53);
This query returns the following output in this example, split here into several parts.
spid login_time host_name
---- ------------------------- ---------
52 2012-06-25 15:20:03.407 K2
53 2012-06-25 15:20:07.303 K2
spid program_name login_name
------ ------------------------------------------------ ---------------
52 Microsoft SQL Server Management Studio - Query K2\Gandalf
53 Microsoft SQL Server Management Studio - Query K2\Gandalf
spid nt_user_name last_request_start_time last_request_end_time
------ -------------- ------------------------- -----------------------
52 Gandalf 2012-06-25 15:20:15.703 2012-06-25 15:20:15.750
53 Gandalf 2012-06-25 15:20:20.693 2012-06-25 15:20:07.320
This output contains information such as the session’s logon time, host name, program name, log on
name, Windows NT user name, the time that the last request started, and the time that the last request
ended. This kind of information gives you a good idea of what those sessions are doing.
www.it-ebooks.info
308
Microsoft SQL Server 2012 T-SQL Fundamentals
Another DMV that you will probably find very useful for troubleshooting blocking situations is
sys.dm_exec_requests. This view has a row for each active request, including blocked requests. In fact,
you can easily isolate blocked requests because the attribute blocking_session_id is greater than zero.
For example, the following query filters only blocked requests.
SELECT -- use * to explore
session_id AS spid,
blocking_session_id,
command,
sql_handle,
database_id,
wait_type,
wait_time,
wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;
This query returns the following output, split across several lines.
spid blocking_session_id command
------ --------------------- -------
53 52 SELECT
spid sql_handle database_id
------ ---------------------------------------------------- -----------
53 0x0200000063FC7D052E09844778CDD615CFE7A2D1FB411802 8
spid wait_type wait_time wait_resource
------ ----------- ----------- ---------------------------------------
53 LCK_M_S 1383760 KEY: 8:72057594038845440 (020068e8b274)
You can easily identify the sessions that participate in the blocking chain, the resource in dispute,
how long the blocked session is waiting in milliseconds, and more.
If you need to terminate the blocker—for example, if you realize that as a result of a bug in the
application the transaction remained open and nothing in the application can close it—you can do
so by using the KILL <spid> command. (Don’t do so yet.) Note that at the date of this writing, the KILL
command is not available in SQL Database.
Earlier, I mentioned that by default the session has no lock timeout set. If you want to restrict the
amount of time your session waits for a lock, you can set a session option called LOCK_TIMEOUT. You
specify a value in milliseconds—such as 5000 for 5 seconds, 0 for an immediate timeout, and -1 for no
timeout (which is the default). To see how this option works, first stop the query in Connection 2 by
choosing Cancel Executing Query from the Query menu (or by using Alt+Break). Then run the follow-
ing code to set the lock timeout to five seconds, and run the query again.
SET LOCK_TIMEOUT 5000;
SELECT productid, unitprice
FROM Production.Products -- WITH (READCOMMITTEDLOCK)
WHERE productid = 2;
www.it-ebooks.info
Do'stlaringiz bilan baham: | 
