SqlServer运维——查询阻塞

目录

0. 演示阻塞

  1. 创建一个用于测试的表
CREATE TABLE [dbo].[AAA_MYTest](
	[Id] [BIGINT] IDENTITY(1,1) NOT NULL,
	[CreateDate] [DATETIME] NULL,
	[Msg] [NVARCHAR](50) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AAA_MYTest] ADD  CONSTRAINT [DF_AAA_MYTest_CreateDate]  DEFAULT (GETDATE()) FOR [CreateDate]
GO
  • 插入10调数据
INSERT INTO dbo.AAA_MYTest ([Msg])
VALUES
( N'2' ),
( N'3' ),
( N'4' ),
( N'5' ),
( N'6' ),
( N'7' ),
( N'8' ),
( N'9' ),
( N'10' )
  1. 首先MSSM中打开一个查询会话，这个是进程68

执行一个更新操作，但是不提交事务

BEGIN TRANSACTION;
UPDATE dbo.AAA_MYTest SET Msg='1' WHERE Id=1;
--COMMIT TRANSACTION
  1. 另开一个查询界面，进程：92

执行如下删除语句

DELETE dbo.AAA_MYTest WHERE Id=1

此时，会话92一直处于：正在执行查询

1. 基于sys.dm_exec_requests视图

  1. 使用sys.dm_exec_requests视图，查询一个进程被另外一个进程阻塞
SELECT session_id,
       blocking_session_id,
       wait_time,
       wait_type,
       last_wait_type,
       wait_resource,
       transaction_isolation_level,
       lock_timeout
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

若是没有阻塞，则查询无结果。

在上述构造的阻塞的情形下，执行上述查询脚本，可以看到如下结果

session_id blocking_session_id wait_time wait_type last_wait_type wait_resource transaction_isolation_level lock_timeout
92 68 275165 LCK_M_U LCK_M_U RID: 13:1:3829888:1 2 -1
  • 说明1：进程92被进程68阻塞。
  • 说明2：session_id（被阻塞会话），blocking_session_id（阻塞者）

2. 在MSSM中通过“活动和监视器”查看

可以在MSSM中的直接右键服务器连接-->活动和监视器-->查看：进程

这里我们简单的摘录几个主要字段如下：

会话ID 任务状态 命令 阻塞者 头阻塞程序
68 1
92 SUSPENED DELETE 68

在界面商可以直接选中行右键-->详细信息，可以查看当前会话在执行的语句

3. 完整脚本查询阻塞

  • 基于该脚本，可以查询阻塞的的会话ID，阻塞者ID，以及当前会话的执行的SQL语句

  • 来源金蝶官方提供

SELECT t1.resource_type AS [锁类型],
       DB_NAME(resource_database_id) AS [数据库名],
       t1.resource_associated_entity_id AS [阻塞资源对象],
       t1.resource_description AS [资源描述信息],
       t1.request_mode AS [请求的锁],
       t1.request_session_id AS [等待会话],
       t2.wait_duration_ms AS [等待时间],
       (
           SELECT [text]
           FROM sys.dm_exec_requests AS r WITH (NOLOCK)
               CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle])
           WHERE r.session_id = t1.request_session_id
       ) AS [等待会话执行的批SQL],
       (
           SELECT SUBSTRING(
                               qt.[text],
                               r.statement_start_offset / 2,
                               (CASE
                                    WHEN r.statement_end_offset = -1 THEN
                                        LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
                                    ELSE
                                        r.statement_end_offset
                                END
                               ) / 2
                           )
           FROM sys.dm_exec_requests AS r WITH (NOLOCK)
               CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt
           WHERE r.session_id = t1.request_session_id
       ) AS [等待会话执行的SQL],
       t2.blocking_session_id AS [阻塞会话],
       (
           SELECT [text]
           FROM sys.sysprocesses AS p
               CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle])
           WHERE p.spid = t2.blocking_session_id
       ) AS [阻塞会话执行的批SQL]
FROM sys.dm_tran_locks AS t1 WITH (NOLOCK)
    INNER JOIN sys.dm_os_waiting_tasks AS t2 WITH (NOLOCK)
        ON t1.lock_owner_address = t2.resource_address
OPTION (RECOMPILE);

结果：

锁类型 数据库名 阻塞资源对象 资源描述信息 请求的锁 等待会话 等待时间 等待会话执行的批SQL 等待会话执行的SQL 阻塞会话 阻塞会话执行的批SQL
RID AIS20240831180718 72057610665132000.00 1:1502832:0 U 92 73519 (@1 tinyint)DELETE [dbo].[AAA_MYTest] WHERE [Id]=@1 )DELETE [dbo].[AAA_MYTest] WHERE [Id]=@1 68 BEGIN TRANSACTION; UPDATE dbo.AAA_MYTest SET Msg='1' WHERE Id=1;

4. 查询死锁

在上述中，此时我们查询死锁

SELECT request_session_id AS spid,
       OBJECT_NAME(resource_associated_entity_id) AS tableName,
       request_mode,
       *
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT';

结果：

spid tableName request_mode resource_type resource_subtype resource_database_id resource_description resource_associated_entity_id resource_lock_partition request_mode request_type request_status request_reference_count request_lifetime request_session_id request_exec_context_id request_request_id request_owner_type request_owner_id request_owner_guid request_owner_lockspace_id lock_owner_address
92 AAA_MYTest IX OBJECT 12 1118180812 16 IX LOCK GRANT 1 33554432 92 0 0 TRANSACTION 612166454 00000000-0000-0000-0000-000000000000 0x0000027D1D001930:2:1 0x00000273DB138580
68 AAA_MYTest IX OBJECT 12 1118180812 14 IX LOCK GRANT 1 33554432 68 0 0 TRANSACTION 612173142 00000000-0000-0000-0000-000000000000 0x0000027AB6C8A530:1:1 0x0000029F42809C00

5. 杀死进程

  • 若是终止进程92，即：Delete的那个会话
KILL 92

执行后，92会话窗口显示结果：无法继续执行，因为会话处于终止状态。

  • 若是终止进程68，即：Update的那个会话
KILL 68

执行后，92会话窗口显示结果：1行受影响

  • 两个进程形成死锁，终止哪一个，需要小心。
    终止进程时要小心，并确保完全了解该进程正在做什么以及为什么会导致阻塞。当事务被终止时，该进程所做的所有数据更改都会回滚到之前的状态。

6. 参考

posted @ 2024-12-18 18:29  shanzm  阅读(2)  评论(0)  编辑  收藏  举报
TOP