I recently came across a scenario whereby we wanted to programmatically, via T-SQL, inspect locking behaviour on objects in a particular database when operations were being performed on given data (actually just a SELECT (WITH (NOLOCK) and DELETE statement, on a table). The requirement was to get a direct view on whether the table was having an exclusive lock thrust upon it, or whether shared access was still in play (and get an insight into whether we were in the realms of row-level locking or not).

This turns out to be relatively easy through the use of the inbuilt Dynamic Management Views; specifically the sys.dm_tran_locks view, coupled with a cheeky join to sys.objects for information to identify the object being referenced (this is my own interpretation, from scratch, but you can easily find many similar examples so pick what floats your boat!):

/*
	Interrogate the lock types at database level (can see the lock type on a table when another statement is running)
	-------------------------------------------------------------------------------------------------------------------

	1) For information on the different lock modes in SQL Server visit the following resources:
		a) https://technet.microsoft.com/en-us/library/ms175519(v=sql.105).aspx
		b) http://lockergnome.com/2010/01/26/what-are-the-different-lock-modes-in-sql-server/
		c) https://logicalread.com/sql-server-lock-modes-mc03/ - Haven't actually read this one yet but looked intriguing enough to include as a 'bonus' :o)
	2) An interesting aside into the WITH (NOLOCK) hint: http://sqlsoldier.net/wp/sqlserver/thetruthaboutnolockhints
	3) The difference by IX and X locks: http://database.ittoolbox.com/groups/technical-functional/sql-server-l/difference-between-x-and-ix-lock-151903
*/
SELECT 
tl.resource_type AS [ResourceType]
, CASE 
	WHEN tl.request_mode = 'Sch-S'
	THEN 'Schema Stability (ensure entity cannot be dropped)'
	WHEN tl.request_mode = 'IS'
	THEN 'Intent Shared'
	WHEN tl.request_mode = 'IX'
	THEN 'Intent Exclusive (exclusive lock to subordinate resource in the lock hierarchy - table can still be accessed in part)'
	WHEN tl.request_mode = 'X'
	THEN 'Exclusive (full, exclusive access to the resource)'
	ELSE tl.request_mode
END AS [LockType]
, so.[object_id] AS [ObjectId] 
, so.name AS [ObjectName]
, so.type_desc AS [TypeDesc]
FROM sys.dm_tran_locks tl
	-- JOIN to sys.objects to get identifiable information on the resource marked with a lock
	INNER JOIN sys.objects so ON tl.resource_associated_entity_id = so.object_id
-- Show lock information for the database in scope (this could be parameterised - this could be placed in an stored procedure, for example)
WHERE tl.resource_database_id = DB_ID();

In the comments above the code snippet, I’ve included a couple of links to detail the various lock types (information that can be shown in the request_mode view column) that you may encounter. Just run the query whilst a transaction that you want to inspect (for locking activity) is in progress, and voila, you’ll get an idea as to how the resources are being accessed and what impact this is having locking wise.

As a quick illustration let’s knock up a test database and table, using the following script which incorporates a very trivial setup:

-- Test script for checking out the dm_tran_locks test script

-- 1) Create the test database
CREATE DATABASE [LockTestDatabase];
GO

USE [LockTestDatabase];

-- 2) Create the LockTest table within the LockTestDatabase database (super noddy example for illustration only)
CREATE TABLE [dbo].[LockTest]
(
	Id				INT				PRIMARY		KEY	IDENTITY(1, 1)	NOT NULL
	, SomeInfo		NVARCHAR(20)									NOT NULL
);

-- 3) Create some simple test data
DECLARE
@Counter		INT = 1;

WHILE (@Counter <= 250000)		-- Want a non-trivial amount of data so queries take a couple of seconds to complete
BEGIN

	PRINT (@Counter);

	INSERT INTO [dbo].[LockTest]
	(
		SomeInfo
	)
	VALUES
	(
		'Information' + CONVERT(NVARCHAR(6), @Counter)
	);

	SET 
	@Counter += 1;

END;

The insertion of test records takes a short while so it’s possible to run the dm_tran_locks test script and obtain the following results:

After the test insertions are complete, attempt to run a SELECT statement using the WITH (NOLOCK) hint:

SELECT 
lt.Id
, lt.SomeInfo
FROM dbo.LockTest lt WITH (NOLOCK);

You should hopefully observe a shared resource lock, for schema stability only, that essentially just prevents the resource from being dropped whilst information is being requested from the table:

I’m not going into any kind of detailed explanation with regards to the lock types here, I’m just proving that observing the lock types being applied is easily possible. Lastly, attempt a deletion (wrapped in a rollback, so it can easily be re-run):

BEGIN TRANSACTION

	DELETE
	FROM dbo.LockTest;

ROLLBACK TRANSACTION

A lock is applied that indicates exclusive access to the resource (due to the nature of the delete here, as all rows are being removed), aka table:

This gives you an insight into how straightforward it is to get an impression on how your SQL objects are being influenced, locking wise, by your code. If anyone has any suggestions on further reading, more useful snippets, etc. then please add a comment below. Thanks, all.