查找引起SQL SERVER死锁操作方法，希望大家探讨

Question

测试
create table test(a int,b int)SET TRANSACTION ISOLATION LEVEL REPEATABLE  SERIALIZABLE 
go
begin tran
select * from test with (holdlock)
waitfor time '12:00'
select * from test
commit
goselect * from test
insert test values(1,2)gouse master
go
declare @spid int
select @spid=blocked
from (select * from sysprocesses where  blocked>0 ) a 
where not exists(select * from (select * from sysprocesses where  blocked>0 ) b 
where a.blocked=spid)
DBCC INPUTBUFFER (@spid )
exec sp_who2/*EventType      Parameters EventInfo                                                                                            
-------------- ---------- ---------------------------------------------------------------------------------------------------- 
Language Event 0          begin tran
select * from test with (holdlock)
waitfor time '12:00'
select * from test
commit
（所影响的行数为 1 行）DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
SPID  Status                         Login HostName BlkBy DBName Command          CPUTime  DiskIO LastBatch                   ProgramName                  SPID  
----- ------------------------------ ----- -------- ----- ------ ---------------- -------- ------ --------------------------- ---------------------------- ----- 
1     BACKGROUND                     sa      .        .   NULL   LAZY WRITER      172      0      04/02 1:46:21:                                           1    
2     sleeping                       sa      .        .   NULL   LOG WRITER       109      0      04/02 1:46:21:                                           2    
3     BACKGROUND                     sa      .        .   master SIGNAL HANDLER   16       0      04/02 1:46:21:                                           3    
4     BACKGROUND                     sa      .        .   NULL   LOCK MONITOR     16       0      04/02 1:46:21:                                           4    
5     BACKGROUND                     sa      .        .   master TASK MANAGER     0        300    04/02 1:46:21:                                           5    
6     BACKGROUND                     sa      .        .   master TASK MANAGER     0        0      04/02 1:46:21:                                           6    
7     sleeping                       sa      .        .   NULL   CHECKPOINT SLEEP 78       74     04/02 1:46:21:                                           7    
8     BACKGROUND                     sa      .        .   master TASK MANAGER     0        203    04/02 1:46:21:                                           8    
9     BACKGROUND                     sa      .        .   master TASK MANAGER     0        0      04/02 1:46:21:                                           9    
10    BACKGROUND                     sa      .        .   master TASK MANAGER     0        0      04/02 1:46:21:                                           10   
11    BACKGROUND                     sa      .        .   master TASK MANAGER     0        0      04/02 1:46:21:                                           11   
12    BACKGROUND                     sa      .        .   master TASK MANAGER     0        0      04/02 1:46:21:                                           12   
13    BACKGROUND                     sa      .        .   master TASK MANAGER     0        34     04/02 1:46:21:                                           13   
51    sleeping                       sa      .        .   zdhris AWAITING COMMAND 77       0      04/04 9:48:15:                                           51   
52    sleeping                       sa    ZD-XCY     .   pubs   WAITFOR          0        2      04/04 0:02:17:              SQL 查询分析器                    52   
53    sleeping                       sa    ZD-XCY     .   master AWAITING COMMAND 16       0      04/04 9:57:53:              SQL 查询分析器 — 对象浏览器            53   
54    sleeping                       sa    ZD-XCY   52    pubs   INSERT           16       4      04/04 0:02:39:              SQL 查询分析器                    54   
55    RUNNABLE                       sa    ZD-XCY     .   master SELECT INTO      940      8      04/04 1:08:27:              SQL 查询分析器                    55   
56    sleeping                       sa    ZD-XCY     .   master AWAITING COMMAND 2232     14     04/04 0:37:40:              MS SQLEM                     56   
57    sleeping                       sa    ZD-XCY   54    pubs   INSERT           16       0      04/04 0:11:41:              SQL 查询分析器                    57   
58    sleeping                       sa    ZD-XCY     .   master AWAITING COMMAND 16       0      04/04 0:06:19:              SQL 查询分析器                    58   
59    sleeping                       sa      .        .   zdhris AWAITING COMMAND 31       0      04/04 0:26:57:                                           59   
62    sleeping                       sa    ZD-XCY     .   master AWAITING COMMAND 16       0      04/04 0:37:53:              SQL 查询分析器 — 对象浏览器            62   （所影响的行数为 23 行）
*/

Answer 1

可以看到，SPID 52 发生死锁，发生死锁的语法是： begin tran
select * from test with (holdlock)
waitfor time '12:00'
select * from test
commit
如果是运用程序引起死锁，通过查找本语法，可以消除死锁

Answer 2

