sql server数据库死锁

Question

问题：没有操作数据库，但是用select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT'语句查询时，一会有数据一会没有数据，是为什么呢

Answer 1

引自免费微信小程序：皆我百晓生

关于SQL Server数据库中死锁的情况以及使用sys.dm_tran_locks动态管理视图查询锁定信息时出现数据不稳定的现象，可以归结为SQL Server内部事务处理机制与查询时刻的关联。

当你说“一会有数据一会没有数据”，这实际上反映了SQL Server在处理多个并发事务时的动态行为。sys.dm_tran_locks视图提供了当前数据库中所有锁定信息的快照，包括锁定类型、锁定模式、锁定资源等。这些信息随时间变化，因为事务可能正在请求锁定、持有锁定或释放锁定。

要深入理解这个问题，我们首先回顾一下sys.dm_tran_locks视图的结构和功能：

• request_session_id：发起锁定请求的会话ID。
• resource_type：被锁定资源的类型，如OBJECT表示表或索引。
• resource_associated_entity_id：锁定资源的ID，如表的object_id。

当你看到数据时，这意味着存在至少一个事务正在等待锁定或已经获取了锁定。而当你看不到数据时，可能是因为所有事务都已成功完成其操作并释放了锁定，或者当前没有事务请求任何锁定。

为了更好地理解和诊断这种情况，你可以采取以下步骤：

1. 定期监控：设置定时任务或使用SQL Server代理作业，定期执行上述查询，将结果记录到日志或表中，以便后续分析。
2. 死锁分析：启用SQL Server的死锁跟踪，通过配置deadlock tracing选项，或使用sp_trace_setevent和sp_trace_setstatus存储过程来启动死锁跟踪。这样，每当发生死锁时，系统会自动记录相关信息。
3. 事务优化：检查并优化长运行事务，确保它们不会无谓地占用资源。考虑使用更细粒度的锁定策略，减少锁定范围，或调整事务隔离级别。
4. 查询优化：确保查询效率，避免不必要的全表扫描或长时间锁定数据行。

此外，使用sys.dm_os_wait_stats和sys.dm_exec_requests等动态管理视图，可以帮助你进一步了解系统等待状态和当前请求，从而定位潜在的性能瓶颈。

示例查询，用于扩展监控：

WITH Locks AS (
    SELECT 
        request_session_id,
        OBJECT_NAME(resource_associated_entity_id) AS TableName,
        lock_mode,
        wait_resource,
        request_status
    FROM 
        sys.dm_tran_locks
)
SELECT 
    L.request_session_id,
    L.TableName,
    L.lock_mode,
    L.wait_resource,
    L.request_status,
    R.status,
    R.command,
    R.login_time,
    R.cpu_time,
    R.total_elapsed_time
FROM 
    Locks L
JOIN 
    sys.dm_exec_requests R ON L.request_session_id = R.session_id;

此查询结合了sys.dm_tran_locks和sys.dm_exec_requests视图，提供了更全面的事务和锁定信息。

记住，持续监控和适时调整是保持数据库性能和稳定性的关键。如果死锁或锁定问题持续影响系统性能，可能需要更深入地分析具体的应用逻辑和数据访问模式，以找到根本原因并实施相应的解决方案。