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.

You can use this useful store proc 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 proc every seconds 20 times, so you will get a snapshot of locking, a really useful stored proc to remember.

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

The stored proc 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.