修改一下，查询出，同时KILL死锁的进程
use master
go
declare @spid int
--查询出死锁的SPID
select @spid=blocked
from (select * from sysprocesses where  blocked>0 ) a 
where not exists(select * from (select * from sysprocesses where  blocked>0 ) b 
where a.blocked=spid)
--输出引起死锁的操作
DBCC INPUTBUFFER (@spid)
--KILL引起死锁的进程
exec (' kill   ' + @spid）

Answer 3

试试俺的存储过程，俺现在不停地靠它杀死锁进程CREATE PROCEDURE auto_checkblocks AS
set nocount on
if exists ( select * from master..sysprocesses where blocked <> 0 )begin
/* show top blockers, but no duplicates */ select convert(char(24),getdate(),13)
select '注意发生阻塞！请不要慌张，请仔细查看下面的信息。导致问题的锁进程：'
select '请尝试使用KILL [SPID] 来杀进程'
        select '请尝试使用SP_LOCK [SPID]来显示锁信息，用OBJECT_NAME(ID)来显示锁对象名称或用sp_who [SPID] 来显示信息'
        select '在使用OBJECT_NAME显示对象名称时请注意对应的db_id'   select distinct
'进程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 spid in ( select blocked from master..sysprocesses )
and blocked = 0
order by str(spid,4) /* 显示阻塞牺牲品 */
        select '以上阻塞的牺牲品' 
        select 
'进程ID[SPID]'        = 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
endelse
begin
select '恭喜！当前没有阻塞，当前的进程信息如下.', convert (char(24),getdate(),13)
        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
endreturn
GO

Answer 4

楼主和jiangopen(jiangopen)的都有问题.
将我的回复,全选就行了,不要管语法错误复制到查询分析器,然后按F5执行,然后再新开一个窗口用你们的检测一下测试
create table test(a int,b int)SET TRANSACTION ISOLATION LEVEL REPEATABLE  SERIALIZABLE 
go
begin tran
select * from test with (holdlock)
waitfor time '12:00'
select * from test
commit
goselect * from test
insert test values(1,2)gouse master
go
declare @spid int
select @spid=blocked
from (select * from sysprocesses where  blocked>0 ) a 
where not exists(select * from (select * from sysprocesses where  blocked>0 ) b 
where a.blocked=spid)
DBCC INPUTBUFFER (@spid )
exec sp_who2/*EventType      Parameters EventInfo                                                                                            
-------------- ---------- ---------------------------------------------------------------------------------------------------- 
Language Event 0          begin tran
select * from test with (holdlock)
waitfor time '12:00'
select * from test
commit
（所影响的行数为 1 行）DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
SPID  Status                         Login HostName BlkBy DBName Command          CPUTime  DiskIO LastBatch                   ProgramName                  SPID  
----- ------------------------------ ----- -------- ----- ------ ---------------- -------- ------ --------------------------- ---------------------------- ----- 
1     BACKGROUND                     sa      .        .   NULL   LAZY WRITER      172      0      04/02 1:46:21:                                           1    
2     sleeping                       sa      .        .   NULL   LOG WRITER       109      0      04/02 1:46:21:                                           2    
3     BACKGROUND                     sa      .        .   master SIGNAL HANDLER   16       0      04/02 1:46:21:                                           3    
4     BACKGROUND                     sa      .        .   NULL   LOCK MONITOR     16       0      04/02 1:46:21:                                           4    
5     BACKGROUND                     sa      .        .   master TASK MANAGER     0        300    04/02 1:46:21:                                           5    
6     BACKGROUND                     sa      .        .   master TASK MANAGER     0        0      04/02 1:46:21:                                           6    
7     sleeping                       sa      .        .   NULL   CHECKPOINT SLEEP 78       74     04/02 1:46:21:                                           7    
8     BACKGROUND                     sa      .        .   master TASK MANAGER     0        203    04/02 1:46:21:                                           8    
9     BACKGROUND                     sa      .        .   master TASK MANAGER     0        0      04/02 1:46:21:                                           9    
10    BACKGROUND                     sa      .        .   master TASK MANAGER     0        0      04/02 1:46:21:                                           10   
11    BACKGROUND                     sa      .        .   master TASK MANAGER     0        0      04/02 1:46:21:                                           11   
12    BACKGROUND                     sa      .        .   master TASK MANAGER     0        0      04/02 1:46:21:                                           12   
13    BACKGROUND                     sa      .        .   master TASK MANAGER     0        34     04/02 1:46:21:                                           13   
51    sleeping                       sa      .        .   zdhris AWAITING COMMAND 77       0      04/04 9:48:15:                                           51   
52    sleeping                       sa    ZD-XCY     .   pubs   WAITFOR          0        2      04/04 0:02:17:              SQL 查询分析器                    52   
53    sleeping                       sa    ZD-XCY     .   master AWAITING COMMAND 16       0      04/04 9:57:53:              SQL 查询分析器 — 对象浏览器            53   
54    sleeping                       sa    ZD-XCY   52    pubs   INSERT           16       4      04/04 0:02:39:              SQL 查询分析器                    54   
55    RUNNABLE                       sa    ZD-XCY     .   master SELECT INTO      940      8      04/04 1:08:27:              SQL 查询分析器                    55   
56    sleeping                       sa    ZD-XCY     .   master AWAITING COMMAND 2232     14     04/04 0:37:40:              MS SQLEM                     56   
57    sleeping                       sa    ZD-XCY   54    pubs   INSERT           16       0      04/04 0:11:41:              SQL 查询分析器                    57   
58    sleeping                       sa    ZD-XCY     .   master AWAITING COMMAND 16       0      04/04 0:06:19:              SQL 查询分析器                    58   
59    sleeping                       sa      .        .   zdhris AWAITING COMMAND 31       0      04/04 0:26:57:                                           59   
62    sleeping                       sa    ZD-XCY     .   master AWAITING COMMAND 16       0      04/04 0:37:53:              SQL 查询分析器 — 对象浏览器            62   （所影响的行数为 23 行）
*/

Answer 5

//允许脏读
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
//设置LOCK 超时
SET LOCK_TIMEOUT timeout_period

Answer 6

to: caiyunxia (monkey) 
    jiangopen(jiangopen)你们将我上面的回复原样复制到查询分析器,不要改任何地方,然后按F5执行.再用你们的方法检测

Answer 7

找引起SQL SERVER死锁操作方法,
1程序写的烂
2检查事物要短
3对于统计或无关紧要的查询设置事物隔离级别允许脏读
4设置锁超时

Answer 8

TO zjcxc(: 邹建 :)  又去测试了一下,好像是我测试出了点问题.
什么问题？

Answer 9

还有个问题没有解决
不能将DBCC INPUTBUFFER (@spid)输出的结果自动保存为文件，不需要选择‘结果保存为文件’

Answer 10

--修改如下
use master
go
declare @spid int,@bl int
DECLARE s_cur CURSOR FOR 
select  0 ,blocked
from (select * from sysprocesses where  blocked>0 ) a 
where not exists(select * from (select * from sysprocesses where  blocked>0 ) b 
where a.blocked=spid)
union select spid,blocked from sysprocesses where  blocked>0
OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0
begin
if @spid =0 
            select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
else
            select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
FETCH NEXT FROM s_cur INTO @spid,@bl
end
CLOSE s_cur
DEALLOCATE s_cur---exec sp_who2/*
------------------------------------------------------- 
引起数据库死锁的是: 71进程号,其执行的SQL语法如下EventType      Parameters EventInfo                                        
-------------- ---------- ------------------------------------------------ 
Language Event 0          
select * from test
insert test values(1,2)
（所影响的行数为 1 行）DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
                                                                               
------------------------------------------------------------------------------ 
进程号SPID：64被进程号SPID：71阻塞,其当前进程执行的SQL语法如下EventType      Parameters EventInfo                                        
-------------- ---------- ------------------------------------------------ 
Language Event 0          
select * from test
insert test values(1,2)
（所影响的行数为 1 行）DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
                                                                               
------------------------------------------------------------------------------ 
进程号SPID：65被进程号SPID：64阻塞,其当前进程执行的SQL语法如下EventType      Parameters EventInfo                                                                                          
-------------- ---------- -------------------------------------------------------------------------------------------------- 
Language Event 0          begin tran
select * from test with (holdlock)
waitfor time '12:00'
select * from test
commit（所影响的行数为 1 行）DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
                                                                               
------------------------------------------------------------------------------ 
进程号SPID：73被进程号SPID：64阻塞,其当前进程执行的SQL语法如下EventType      Parameters EventInfo                                                                                          
-------------- ---------- -------------------------------------------------------------------------------------------------- 
Language Event 0          begin tran
select * from test with (holdlock)
waitfor time '12:00'
select * from test
commit（所影响的行数为 1 行）DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/

Answer 11

关键是解决死锁的根源,防止下次死锁. 1. 引起死锁的原因, 多用户访问同一个表没有用Nolock语句.
2. 查询数据量过大,服务器边线愉时.
3. 多个进程同时运行.队列等待时间太服务器边线愉时.
解决的方案.:
 抓出引起死锁的存储.(sp_lock, dbcc inputbuffer(spId)
1. 语句里面必要时用 select * from table  a(Nolocke) 不太建议使用
2. 查询条件建立索引,table table关联合理化.吞吐量不易太大.
3. 合理的优化排程

Answer 12

Kill死锁的进程是一种应急的措施

Answer 13

在Oracle中不會出現死鎖的問題﹐為什么SQL SERVER中沒有這種機制呢?
可否告訴我?