Find the locks

There are many blog posts on the internet that have explained time and again regarding this topic. Many of them are pretty basic stuff while some of them are quite advanced. A while back I myself wrote a blog post explaining how to find blocking sessions in Oracle database. These posts usually talk about activity monitor, sp_who2, sysprocesses, etc.

A simple query to provide us information regarding locks in SQL Server would look like this

SELECT * FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL
GO

This code block is quite helpful because looking carefully at the query output tells us who is waiting for whom which lets us know exactly what is to be done for the lock to be removed.

Although this query is already sufficient in itself, I wanted a query output which would help to understand the locks visually for all sorts of stakeholders, when I as a DBA, am requested for such information. And even before I could write such query I found a blog post from Pinal Dave having such a query which is shown below

SET NOCOUNT ON
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE(T.TEXT, CHAR(10), ‘ ‘), CHAR (13), ‘ ‘ ) AS BATCH
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE (‘0′, 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED = R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 +R.SPID) AS VARCHAR (100)), 4) AS VARCHAR(1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED =BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED = R.SPID
)
SELECT N’ ‘ + REPLICATE (N’| ‘,LEN (LEVEL)/4 – 1) +
CASE WHEN (LEN(LEVEL)/4 – 1) = 0
THEN ‘HEAD – ‘
ELSE ‘|—— ‘ END
+ CAST (SPID AS NVARCHAR (10)) + N’ ‘ + BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO

The query output from the above code block gives a visual representation of the block that is present in the database which can be called a blocking tree. This piece of code is my daily go to code for finding locks and their respective locking sessions in the database.

Let me know what codes or tools do you use to find locks in your database.

This entry was posted in Uncategorized and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s