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
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
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)
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.