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

摘要

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

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

/***********************************************************************************************************************

整理人：黑木崖上的蜗(lenolotus...

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

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

/***********************************************************************************************************************

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

************************************************************************************************************************/

/***********************************************************************************************************************
阻塞：其中一个事务阻塞，其它事务等待对方释放它们的锁,同时会导致死锁问题

整理人：中国(Roy) 参照Roy_88的博

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:5758)
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

📱 扫码关注公众号

扫描二维码关注我们，获取更多精彩内容
实时更新 · 干货满满