Tuesday, December 14, 2021

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


"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






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;




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.





Posted at https://sl.advdat.com/3ymPYil