|
CHAPTER 9
Transactions and Concurrency
303
Troubleshooting Blocking
When one transaction holds a lock on a data resource and another transaction requests an incom-
patible lock on the same resource, the request is blocked and the requester enters a wait state. By
default, the blocked request keeps waiting until the blocker releases the interfering lock. Later in
this section, I’ll explain how you can define a lock expiration time-out in your session if you want to
restrict the amount of time that a blocked request waits before it times out.
Blocking is normal in a system as long as requests are satisfied within a reasonable amount of time.
However, if some requests end up waiting too long, you might need to troubleshoot the blocking
situation and see whether you can do something to prevent such long latencies. For example, long-
running transactions result in locks being held for long periods. You can try to shorten such transac-
tions, moving activities that are not supposed to be part of the unit of work outside the transaction. A
bug in the application might result in a transaction that remains open in certain circumstances. If you
identify such a bug, you can fix it and ensure that the transaction is closed in all circumstances.
This section demonstrates a blocking situation and walks you through the process of trouble-
shooting it. Note that this demonstration assumes that you’re connected to an on-premises SQL
Server instance and using the READ COMMITTED isolation level, meaning that by default SELECT
statements will request a shared lock. Remember that in SQL Database the default isolation is READ
COMMITTED SNAPSHOT, in which SELECT statements do not ask for a shared lock by default. If you
want to run the demo in SQL Database, to work under READ COMMITTED, you will need to add a
table hint called READCOMMITTEDLOCK to your SELECT statements, as in SELECT * FROM T1 WITH
(READCOMMITTEDLOCK). Also, by default, connections to SQL Database time out quite quickly. So
if a demo you’re running doesn’t work as expected, it could be that a connection involved in that
demo timed out.
Open three separate query windows in SQL Server Management Studio. (For this example, I will
refer to them as Connection 1, Connection 2, and Connection 3.) Make sure that in all of them you are
connected to the sample database TSQL2012.
USE TSQL2012;
Run the following code in Connection 1 to update a row in the Production.Products table, adding
1.00 to the current unit price of 19.00 for product 2.
BEGIN TRAN;
UPDATE Production.Products
SET unitprice += 1.00
WHERE productid = 2;
To update the row, your session had to acquire an exclusive lock, and if the update was success-
ful, SQL Server granted your session the lock. Recall that exclusive locks are kept until the end of the
transaction, and because the transaction remains open, the lock is still held.
www.it-ebooks.info
304
Microsoft SQL Server 2012 T-SQL Fundamentals
Run the following code in Connection 2 to try to query the same row (uncomment the hint WITH
(READCOMMITTEDLOCK) in this and subsequent queries if you’re running this on SQL Database).
SELECT productid, unitprice
FROM Production.Products -- WITH (READCOMMITTEDLOCK)
WHERE productid = 2;
Your session needs a shared lock to read the data, but because the row is exclusively locked by the
other session, and a shared lock is incompatible with an exclusive lock, your session is blocked and has
to wait.
Assuming that such a blocking situation happens in your system, and the blocked session ends up
waiting for a long time, you probably want to troubleshoot the situation. The rest of this section pro-
vides queries against dynamic management objects, including views and functions, that you should
run from Connection 3 when you troubleshoot the blocking situation.
To get lock information, including both locks that are currently granted to sessions and locks that
sessions are waiting for, query the dynamic management view (DMV) sys.dm_tran_locks in Connec-
tion 3.
SELECT -- use * to explore other available attributes
request_session_id AS spid,
resource_type AS restype,
resource_database_id AS dbid,
DB_NAME(resource_database_id) AS dbname,
resource_description AS res,
resource_associated_entity_id AS resid,
request_mode AS mode,
request_status AS status
FROM sys.dm_tran_locks;
When I run this code in my on-premises system (with no other query window open), I get the fol-
lowing output.
spid restype dbid dbname res resid mode status
---- -------- ---- --------------------- -------------- ----------------- ---- ------
53 DATABASE 8 TSQL2012 0 S GRANT
52 DATABASE 8 TSQL2012 0 S GRANT
51 DATABASE 8 TSQL2012 0 S GRANT
54 DATABASE 8 TSQL2012 0 S GRANT
53 PAGE 8 TSQL2012 1:127 72057594038845440 IS GRANT
52 PAGE 8 TSQL2012 1:127 72057594038845440 IX GRANT
53 OBJECT 8 TSQL2012 133575514 IS GRANT
52 OBJECT 8 TSQL2012 133575514 IX GRANT
52 KEY 8 TSQL2012 (020068e8b274) 72057594038845440 X GRANT
53 KEY 8 TSQL2012 (020068e8b274) 72057594038845440 S WAIT
Each session is identified by a unique server process ID (SPID). You can determine your session’s
SPID by querying the function @@SPID. If you’re working with SQL Server Management Studio, you
will find the session SPID in parentheses to the right of the logon name in the status bar at the bot-
tom of the screen, and also in the caption of the connected query window. For example, Figure 9-1
www.it-ebooks.info
Do'stlaringiz bilan baham: | 
