"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.
Microsoft
