Systems & Databases

Balazs Berki

  • Home
  • Downloads
  • About

Monthly Archives: January 2018

Issues with monitoring open transactions: Schema lock

10th January, 2018 · Balazs Berki · Leave a comment

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.

Posted in LCK_M_SCH_S, open transaction, Schema Lock, SQL Server |

Recent Posts

  • SQL Server Failed logins: TLS Handshake issues
  • Scheduled tracing with procmon
  • Login timeouts in SQL Server due to delayed SSPI handshakes
  • A lightweight alternative to Query Store
  • Issues with monitoring open transactions: Schema lock

Archives

  • January 2020
  • May 2019
  • March 2019
  • November 2018
  • January 2018
  • April 2017
  • February 2017
  • December 2016
  • November 2016
  • October 2016
  • September 2016
  • August 2016
  • July 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016

Categories

  • ALTER TABLE MOVE
  • Avaloq
  • connectivity_ring_buffer_recorded
  • CPU
  • DATA_COMPRESSION
  • Database Crash
  • dba_users
  • emocmrsp
  • expiry_date
  • extended events
  • Full Table Scan
  • Kerberos
  • Kerberos Keytab
  • Kerberos Ticket
  • ktutil
  • LCK_M_SCH_S
  • logman
  • LogMiner
  • modelcache
  • open transaction
  • ORA-17627
  • ORA-17629
  • ORA-56904
  • Oracle
  • Oracle Client
  • Oracle client Install Script
  • Oracle Database In-Memory
  • Oracle monitoring
  • Oracle Single sign-on
  • Oracle WaitEvents
  • PerfMon
  • Pivot
  • Powershell
  • procmon
  • Python
  • Query Store
  • READ_COMMITTED_SNAPSHOT
  • Scheduled Task
  • Schema Lock
  • SQL Server
  • SSMS
  • TLS Handshake
  • wait_info_external
  • Windows Event Log

Meta

  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org
© balazsberki.com
  • Downloads
  • About