SQL SERVER性能分析--死锁检测数据库阻塞语句

女爷i 2022-06-09 12:37 225阅读 0赞

工作中数据库经常出错死锁，并且还要要求解决当前的死锁，问题多多；

参照CSDN，中国风(Roy)一篇死锁文章并改进了下；

整理人：黑木崖上的蜗牛(lenolotus) 日期:2009.04.28

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
阻塞：其中一个事务阻塞，其它事务等待对方释放它们的锁,同时会导致死锁问题。 整理人：中国风(Roy) 参照Roy\_88的博客

[http://blog.csdn.net/roy\_88/archive/2008/07/21/2682044.aspx][http_blog.csdn.net_roy_88_archive_2008_07_21_2682044.aspx]

日期:2008.07.20  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/  
\--生成测试表Ta  
if not object\_id('Ta') is null  
drop table Ta  
go  
create table Ta(ID int Primary key,Col1 int,Col2 nvarchar(10))  
insert Ta  
select 1,101,'A' union all  
select 2,102,'B' union all  
select 3,103,'C'  
go  
生成数据:  
/\*  
表Ta  
ID Col1 Col2  
\----------- ----------- ----------  
1 101 A  
2 102 B  
3 103 C (3 行受影响) \*/  
1、将处理阻塞减到最少:  
2、不要在事务中请求用户输入  
3、在读数据考虑便用行版本管理  
4、在事务中尽量访问最少量的数据  
5、尽可能地使用低的事务隔离级别  
阻塞1(事务):  
\--测试单表  
\-----------------------连接窗口1(update/insert/delete)------------------------------  
begin tran  
\--update  
update ta set col2='BB' where ID=2  
\--或insert  
\--begin tran  
\-- insert Ta values(4,104,'D')  
\--或delete  
\--begin tran  
\-- delete ta where ID=1  
  
\--rollback tran  
\-------------------------连接窗口2（查询表）---------------------------------------------  
begin tran  
select \* from ta  
\--rollback tran  
\---\--分析--------------------------------------------------  
\-->SQL SERVER 2005查询死锁进程  
select  
request\_session\_id as spid,  
resource\_type,  
db\_name(resource\_database\_id) as dbName,  
resource\_description,  
resource\_associated\_entity\_id,  
request\_mode as mode,  
request\_status as Status  
from  
sys.dm\_tran\_locks  
\--Result:  
/\*  
进程ID 资源类型 数据库 资源描述 资源关链ID 锁类型 进程状态  
\----------- ------------- ------ -------------------- ----------------------------- ----- ------  
59 DATABASE Gepro 0 S GRANT  
58 DATABASE Gepro 0 S GRANT  
57 DATABASE Gepro 0 S GRANT  
56 DATABASE Gepro 0 S GRANT  
58 PAGE Gepro 1:1904 72057594039435264 IS GRANT  
57 PAGE Gepro 1:1904 72057594039435264 IX GRANT  
58 OBJECT Gepro 853578079 IS GRANT  
57 OBJECT Gepro 853578079 IX GRANT  
57 KEY Gepro (020068e8b274) 72057594039435264 X GRANT  
58 KEY Gepro (020068e8b274) 72057594039435264 S WAIT  
(9 行受影响)  
\*/  
\-->SQL SERVER 2000查询死锁进程  
SELECT DISTINCT  
'进程ID' = STR(a.spid, 4)  
, '进程ID状态' = CONVERT(CHAR(10), a.status)  
, '死锁进程ID' = STR(a.blocked, 2)  
, '工作站名称' = CONVERT(CHAR(10), a.hostname)  
, '执行命令的用户' = CONVERT(CHAR(10), SUSER\_NAME(a.uid))  
, '数据库名' = CONVERT(CHAR(10), DB\_NAME(a.dbid))  
, '应用程序名' = CONVERT(CHAR(10), a.program\_name)  
, '正在执行的命令' = CONVERT(CHAR(16), a.cmd)  
, '登录名' = a.loginame  
, '执行语句' = b.text  
FROM master..sysprocesses a CROSS APPLY  
sys.dm\_exec\_sql\_text(a.sql\_handle) b  
WHERE a.blocked IN ( SELECT blocked  
FROM master..sysprocesses )  
\-- and blocked <> 0  
ORDER BY STR(spid, 4)  
\--Result  
/\*  
进程ID 进程ID 状态 死锁进程ID 工作站名称 执行命令的用户 数据库名 应用程序名 正在执行的命令 登录名 执行语句  
\---- ---------- ------ ---------- ---------- ---------- ---------- ---------------- ---------------------------------------------------------------------- -------------------------  
56 sleeping 0 DC91229126 sa Gepro Microsoft AWAITING COMMAND DC91229126FF442/Administrator SET STATISTICS XML OFF  
57 sleeping 0 DC91229126 sa Gepro Microsoft AWAITING COMMAND DC91229126FF442/Administrator SET STATISTICS XML OFF  
58 suspended 57 DC91229126 sa Gepro Microsoft SELECT DC91229126FF442/Administrator begin tran select \* from ta  
59 runnable 0 DC91229126 sa Gepro Microsoft SELECT DC91229126FF442/Administrator SELECT DISTINCT  
60 sleeping 0 DC91229126 sa Gepro Toad for S AWAITING COMMAND DC91229126FF442/Administrator SET FMTONLY OFF;  
62 sleeping 0 DC91229126 sa Gepro Toad for S AWAITING COMMAND DC91229126FF442/Administrator  
\*/  
\--查连接住信息(spid:57、58)  
select connect\_time,last\_read,last\_write,most\_recent\_sql\_handle  
from sys.dm\_exec\_connections where session\_id in(57,58)  
\--查看会话信息  
select login\_time,host\_name,program\_name,login\_name,last\_request\_start\_time,last\_request\_end\_time  
from sys.dm\_exec\_sessions where session\_id in(57,58)  
\--查看阻塞正在执行的请求  
select  
session\_id,blocking\_session\_id,wait\_type,wait\_time,wait\_resource  
from  
sys.dm\_exec\_requests  
where  
blocking\_session\_id>0--正在阻塞请求的会话的 ID。如果此列是 NULL，则不会阻塞请求  
/\*  
session\_id,blocking\_session\_id,wait\_type,wait\_time,wait\_resource  
58 57 LCK\_M\_S 2116437 KEY: 6:72057594039435264 (020068e8b274)  
\*/  
\--查看正在执行的SQL语句  
select  
a.session\_id,sql.text,a.most\_recent\_sql\_handle  
from  
sys.dm\_exec\_connections a  
cross apply  
sys.dm\_exec\_sql\_text(a.most\_recent\_sql\_handle) as SQL --也可用函数fn\_get\_sql通过most\_recent\_sql\_handle得到执行语句  
where  
a.Session\_id in(57,58)  
/\*  
session\_id text  
\----------- -----------------------------------------------  
57 SET STATISTICS XML OFF  
58 begin tran select \* from ta  
\*/  
  
处理方法:  
法一：  
\--连接窗口2  
begin tran  
select \* from ta with (nolock)--用nolock:业务数据不断变化中,如销售查看当月时可用。  
法二：  
阻塞2(索引):  
处理方法: 加索引  
create index IX\_Ta\_Col1 on Ta(Col1)--用COl1列上创索引,当更新时条件:COl1=102会用到索引IX\_Ta\_Col1上得到一个排它键的范围锁  
\----------------------------连接窗口1 -------------------------------------------------  
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE  
\--针对会话设置了 TRANSACTION ISOLATION LEVEL  
\--SERIALIZABLE 幻影读、不可重复读和脏读都不允许  
begin tran  
update ta set col2='BB' where COl1=102  
\--rollback tran  
\-----------------------------连接窗口2------------------------------------------------  
begin tran  
select \* from ta  
法三：设置当前查询隔离级别  
\-----------------------------连接窗口2------------------------------------------------  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED --设置会话已提交读:指定语句不能读取已由其他事务修改但尚未提交的数据  
begin tran  
select \* from ta

1、事务要尽量短

\--查看死锁牺牲品

SELECT '进程ID\[SPID\]' = STR(a.spid, 4)  
, '进程状态' = CONVERT(CHAR(10), a.status)  
, '分块进程ID' = STR(a.blocked, 2)  
, '服务器名称' = CONVERT(CHAR(10), a.hostname)  
, '执行用户' = CONVERT(CHAR(10), SUSER\_NAME(a.uid))  
, '数据库名' = CONVERT(CHAR(10), DB\_NAME(a.dbid))  
, '应用程序名' = CONVERT(CHAR(10), a.program\_name)  
, '正在执行的命令' = CONVERT(CHAR(16), a.cmd)  
, '累计CPU时间' = STR(a.cpu, 7)  
, 'IO' = STR(a.physical\_io, 7)  
, '登录名' = a.loginame  
, '执行sql' = b.text  
FROM master..sysprocesses a CROSS APPLY  
sys.dm\_exec\_sql\_text(a.sql\_handle) b  
WHERE blocked <> 0  
ORDER BY spid

\--查看进程运行状况

SELECT '进程ID' = STR(spid, 4)  
, '进程ID状态' = CONVERT(CHAR(10), status)  
, '分块进程ID' = STR(blocked, 2)  
, '工作站名称' = CONVERT(CHAR(10), hostname)  
, '执行用户' = CONVERT(CHAR(10), SUSER\_NAME(uid))  
, '数据库名' = CONVERT(CHAR(10), DB\_NAME(dbid))  
, '应用程序名' = CONVERT(CHAR(10), program\_name)  
, '正在执行的命令' = CONVERT(CHAR(16), cmd)  
, '累计CPU时间' = STR(cpu, 7)  
, 'IO' = STR(physical\_io, 7)  
, '登录名' = loginame  
FROM master..sysprocesses  
\--where blocked = 0  
ORDER BY spid

\--blocked = 0表示没有阻塞的进程ID；

\--查询锁类型

select 进程id=a.req\_spid  
,数据库=db\_name(rsc\_dbid)  
,类型=case rsc\_type when 1 then 'NULL 资源（未使用）'  
when 2 then '数据库'  
when 3 then '文件'  
when 4 then '索引'  
when 5 then '表'  
when 6 then '页'  
when 7 then '键'  
when 8 then '扩展盘区'  
when 9 then 'RID（行 ID)'  
when 10 then '应用程序'  
end  
,对象id=rsc\_objid  
,对象名=b.obj\_name  
,rsc\_indid  
from master..syslockinfo a left join \#t b on a.req\_spid=b.req\_spid

转自：

http://blog.csdn.net/you\_tube/article/details/4132850

[http_blog.csdn.net_roy_88_archive_2008_07_21_2682044.aspx]: http://blog.csdn.net/roy_88/archive/2008/07/21/2682044.aspx