思享工具箱

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

摘要：
工作期间，数据库中经常出现内存。我发现了一篇关于CSDN的文章，《中国风》中的一篇死锁文章被阻止了：其中一个事务被阻止，其他事务等待另一方释放锁，这也会导致死锁问题。

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

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

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

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

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

SQL SERVER性能分析死锁检测数据库阻塞语句<转>第1张SQL SERVER性能分析死锁检测数据库阻塞语句<转>第2张生成测试表
--生成测试表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(事务):

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

SQL SERVER性能分析死锁检测数据库阻塞语句<转>第5张SQL SERVER性能分析死锁检测数据库阻塞语句<转>第6张代码
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(索引):
处理方法: 加索引

SQL SERVER性能分析死锁检测数据库阻塞语句&lt;转&gt;第7张SQL SERVER性能分析死锁检测数据库阻塞语句&lt;转&gt;第8张代码
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、事务要尽量短

SQL SERVER性能分析死锁检测数据库阻塞语句&lt;转&gt;第9张SQL SERVER性能分析死锁检测数据库阻塞语句&lt;转&gt;第10张查看死锁牺牲品
--查看死锁牺牲品 

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 

 
SQL SERVER性能分析死锁检测数据库阻塞语句&lt;转&gt;第11张SQL SERVER性能分析死锁检测数据库阻塞语句&lt;转&gt;第12张查看进程运行状况
--查看进程运行状况 

   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；
SQL SERVER性能分析死锁检测数据库阻塞语句&lt;转&gt;第13张SQL SERVER性能分析死锁检测数据库阻塞语句&lt;转&gt;第14张查询锁类型
--查询锁类型

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
SQL SERVER性能分析死锁检测数据库阻塞语句&lt;转&gt;第15张SQL SERVER性能分析死锁检测数据库阻塞语句&lt;转&gt;第16张查看SA用户执行的SQL
----查看SA用户执行的SQL
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 OR a.loginame='sa'
ORDER BY spid 
原文地址:http://www.cnblogs.com/ilovexiao/archive/2010/05/21/1740645.html

标签：#char#convert#char函数#str函数#数据库死锁#数据库性能#sql数据库 | 浏览：179 | 发布日期：

免责声明：文章转载自《SQL SERVER性能分析死锁检测数据库阻塞语句&amp;lt;转&amp;gt;》仅用于学习参考。如对内容有疑问，请及时联系本站处理。

上篇[Asp.Net+C#]Datagrid使用技巧一（怎样灵活控制表头）CentOS7 编译安装golang和rpm安装golang下篇

宿迁高防，2C2G15M，22元/月；香港BGP，2C5G5M，25元/月 雨云优惠码：MjYwNzM=

相关文章

SqlServer2000连不上

SqlServer2000连不上 在使用 SQL Server 的过程中，用户遇到的最多的问题莫过于连接失败了。一般而言，有以下两种连接 SQL Server 的方式：     一是利用 SQL Server 自带的客户端工具，如企业管理器、查询分析器、事务探查器等。     二是利用用户自己开发的客户端程序，如ASP 脚本、VB程序等，客户端程序中又是利...

Crypto++入门学习笔记（DES、AES、RSA、SHA-256）

最先附上下载地址 背景（只是个人感想，技术上不对后面的内容构成知识性障碍，可以skip）： 最近，基于某些原因和需要，笔者需要去了解一下Crypto++库，然后对一些数据进行一些加密解密的操作。 笔者之前没接触过任何加密解密方面的知识（当然，把每个字符的ASCII值加1之流对明文进行加密的“趣事”还是干过的，当时还很乐在其中。），甚至一开始连Crypto+...

ORACLE ROWNUM解析[转]

一、对rownum的说明    关于Oracle 的 rownum 问题，很多资料都说不支持SQL语句中的“>、>=、=、between...and”运算符，只能用如下运算符号“<、<=、!=”，    并非说用“>、>=、=、between..and”时会提示SQL语法错误，而是经常是查不出一条记录来，还会出现似乎是莫...

[转]SQL SERVER 函数组合实现oracle的LPAD函数功能

本文转自：http://blog.csdn.net/a475701239/article/details/8295976      在写存储过程的时候遇到个问题，就是 将数字转成4位右对齐的字符串，不足补0       比如： 1   --> '0001'       在网上找了不少资料，最后找到个oracle的库函数 LPAD，但是SQL SERV...

CHAR,TCHAR,WCHAR 三者的区别与转换

#ifdef   UNICODE               typedef   wchar_t   TCHAR;     #else               typedef   unsigned   char   TCHAR;     #endif     typedef   unsigned   char   CHAR;     typedef  ...

查询速度慢的原因很多，常见如下几种

1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 2、I/O吞吐量小，形成了瓶颈效应。 3、没有 创建计算列导致查询不优化。 4、内存不足 5、网络速度慢 6、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量） 7、 锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷)sp_lock,sp_who,活动的用户查看,原因...

最新文章

随机推荐

思享工具箱导航