Azure SQL数据库死锁原因排查及预防方案咨询

1. 用于排查死锁的元数据查询语句 #

Azure SQL数据库提供多个系统视图和动态管理视图(DMV)排查死锁：

• 内存中死锁记录：sys.dm_tran_deadlocks可查看最近的死锁事件（数据库重启后数据丢失），示例查询：

  SELECT 
      deadlock_graph,
      CAST(deadlock_graph AS XML) AS DeadlockXML
  FROM sys.dm_tran_deadlocks;
  

• 当前锁与事务状态：关联sys.dm_tran_locks和sys.dm_exec_requests，查看等待锁的会话及相关信息：

  SELECT 
      tl.resource_type,
      tl.request_mode,
      tl.request_status,
      er.session_id,
      er.command,
      er.wait_type,
      OBJECT_NAME(tl.resource_associated_entity_id) AS LockedObject
  FROM sys.dm_tran_locks tl
  JOIN sys.dm_exec_requests er ON tl.request_session_id = er.session_id
  WHERE tl.request_status = 'WAIT';
  

• 数据库日志死锁记录：sys.event_log筛选死锁事件（适用于单数据库/弹性池）：

  SELECT 
      event_time,
      event_type,
      severity,
      message
  FROM sys.event_log
  WHERE event_type = 'deadlock'
  ORDER BY event_time DESC;
  

2. 用Azure Log Analytics（KQL）定位死锁时间与原因 #

通过已启用的Azure SQL日志，可使用以下KQL查询从AzureDiagnostics表提取死锁详情：

AzureDiagnostics
| where Category == "Deadlocks"
| project 
    EventTime = TimeGenerated,
    DeadlockReport = xml_deadlock_report_s,
    SessionID = session_id_d,
    DatabaseName = database_name_s
| order by EventTime desc
| extend DeadlockXML = parse_xml(DeadlockReport)
| project-away DeadlockReport

该查询返回死锁发生时间、涉及会话ID、数据库名，以及可解析的XML死锁图——通过死锁图能查看冲突资源、持有/等待锁的进程执行语句，定位死锁根源。

3. 确认ALTER SCHEMA是否为死锁原因的可靠方法 #

验证ALTER SCHEMA引发死锁的推测，可通过以下方式：

• 分析死锁图：从扩展事件或Log Analytics提取的死锁XML中，检查<resource-list>是否包含resource_type="SCHEMA"的资源，同时查看<process-list>中是否有执行ALTER SCHEMA的进程。
• 扩展事件捕获：创建针对死锁和架构锁的扩展事件会话，精准记录冲突场景：

  CREATE EVENT SESSION [Deadlock_SchemaLock] ON DATABASE 
  ADD EVENT sqlserver.xml_deadlock_report,
  ADD EVENT sqlserver.lock_acquired(WHERE resource_type = 'SCHEMA'),
  ADD EVENT sqlserver.lock_released(WHERE resource_type = 'SCHEMA')
  ADD TARGET package0.event_file(SET filename=N'Deadlock_SchemaLock.xel')
  WITH (STARTUP_STATE=ON);
  

  后续可查询扩展事件文件，关联死锁事件与架构锁的获取/释放记录。
• 并行场景模拟：在测试环境模拟多实体并行执行ELT流程，同时启用扩展事件跟踪，观察ALTER SCHEMA执行时的锁冲突情况。

4. 并行执行前检查架构锁定状态的sys查询 #

通过sys.dm_tran_locks查询当前是否有会话持有架构级锁（尤其是ALTER SCHEMA会申请的SCH-M锁），示例查询：

SELECT 
    tl.resource_type,
    tl.request_mode,
    OBJECT_NAME(tl.resource_associated_entity_id) AS SchemaObject,
    es.session_id,
    es.login_name,
    er.command,
    er.status
FROM sys.dm_tran_locks tl
JOIN sys.dm_exec_sessions es ON tl.request_session_id = es.session_id
LEFT JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
WHERE tl.resource_type = 'SCHEMA'
AND tl.request_mode IN ('SCH-M', 'SCH-S');

该查询返回持有架构锁的会话ID、执行命令及锁模式，可在ALTER SCHEMA执行前检查，避免并行冲突。

5. 死锁频率与数据库层级的关联 #

死锁频率与数据库层级存在直接关联：

• 更高层级的数据库（如从Basic升级到Standard/Premium，或提升vCore规格）拥有更多CPU、内存、IO资源，事务执行速度更快，锁持有时间缩短，降低锁冲突概率。
• 高级层级的数据库引擎优化更完善，锁调度机制更高效，资源竞争场景减少，间接降低死锁发生频率。你观察到升级后失败频率降低，正是这一机制的体现。

内容的提问来源于stack exchange，提问作者Priya Jha