第十六章——处理锁、阻塞和死锁(2)——侦测阻塞和阻塞查询-阿里云开发者社区

开发者社区> 数据库> 正文

第十六章——处理锁、阻塞和死锁(2)——侦测阻塞和阻塞查询

简介: 原文: 第十六章——处理锁、阻塞和死锁(2)——侦测阻塞和阻塞查询 前言: 如果一个事务正在等待一些给其他事务锁定的资源。这个事务就被成为“被阻塞的事务”。
原文: 第十六章——处理锁、阻塞和死锁(2)——侦测阻塞和阻塞查询

前言:

如果一个事务正在等待一些给其他事务锁定的资源。这个事务就被成为“被阻塞的事务”。反过来,引起阻塞的事务,也就是锁定资源并造成其他事务等待的事务叫做“正在阻塞的事务”。

长时间运行事务会阻塞其他事务和查询,使他们等待长时间。在繁重的系统中,很多时候我们会遇到阻塞问题,如果一个事务因为阻塞未完成。会造成一些列的等待链。

本文将介绍如何发现并马上解决这方面的问题。

 

准备工作:

本例依旧使用SQLServer2012上的AdventureWorks2012数据库。

 

步骤:

1、 连到SQLServer2012的AdventureWorks2012数据库。

2、 新建窗口并输入:

USE AdventureWorks2012
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO

--开启事务
BEGIN TRANSACTION

--获取会话ID
SELECT  @@SPID AS Connection1_SessionID

SELECT  *
FROM    Sales.SalesOrderDetail
WHERE   SalesOrderDetailID = 121316


3、 执行完之后,截图如下:


4、 新开另外一个窗口,输入下面代码去开启另外一个事务,留意UPDATE语句,将不会执行,因为在等待第二步中的事务:


USE AdventureWorks2012
GO

--开启事务
BEGIN TRANSACTION

UPDATE  Sales.SalesOrderDetail
SET     OrderQty = 10
WHERE   SalesOrderDetailID = 121316

COMMIT TRANSACTION


5、 再开启一个事务,输入以下代码查询被阻塞和正在阻塞的查询: 

SELECT  R.session_id AS BlockedSessionID ,
        S.session_id AS BlockingSessionID ,
        Q1.text AS BlockedSession_TSQL ,
        Q2.text AS BlockingSession_TSQL ,
        C1.most_recent_sql_handle AS BlockedSession_SQLHandle ,
        C2.most_recent_sql_handle AS BlockingSession_SQLHandle ,
        S.original_login_name AS BlockingSession_LoginName ,
        S.program_name AS BlockingSession_ApplicationName ,
        S.host_name AS BlockingSession_HostName
FROM    sys.dm_exec_requests AS R
        INNER JOIN sys.dm_exec_sessions AS S ON R.blocking_session_id = S.session_id
        INNER JOIN sys.dm_exec_connections AS C1 ON R.session_id = C1.most_recent_session_id
        INNER JOIN sys.dm_exec_connections AS C2 ON S.session_id = C2.most_recent_session_id
        CROSS APPLY sys.dm_exec_sql_text(C1.most_recent_sql_handle) AS Q1
        CROSS APPLY sys.dm_exec_sql_text(C2.most_recent_sql_handle) AS Q2



6、 因为第一个连接占用了资源,阻塞了其他事务,所以这里要结束这个进程:


KILL 68
GO



7、 换回第二个查询界面,发现update操作已经成功完成。上面的进程号根据不同机器而定。


分析:

在本例中,把事务隔离级别设为REPEATABLE READ,因为在这个隔离级别中,在资源上的共享锁将持续到事务完成。所以当从表中查找数据是,该值上会加上共享锁。在事务提交或回滚前不会释放。

当执行第二个连接的update语句时,不能完成,因为被第一个事务阻塞了,且在REPEATABLE READ下共享锁不释放。

为了标识阻塞和被阻塞的请求,需要用到下面的DMO:

1、 dm_exec_requests

2、 dm_exec_sessions

3、 dm_exec_connections

4、 dm_exec_sql_text

 

 

版权声明:本文首发在云栖社区,遵循云栖社区版权声明:本文内容由互联网用户自发贡献,版权归用户作者所有,云栖社区不为本文内容承担相关法律责任。云栖社区已升级为阿里云开发者社区。如果您发现本文中有涉嫌抄袭的内容,欢迎发送邮件至:developer2020@service.aliyun.com 进行举报,并提供相关证据,一经查实,阿里云开发者社区将协助删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章