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.

Answer 2

You can use this useful stored procedure which I picked up somewhere along my travels. It recently helped me see the locking on a table and showed that after setting READ UNCOMMITED it was no longer doing row/page/table locks, but still had the schema lock. I believe you may have schema locks if you are modifying them! and also as commented don't keep a transaction open long, in and out is key.

What this does is runs the stored procedure every seconds 20 times, so you will get a snapshot of locking, a really useful stored procedure to remember.

EXEC [dbo].CheckLocks @Database = 'PriceBook'
WAITFOR DELAY '00:00:01'
GO 20

The stored procedure is as follows:

/*
This script can be run to find locks at the current time

We can run it as follows:

EXEC [dbo].CheckLocks @Database = 'PriceBook'
WAITFOR DELAY '00:00:01'
GO 10

*/

CREATE OR ALTER PROCEDURE [dbo].[CheckLocks]
    @Database NVARCHAR(256)
AS
BEGIN
    -- Get the sp_who details
    IF object_id('tempdb..#WhoDetails') IS NOT NULL
    BEGIN
        DROP TABLE #WhoDetails
    END

    CREATE TABLE #WhoDetails (
        [spid] INT,    
        [ecid] INT,    
        [status] VARCHAR(30),    
        [loginame] VARCHAR(128),
        [hostname] VARCHAR(128),
        [blk] VARCHAR(5),
        [dbname] VARCHAR(128),
        [cmd] VARCHAR(128),
        [request_id] INT
    )

    INSERT INTO #WhoDetails EXEC sp_who

    -- Get the sp_lock details
    IF object_id('tempdb..#CheckLocks') IS NOT NULL
    BEGIN
        DROP TABLE #CheckLocks
    END

    CREATE TABLE #CheckLocks (
        [spid] int,    
        [dbid] int,    
        [ObjId] int,    
        [IndId] int,    
        [Type] char(4),    
        [Resource] nchar(32),    
        [Mode] char(8),    
        [Status] char(6)
    )

    INSERT INTO #CheckLocks EXEC sp_lock

    SELECT DISTINCT
    W.[loginame],
    L.[spid], 
    L.[dbid], 
    db_name(L.dbid) AS [Database], 
    L.[ObjId], 
    object_name(objID) AS [ObjectName],
    L.[IndId], 
    L.[Type], 
    L.[Resource], 
    L.[Mode], 
    L.[Status]--, 
    --ST.text, 
    --IB.event_info
    FROM #CheckLocks AS L
    INNER JOIN #WhoDetails AS W ON W.spid = L.spid
    INNER JOIN sys.dm_exec_connections AS EC ON EC.session_id = L.spid
    --CROSS APPLY sys.dm_exec_sql_text(EC.most_recent_sql_handle) AS ST
    --CROSS APPLY sys.dm_exec_input_buffer(EC.session_id, NULL) AS IB -- get the code that the session of interest last submitted
    WHERE L.[dbid] != db_id('tempdb')
    AND L.[Type] IN ('PAG', 'EXT', 'TAB')
    AND L.[dbid] = db_id(@Database)

    /*
    https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql?view=sql-server-ver15
    
    Lock modes are as follows
    ------------------------------
    S = Shared
    U = Update
    X = Exclusive
    IS = Indent Shared
    IS = Intent Update
    IX = Intent Exclusive
    Sch-S = Schema Stability lock so no we cant remove tables or indexes in use

    Lock Type are as follows:
    ------------------------------
    RID = Single row lock
    KEY = Lock within an index that protects a range of keys
    PAG = Page level lock
    EXT = Extend Lock
    TAB = Table Lock
    DB = Database lock

    */

END

This is what you might see if you can catch the locking, this was before an after example, left and right.