Finishing a statement does not release every lock held by its transaction. Counting locks per session shows what the transaction continues to hold. Combine the lock view with session information so you can distinguish many key locks, intent locks, and a larger object-level lock.
Build a Transaction You Can Safely Close
Use a disposable database and two SSMS query windows. The first window owns the transaction. The second inspects it while that transaction remains open. Do not leave a demonstration transaction running on a shared production table. The cleanup is part of the example, not an optional last step.
I check transaction lifetime before counting locks. A quick UPDATE inside a long application transaction can keep locks far longer than the statement's execution. The waiting request experiences that longer interval, even though the update itself looked fast.
The setup uses synthetic rows in a dedicated table. The range update changes those rows and deliberately leaves the transaction open for inspection. Note the returned session identifier. After the second-window queries finish, return to the first window and execute ROLLBACK TRANSACTION. A demonstration that forgets rollback is just a blocking incident with better documentation.
CREATE TABLE dbo.LockCountDemo(ID int NOT NULL PRIMARY KEY,Flag int NOT NULL);
WITH Numbers AS
(
SELECT TOP (10000) ROW_NUMBER() OVER(ORDER BY a.object_id,b.object_id) AS n
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
INSERT dbo.LockCountDemo SELECT CONVERT(int,n),0 FROM Numbers;
BEGIN TRANSACTION;
UPDATE dbo.LockCountDemo SET Flag=1 WHERE ID BETWEEN 1 AND 7000;
SELECT @@SPID AS OwningSession;
-- Run the inspection in another window, then execute ROLLBACK TRANSACTION here.Compare locks per session during the blocked workload rather than interpreting a quiet-server snapshot as the peak.
Count Locks per Session by Resource and Mode
sys.dm_tran_locks returns lock requests, including granted requests and requests that are waiting. Grouping only by session mixes those states. Include request_status along with resource_type and request_mode so the output says what the count represents.
KEY identifies key-level resources in a B-tree. PAGE identifies page resources. OBJECT identifies object-level resources. Modes such as S, X, IS, and IX describe shared, exclusive, and intent behavior. Intent locks show that finer-grained locks exist or are intended beneath a broader resource.
The following query keeps only user sessions and joins current session information. It filters on is_user_process, because background tasks also use identifiers above 50. open_transaction_count provides context but does not prove which statement acquired every lock. Read the resource and mode together. I keep the session's login and application nearby during real investigations, while avoiding an unnecessarily wide result when the server is already busy.
SELECT l.request_session_id,s.login_name,s.program_name,s.open_transaction_count,
l.resource_type,l.request_mode,l.request_status,COUNT_BIG(*) AS LockRequests
FROM sys.dm_tran_locks AS l
JOIN sys.dm_exec_sessions AS s ON s.session_id=l.request_session_id
WHERE s.is_user_process=1
GROUP BY l.request_session_id,s.login_name,s.program_name,s.open_transaction_count,
l.resource_type,l.request_mode,l.request_status
ORDER BY LockRequests DESC,l.request_session_id;Recognize Escalation Without Demanding It
Lock escalation replaces many fine-grained locks with a broader table lock, or an eligible partition-level lock when configured appropriately. SQL Server considers thresholds and memory pressure, but a simple row count does not guarantee that an escalation attempt succeeds. Conflicting locks can prevent the broader lock from being acquired.
The large synthetic range gives you a candidate for escalation. Inspect the resulting lock modes rather than asserting that the example must show a particular count. An OBJECT IX lock alone is an intent lock, not proof that the transaction holds an exclusive table lock. An OBJECT X lock has a different meaning.
Which resource is actually blocking the waiting request? Match its wait and requested lock to the granted incompatible lock. Many locks can coexist without blocking unrelated work. A single broader lock can block a large amount of activity. The quantity is useful context, while incompatibility and transaction duration explain the waiting.
Match Locks per Session to Waiting Work
The next query shows active requests, their blocking session identifiers, and wait details. It complements the grouped lock view by identifying who is waiting now. Filter to the demonstration session or its blocked requests when running the test. A server-wide result is unnecessary for a controlled example.
A sleeping session with an open transaction will not appear as an active request in sys.dm_exec_requests. Its locks and session record still matter. That is why the earlier join to sys.dm_exec_sessions is useful. Do not assume the absence of a current request means the session cannot block anything.
Keep the current transaction and connection context available before taking action against a session. Rolling back a real transaction can take time and has application consequences. The appropriate response is to identify the owner and resolve the transaction safely, rather than turning the largest lock count into an automatic termination list.
SELECT r.session_id,r.blocking_session_id,r.status,r.command,
r.wait_type,r.wait_time,r.wait_resource,r.open_transaction_count
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id=r.session_id
WHERE s.is_user_process=1 AND r.session_id<>@@SPID
ORDER BY r.wait_time DESC;Reduce the Transaction's Footprint Thoughtfully
Shorter transactions and appropriately selective access paths reduce unnecessary locking work. Batching a large modification can also reduce its footprint, but each committed batch changes atomicity. The application must be designed to handle partial completion and restart safely before that approach replaces a single transaction.
An index that finds the required rows directly can avoid reading and locking unrelated rows. It does not eliminate the locks needed to protect modified data. Review the actual update plan, predicates, and transaction boundaries together. A query-only change cannot compensate for an application that waits for human input before committing.
Do not disable escalation as a reflex. Retaining large numbers of fine-grained locks consumes memory and can create another problem. Treat escalation settings as a specific design decision. Newer locking behavior also changes the shape of observed locks, so interpret your server's configuration rather than expecting every installation to match one screenshot.
Sample Sparingly and Finish the Demonstration
Querying sys.dm_tran_locks on a busy server has its own cost. Use a narrow investigation window, capture the relevant sessions, and avoid a rapid polling loop that repeatedly scans all lock requests. The DMV reports one moment, not a complete timeline of acquisitions and releases.
Return to the first test window and roll back the open transaction. Then rerun the grouped inspection for that session and confirm its demonstration locks are gone. Remove the dedicated table when the disposable test is finished. Check that no other test window still holds a transaction.
For production review, save timestamped snapshots and the related waits instead of presenting one locks per session result as history. The useful conclusion identifies the transaction boundary, the incompatible resources, and the reason the owner kept them. Counting is the first step toward that explanation, not the explanation itself.
Related reading on this blog: Diagnosing Blocking Chains With sys.dm_exec_requests and Locking, Blocking, and Deadlocking: Differences, Similarities, and Best Practices.
A lock count is not a blocking history, it is a snapshot of resources held or requested.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
