sql server 查看死锁，以及执行语句

第二秒

select t1.resource_type [资源锁定类型], DB_NAME(resource_database_id)as 数据库名, t1.resource_associated_entity_id锁定对象, t1.request_modeas 等待者请求的锁定模式, t1.request_session_id 等待者SID, t2.wait_duration_ms 等待时间, (select TEXTfrom sys.dm_exec_requests rcross applysys.dm_exec_sql_text(r.sql_handle)where r.session_id = t1.request_session_id) as 等待者要执行的SQL, t2.blocking_session_id[锁定者SID], (select TEXTfrom sys.sysprocesses pcross applysys.dm_exec_sql_text(p.sql_handle)where p.spid = t2.blocking_session_id) 锁定者执行语句from sys.dm_tran_locks t1,sys.dm_os_waiting_tasks t2where t1.lock_owner_address = t2.resource_address

sys.dm_tran_locks (Transact-SQL)

适用于：SQL Server（所有支持的版本）Azure SQL 数据库Azure SQL 托管实例Azure Synapse Analytics并行数据仓库

返回SQL Server (15.x)中有关当前活动的锁管理器资源的信息。向锁管理器发出的已授予锁或正等待授予锁的每个当前活动请求分别对应一行。

结果集中的列大体分为两组：资源组和请求组。资源组说明正在进行锁请求的资源，请求组说明锁请求。

备注

若要从或调用此Azure Synapse Analytics（SQL 数据仓库）并行数据仓库，请使用名称dm_pdw_nodes_tran_locks。

权限

在上SQL Server，需要VIEW SERVER STATE权限。

在SQL 数据库高级层上，需要具有VIEW DATABASE STATE数据库中的权限。在SQL 数据库标准层和基本层上，需要服务器管理员或Azure Active Directory 管理员帐户。

注解

已授予请求状态指示已将资源上的锁授予请求程序。等待请求指示尚未授予请求。request_status列返回下列等待请求类型：

由于sys.dm_tran_locks从锁管理器的内部数据结构填充，因此维护该信息不会给常规处理带来额外的开销。具体化该视图需要访问锁管理器的内部数据结构。这可能会略微影响服务器中的常规处理。这些影响应该很难察觉，并且应该只会影响频繁使用的资源。由于该视图中的数据与活动的锁管理器状态相对应，因此该数据可能会随时更改，并且在获取和释放锁时会相应地添加和删除行。该视图不包含历史信息。

仅当所有资源组列都相等时，才对同一资源执行两个请求。

您可以使用下列工具控制读取操作的锁定：

使用一个会话 ID 运行的资源可以有多个已授予锁。在一个会话下运行的不同实体可以拥有同一资源的锁，并且相关信息显示在sys.dm_tran_locks所返回的request_owner_type和request_owner_id列中。如果存在属于同一request_owner_type的多个实例，则使用request_owner_id列区分每个实例。对于分布式事务，request_owner_type和request_owner_guid列将显示不同的实体信息。

例如，会话 S1 拥有Table1的共享锁，而在会话 S1 下运行的事务 T1 也拥有Table1的共享锁。在这种情况下，sys.dm_tran_locks所返回的resource_description列将显示同一资源的两个实例。request_owner_type列将其中一个实例显示为会话，将另一个实例显示为事务。此外，resource_owner_id列将具有不同的值。

在一个会话下运行的多个游标无法区分，被视为一个实体。

与会话 ID 值没有关联的分布式事务是孤立事务，向该事务分配的会话 ID 值为 -2。有关详细信息，请参阅KILL (Transact-SQL)。

住

锁加在SQL Server资源上（如在一个事务中读取或修改的行），以防止各种事务并发使用资源。例如，如果一个排它 (X) 锁被一个事务加在某一表的某一行上，在这个锁被释放前，其他事务都不可以修改这一行。尽可能少使用锁可提高并发性，从而改善性能。

资源详细信息

下表列出了在resource_associated_entity_id列中表示的资源。

下表列出了与每个资源类型相关联的子类型。

下表提供每个资源类型的resource_description列的格式。

以下 XEvents 与分区切换和联机索引重新生成相关。有关语法的信息，请参阅ALTER TABLE (transact-sql)和Alter INDEX (transact-sql)。

通过添加partition_number和partition_id扩展了联机索引操作的现有 XEventprogress_report_online_index_operation。

示例

A.将 sys.dm_tran_locks 与其他工具一起使用

以下示例处理更新操作被另一个事务阻塞的情况。使用sys.dm_tran_locks和其他工具，可提供有关锁定资源的信息。

SQL复制

USE tempdb; GO -- Create test table and index. CREATE TABLE t_lock ( c1 int, c2 int ); GO CREATE INDEX t_lock_ci on t_lock(c1); GO -- Insert values into test table INSERT INTO t_lock VALUES (1, 1); INSERT INTO t_lock VALUES (2,2); INSERT INTO t_lock VALUES (3,3); INSERT INTO t_lock VALUES (4,4); INSERT INTO t_lock VALUES (5,5); INSERT INTO t_lock VALUES (6,6); GO -- Session 1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN TRAN SELECT c1 FROM t_lock WITH(holdlock, rowlock); -- Session 2 BEGIN TRAN UPDATE t_lock SET c1 = 10

下面的查询将显示锁信息。<dbid>的值应该替换为sys.databases的database_id。

SQL复制

SELECT resource_type, resource_associated_entity_id, request_status, request_mode,request_session_id, resource_description FROM sys.dm_tran_locks WHERE resource_database_id = <dbid>

下面的查询使用前一个查询中的resource_associated_entity_id返回对象信息。必须在连接到包含此对象的数据库时执行此查询。

复制

SELECT object_name(object_id), * FROM sys.partitions WHERE hobt_id=<resource_associated_entity_id>

下面的查询将显示阻塞信息。

SQL复制

SELECT t1.resource_type, t1.resource_database_id, t1.resource_associated_entity_id, t1.request_mode, t1.request_session_id, t2.blocking_session_id FROM sys.dm_tran_locks as t1 INNER JOIN sys.dm_os_waiting_tasks as t2 ON t1.lock_owner_address = t2.resource_address;

通过回滚事务来释放资源。

SQL复制

-- Session 1 ROLLBACK; GO -- Session 2 ROLLBACK; GO

B.将会话信息链接到操作系统线程

下面的示例返回将会话 ID 与 Windows 线程 ID 相关联的信息。可以在 Windows 性能监视器中监视线程的性能。该查询不返回当前正在休眠的会话 ID。

SQL复制

SELECT STasks.session_id, SThreads.os_thread_id FROM sys.dm_os_tasks AS STasks INNER JOIN sys.dm_os_threads AS SThreads ON STasks.worker_address = SThreads.worker_address WHERE STasks.session_id IS NOT NULL ORDER BY STasks.session_id; GO

转换请求状态指示已向请求程序授予对资源的请求，并且请求程序当前正在等待升级到要授予的初始请求。

等待请求状态指示请求程序当前未持有对资源的已授予请求。

SET TRANSACTION ISOLATION LEVEL 用于指定会话的锁定级别。有关详细信息，请参阅SET TRANSACTION ISOLATION LEVEL (Transact-SQL)。

锁定表提示用于指定在 FROM 子句中表的单个引用的锁定级别。有关语法和限制，请参阅表提示 (transact-sql)。

lock_request_priority_state

process_killed_by_abort_blockers

ddl_with_wait_at_low_priority

如果觉得《sql server 查看死锁 以及执行语句》对你有帮助，请点赞、收藏，并留下你的观点哦！