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

工作中数据库经常出现内存,找了篇文章

 

参照CSDN,中国风(Roy)一篇死锁文章 
阻塞:其中一个事务阻塞,其它事务等待对方释放它们的锁,同时会导致死锁问题。

整理人:中国风(Roy) 参照Roy_88的博客

http://blog.csdn.net/roy_88/archive/2008/07/21/2682044.aspx

日期:2008.07.20 
************************************************************************************************************************/ 

SQL SERVER性能分析--死锁检测数据库阻塞语句<转>
SQL SERVER性能分析--死锁检测数据库阻塞语句<转>生成测试表
--生成测试表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 
SQL SERVER性能分析--死锁检测数据库阻塞语句<转>

 

生成数据:

SQL SERVER性能分析--死锁检测数据库阻塞语句<转>
/* 
表Ta 
ID Col1 Col2 
----------- ----------- ---------- 
1 101 A 
2 102 B 
3 103 C (3 行受影响) 
*/ 
SQL SERVER性能分析--死锁检测数据库阻塞语句<转>

 

1、将处理阻塞减到最少:
2、不要在事务中请求用户输入 
3、在读数据考虑便用行版本管理 
4、在事务中尽量访问最少量的数据 
5、尽可能地使用低的事务隔离级别 
阻塞1(事务): 

 

SQL SERVER性能分析--死锁检测数据库阻塞语句<转>
SQL SERVER性能分析--死锁检测数据库阻塞语句<转>事务阻塞
--测试单表 
--
---------------------连接窗口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性能分析--死锁检测数据库阻塞语句<转>

 

-->SQL SERVER 2000查询死锁进程

SQL SERVER性能分析--死锁检测数据库阻塞语句<转>
SQL SERVER性能分析--死锁检测数据库阻塞语句<转>代码
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 
*/ 
SQL SERVER性能分析--死锁检测数据库阻塞语句<转>

 

处理方法: 
法一:

--连接窗口2 
begin tran 
select * from ta with (nolock)--用nolock:业务数据不断变化中,如销售查看当月时可用。 

 

法二:
阻塞2(索引):
处理方法: 加索引

SQL SERVER性能分析--死锁检测数据库阻塞语句<转>代码

 

法三:设置当前查询隔离级别

 

-----------------------------连接窗口2------------------------------------------------
SET TRANSACTION ISOLATION LEVEL READ COMMITTED --设置会话已提交读:指定语句不能读取已由其他事务修改但尚未提交的数据 
begin tran 
select * from ta 

 

1、事务要尽量短

 

SQL SERVER性能分析--死锁检测数据库阻塞语句<转>查看死锁牺牲品

 

 

SQL SERVER性能分析--死锁检测数据库阻塞语句<转>查看进程运行状况

 

 

SQL SERVER性能分析--死锁检测数据库阻塞语句<转>查询锁类型

 

 

SQL SERVER性能分析--死锁检测数据库阻塞语句<转>查看SA用户执行的SQL

原文地址:http://www.cnblogs.com/ilovexiao/archive/2010/05/21/1740645.html



本文转自温景良(Jason)博客园博客,原文链接:http://www.cnblogs.com/wenjl520/archive/2010/07/15/1777794.html,如需转载请自行联系原作者

上一篇:【原】iOS触摸事件深度解析


下一篇:jqGrid细节备注—jqGrid有很多事件