SQL Server中事务导致整个数据库冻结的问题求助

问题场景 #

此前使用PostgreSQL，接手SQL Server项目时遇到异常：通过System.Data.SqlClient连接数据库，事务内包含以下操作：

• 插入/更新表元数据（INSERT/UPDATE）
• 根据用户项目创建新表（CREATE TABLE）
• 后续数据操作（INSERT/UPDATE）

事务代码框架：

using (var transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)) 
{
    // 1. 插入/更新表元数据
    // 2. 根据用户项目创建新表
    // 3. 执行后续插入/更新操作
    
    // 模拟事务延迟
    await Task.Delay(1000 * 60 * 2);
}

执行该事务期间，数据库所有操作均被阻塞：SSMS中简单查询SELECT * FROM "TableA"挂起、查看数据库属性挂起，所有独立查询必须等待事务完成。

已尝试无效方案 #

• 在SELECT语句中使用WITH (NOLOCK)或WITH (READPAST)
• 开启数据库Is Read Commited Snapshot属性
• 修改事务隔离级别（测试所有可用级别）

上述方案在3台不同设备（台式机+两台笔记本）的默认SQL Server/SSMS配置下均无效。

补充信息 #

• Update 1：事务中混合了DDL操作（CREATE TABLE、ALTER TABLE）与常规DML（UPDATE、SELECT），业务逻辑允许用户创建自定义表，注册后执行CREATE语句调整结构。
• Update 2：已执行SELECT * FROM sys.dm_tran_locks、DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);排查锁与等待信息，但问题未解决。
• 环境：Windows 10 Pro、SQL Server Developer 15.0.2000.5（64位）、System.Data.SqlClient 4.8.3、.NET 6.0

核心原因分析 #

SQL Server中DDL操作（如CREATE TABLE/ALTER TABLE）会持有SCH-M（架构修改）锁，这种锁是排他性的，会阻塞所有对数据库的访问请求——包括普通数据查询、元数据读取（如查看数据库属性）。SCH-M锁的优先级高于所有数据锁，因此NOLOCK、快照隔离等针对数据锁的优化方案完全无效，这也是Oracle和PostgreSQL无此问题的核心差异点。

解决思路 #

1. 拆分事务，分离DDL与长时业务逻辑 #

将事务拆分为两个独立部分：

• 第一事务：仅执行元数据DML + DDL操作，完成后立即提交（尽可能缩短SCH-M锁持有时间）
• 第二事务：执行后续的长时业务逻辑与DML操作

2. 绝对避免在长事务中包含DDL操作 #

事务中的Task.Delay(120秒)会让SCH-M锁持续持有2分钟，这是阻塞的关键。DDL操作必须放在极简事务中，执行完成立即提交，绝不与耗时操作（如延迟、复杂计算）绑定。

3. 精准排查SCH-M锁 #

执行以下语句定位锁的持有者与关联对象，确认是否有冗余DDL操作：

SELECT 
    request_session_id AS 会话ID,
    resource_type AS 锁资源类型,
    resource_associated_entity_id AS 关联对象ID,
    request_mode AS 锁模式
FROM sys.dm_tran_locks 
WHERE request_mode = 'SCH-M'

4. 优化DDL操作的影响范围（若适用） #

• 对于ALTER TABLE操作，SQL Server Enterprise版支持部分操作的ONLINE = ON选项，可减少锁的影响范围
• CREATE TABLE本身无在线选项，只能通过缩短事务时长来降低阻塞

内容的提问来源于stack exchange，提问作者Adam Mrozek