Transaction cause freezing entire database in SQL Server

Question

I was working mostly on PostgreSQL, but recently I was assigned to project with SqlServer and I encountered very strange behavior of this engine. I am using transaction in my code and connect with server via System.Data.SqlClient library. The code in transaction is approximately 1000 lines long, so I would like to not copy it here, but transaction is handled via code below:

using (var transaction = connection.BeginTransaction(IsolationLevel.ReadCommited)) 
{
    //here code goes
    //1. inserting new table metadata via inserts and updates 
    //2. creating new tables according to users project
    //3. post execute actions via inserts and updates
    
    //here is intended transaction freeze
    await Task.Delay(1000 * 60 * 2);
}

During this execution I cannot perform any operation on database (query execution in SSMS or some code execution in application - doesn't matter). Simple selects f.e. SELECT * FROM "TableA" hangs, retrieving database properties in SSMS hangs etc. Any independent query waits for this one transaction to be completed.

I found several articles and answers here on SO, and based on those I tried following solutions:

1. Use WITH (NOLOCK) or WITH (READPAST) in SELECT statement
2. Changing database property Is Read Commited Snapshot ON to true
3. Changing transaction isolation level in code (all possible levels were tested)

None of the above solutions works. I tested on 3 different computers: desktop, two laptops - the same behavior (SqlServer and SSMS was installed with default options).

In this thread: Understanding locking behavior in SQL Server there are some explanation of transaction isolation levels and locks but the problem is that WITH (NOLOCK) doesn't work for me as mentioned in 1).

This is very big problem for me, because my asynchronous application works synchronously because of that weird locks. Oracle and postgres databases works perfectly fine, the problem concerns SqlServer only.

I don't use EntityFramework - I handle connections myself.

Windows 10 Pro

Microsoft SQL Server Developer (64-bit) version 15.0.2000.5

System.Data.SqlClient version 4.8.3

.NET 6.0

Any clues?

Update 1: As pointed out in comments I have indeed schema changes in my transaction - CREATE TABLE and ALTER TABLE statements mixed with standard UPDATES and SELECTS. My app allows user to create own tables (in limited functionality) and when this table is registered in table via INSERT there are some CREATES to adjust table structure.

Update 2: I can perform SELECT * FROM sys.dm_tran_locks I executed DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR); The problem remains.

Answer 1

The cause of the locking issue is DDL (CREATE TABLE, etc.) within a transaction. This will acquire and hold restrictive locks on system table meta-data and block other database activity that need access to object meta-data until the transaction is committed.

This is an app design problem as one should not routinely execute DDL functions in application code. If that design cannot be easily remediated, perform the DDL operations separately in a short transaction (or with utocommit statements without an explict transaction) and handle DDL rollback in code.