Monitoring open transactions is beneficial for the performance of a database. There are many nice SQL queries which show the locked objects but there might be some special situations where these queries do not work. They get blocked by the open transaction of another session. Such a scenario can occur when one of the sessions has a schema lock.
First lets create a row lock on a table:
CREATE TABLE [dbo].[testtable11]([col1] [int] NULL)
BEGIN TRAN
insert into TestDB1.dbo.testtable1 values (2)
In another session we can then execute the below SQL to look for open transactions:
DECLARE @thresholdMin INT = 1 --the transaction needs to be open since @thresholdMin minutes
SELECT @@SERVERNAME as inst_fullname
, DB_NAME(dt.database_id) AS db_name
, de.login_name
, de.host_name
, de.program_name
, de.client_interface_name
, ds.session_id
, de.last_request_start_time
, de.last_request_end_time
, dt.database_transaction_state
, dt.database_transaction_begin_time
, da.transaction_begin_time
, OBJECT_SCHEMA_NAME(resource_associated_entity_id, resource_database_id) + '.' + Object_Name(resource_associated_entity_id, resource_database_id) as locked_object
FROM sys.dm_tran_database_transactions (nolock) dt
LEFT JOIN sys.dm_tran_active_transactions (nolock) da ON da.transaction_id = dt.transaction_id
LEFT JOIN sys.dm_tran_session_transactions (nolock) ds ON ds.transaction_id = dt.transaction_id
LEFT JOIN sys.dm_exec_sessions (nolock) de ON de.session_id = ds.session_id
left join sys.dm_tran_locks (nolock) li on li.request_session_id = ds.session_id
WHERE ds.is_user_transaction = 1
AND ISNULL(dt.database_transaction_begin_time, da.transaction_begin_time) < DATEADD(MINUTE, -@thresholdMin, GETDATE()) AND dt.database_id <> 32767
AND database_transaction_state <> 3
AND li.resource_type = 'OBJECT'
Our open transaction check works perfectly, we can see in the locked_object column which object is locked. Until this point we only have a row or a table lock.
Now we create a schema lock by creating a table in our session with the uncommitted transaction:
create table #testtemp1 (val1 int)
Now when we execute our check query again it will not finish. Lets see what is happening in the instance:
--Current db activity
select db_name(database_id) as dbname,command, substring(text, statement_start_offset / 2,
(case when statement_end_offset = -1 then datalength(text)
else statement_end_offset end - statement_start_offset) / 2) as query_text, percent_complete, start_time,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time ,
user_name(user_id) as userid,session_id,
status, blocking_session_id, wait_type,cpu_time, reads, writes, open_transaction_count, text,*
from sys.dm_exec_requests
cross apply sys.dm_exec_sql_text(sql_handle)
userid session_id status blocking_session_id wait_type wait_resource
------ ---------- ---------- ------------------- ----------- --------------------
dbo 54 suspended 0 WAITFOR
dbo 57 suspended 52 LCK_M_SCH_S OBJECT: 2:-1152441745:0
dbo 58 running 0 NULL
Our monitoring SQL is running in the session with ID 57 and is blocked by the session of the open transaction (Session 52). This wait type LCK_M_SCH_S suggests that this is a schema lock. So lets find out more about the following Object: 2:-1152441745:0
The first number wil be a database ID:
select DB_NAME(2)
-----------------
tempdb
In our session with the open transaction, we can see that -1152441745 is an object ID for a temporary object:
use tempdb
go
select object_name(-1152441745)
------------------------------
testtemp1______________________________________________________________________________________00000000EE4A
So now we can see that the open transaction with a schema lock is blocking our monitoring SQL. If we execute the above query in any other session then it gets blocked as well. A schema lock will always block these type of queries, it does not depend on READ_COMMITTED_SNAPSHOT or SET ALLOW_SNAPSHOT_ISOLATION being enabled or disabled.
In order to monitor open transactions and avoid running into a schema lock, our monitoring SQL needs to be able to identify a schema lock first. We can use the request_mode column of the sys.dm_tran_locks view for this. While our transaction is still active, we can check the contents of this view in another session:
select distinct(request_mode) from sys.dm_tran_locks
request_mode
-----------
IX
S
Sch-M
Sch-S
X
Currently we are interested in schema locks which are the ones beginning with “sch-“. Now we can distinguish between schema locks and object locks in our new query by using the CASE clause as follows:
DECLARE @thresholdMin INT = 1
SELECT @@SERVERNAME as inst_fullname
, DB_NAME(dt.database_id) AS db_name
, de.login_name
, de.host_name
, de.program_name
, de.client_interface_name
, ds.session_id
, de.last_request_start_time
, de.last_request_end_time
, dt.database_transaction_state
, dt.database_transaction_begin_time
, da.transaction_begin_time
, CASE WHEN request_mode like 'Sch%'
THEN 'Schema lock due to entity_id: ' + CONVERT(NVARCHAR(MAX), resource_associated_entity_id) + 'resource_database_id: ' + CONVERT(NVARCHAR(MAX),resource_database_id)
ELSE OBJECT_SCHEMA_NAME(resource_associated_entity_id, resource_database_id) + '.' + Object_Name(resource_associated_entity_id, resource_database_id)
END as locked_object
FROM sys.dm_tran_database_transactions (nolock) dt
LEFT JOIN sys.dm_tran_active_transactions (nolock) da ON da.transaction_id = dt.transaction_id
LEFT JOIN sys.dm_tran_session_transactions (nolock) ds ON ds.transaction_id = dt.transaction_id
LEFT JOIN sys.dm_exec_sessions (nolock) de ON de.session_id = ds.session_id
left join sys.dm_tran_locks (nolock) li on li.request_session_id = ds.session_id
WHERE ds.is_user_transaction = 1
AND ISNULL(dt.database_transaction_begin_time, da.transaction_begin_time) < DATEADD(MINUTE, -@thresholdMin, GETDATE()) AND dt.database_id <> 32767
AND database_transaction_state <> 3
AND li.resource_type = 'OBJECT'
Now we can execute the above monitoring SQL. This query can identify schema locks and instead of looking for an object name it will display information about the schema lock itself.