Create table and and insert into within same transaction blocks sys.tables catalog view

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

 

"Transaction Isolation Level" controls the locking and row versioning behaviour of Transact-SQL statements issued by a connection to SQL Server.

By default SQL server and  Azure SQL SQL Transaction Isolation Level is set to “READ COMMITTED” , this means that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default

 

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15

 

Palomag_MSFT_0-1638282771306.png

 

So what happens if you create an explicit transaction to create a table and also INSERT INTO records?  let me show you with a simple test

 

Open an explicit transaction to create the table

 

begin tran create table dbo.isolationleveltest (col1 varchar)

 

 

While the transaction is not ended sys.Tables catalog with be blocked, and if you execute "SELECT * FROM sys.Tables" execution will never end.

 

If you check database block with bellow query, you will be able to see that the is a lock between both query’s.  In same output you can also see that Isolation Level is set to 2  (READ COMMITTED)

 

 

WITH cteBL (session_id, blocking_these) AS (SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s CROSS APPLY (SELECT isnull(convert(varchar(6), er.session_id),'') + ', ' FROM sys.dm_exec_requests as er WHERE er.blocking_session_id = isnull(s.session_id ,0) AND er.blocking_session_id <> 0 FOR XML PATH('') ) AS x (blocking_these) ) SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these , batch_text = t.text, input_buffer = ib.event_info, * FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id INNER JOIN cteBL as bl on s.session_id = bl.session_id OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib WHERE blocking_these is not null or r.blocking_session_id > 0 ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;

 

Palomag_MSFT_3-1638283559626.png

 

As soon you close transaction with COMMIT TRAN, lock with end and "SELECT * FROM sys.Tables" will return you tables list

 

So if you use a single transaction to create a table and INSERT INTO  records, "sys.tables" system catalog will be blocked and other queries that need to access to the same catalog will be waiting until you free the lock. 

 

Conclusion: Try to avoid exclusive locks in system tables for the duration of the transaction.

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.