标签归档:死锁

mssql sqlserver 查看死锁相关信息


摘要:
在业务系统中,由于很多未确定的因素产生,可能会”或多或少”的产生死锁现象,下文将从死锁的原因分析死锁,并给出相关的死锁避免脚本,如下所示:
实验环境:sqlserver 2008 R2



sqlserver产生死锁的原因:

1.系统各种资源不足,导致死锁
2.各种对象的调用,产生互相死锁

sqlserver产生死锁的必要条件:

1.一个资源被一个进程引用时,此时另一个进程无法使用,此时另一个进程就会处于等待状态
2.当前进程A所请求的资源被另一个进程B占用,而进程B所需使用的资源此时正被进程A占用
3.进程所需的资源无法从别地进程中释放出来
4.进程所需的资源无限等待状态

mssql sqlserver 使用sql脚本查看表死锁的方法分享


摘要:
下文讲述使用sql脚本获取表死锁的方法分享,如下所示:
实验环境:sql server 2008 R2


 
----方法1:使用sys.dm_tran_locks 系统表获取死锁进程Id 及 所对应的表名 
SELECT  request_session_id as [进程Id] ,
        OBJECT_NAME(resource_associated_entity_id)  as [表名]
FROM    sys.dm_tran_locks
WHERE   resource_type = 'OBJECT'
  
----方法2: 使用存储过程获取死锁表的方法分享

create proc   sp_lock_search
AS 
    BEGIN     
        DECLARE @spid INT     
        DECLARE @blk INT     
        DECLARE @count INT     
        DECLARE @index INT     
        DECLARE @lock TINYINT      
        SET @lock = 0      
        CREATE TABLE #tmp_lock_info
            (
              id INT IDENTITY(1, 1) ,
              spid INT ,
              blk INT
            )      
        IF @@error <> 0 
            RETURN @@error      
        INSERT  INTO #tmp_lock_info
                ( spid ,
                  blk
                )
                SELECT  0 ,
                        blocked
                FROM    ( SELECT    *
                          FROM      master..sysprocesses
                          WHERE     blocked > 0
                        ) a
                WHERE   NOT EXISTS ( SELECT *
                                     FROM   master..sysprocesses
                                     WHERE  a.blocked = spid
                                            AND blocked > 0 )
                UNION
                SELECT  spid ,
                        blocked
                FROM    master..sysprocesses
                WHERE   blocked > 0      
        IF @@error <> 0 
            RETURN @@error      
        SELECT  @count = COUNT(*) ,
                @index = 1
        FROM    #temp_who_lock      
        IF @@error <> 0 
            RETURN @@error      
        IF @count = 0 
            BEGIN     
                SELECT  '无死锁及阻塞'     
                RETURN 0      
            END     
        WHILE @index <= @count 
            BEGIN     
                IF EXISTS ( SELECT  1
                            FROM    #tmp_lock_info a
                            WHERE   id > @index
                                    AND EXISTS ( SELECT 1
                                                 FROM   #tmp_lock_info
                                                 WHERE  id <= @index
                                                        AND a.blk = spid ) ) 
                    BEGIN     
                        SET @lock = 1      
                        SELECT  @spid = spid ,
                                @blk = blk
                        FROM    #tmp_lock_info
                        WHERE   id = @index     
                        SELECT  '死锁进程Id: ' + CAST(@spid AS VARCHAR(88)) 
                       --- DBCC inputbuffer(@spid)      --死锁进程Id
                        ---DBCC inputbuffer(@blk)        --阻塞进程Id
                    END     
                SET @index = @index + 1      
            END     
        IF @lock = 0 
            BEGIN     
                SET @index = 1      
                WHILE @index <= @count 
                    BEGIN     
                        SELECT  @spid = spid ,
                                @blk = blk
                        FROM    #tmp_lock_info
                        WHERE   id = @index     
                        IF @spid = 0 
                            SELECT  '阻塞Id:' + CAST(@blk AS VARCHAR(88)) 
                        ELSE 
                            SELECT  '进程ID:' + CAST(@spid AS VARCHAR(88))
                                    + '被' + '进程号SPID:'
                                    + CAST(@blk AS VARCHAR(10))
                                    + '阻塞'     
                          --- DBCC inputbuffer(@spid)      --死锁进程Id
                        ---DBCC inputbuffer(@blk)        --阻塞进程Id    
                        SET @index = @index + 1      
                    END     
            END     
        DROP TABLE #tmp_lock_info      
        RETURN 0      
    END           
GO


exec sp_lock_search 
go 

mssql sqlserver 使用 sys.dm_tran_locks 解决死锁进程的方法分享


摘要:
下文通过举例的方式分享,通过sys.dm_tran_locks 系统对象快速获取死锁进程,处理死锁的方法分享,如下所示:
下文通过模拟死锁一张表,
并通过 sys.dm_tran_locks 获取死锁进程所对应的死锁进程ID和死锁表名,
通过kill处理死锁的方法分享
实验环境:sql server 2008 R2


第一步:
打开查询分析器窗口:
输入以下sql脚本:

 
/*
生成基础数据
*/ 
create table test 
(keyID int ,info varchar(60))
go
insert into test(keyId,info)
values(100,'maomao365.com'),
(200,N'sql_blog教程')

go

/*开始事务*/
begin tran
   ---更新表数据
   update test 
   set info ='测试事务'
  
--让程序等待3分钟
WAITFOR DELAY '03:00';    


commit tran
truncate table test 
drop table test 
return;

第二步:打开另一个查询分析器窗口

      SELECT  request_session_id  as [进程Id],
        OBJECT_NAME(resource_associated_entity_id)  as [涉及对象],*
		FROM    sys.dm_tran_locks
	    WHERE   resource_type = 'OBJECT'
	

mssql_sqlserver_sys.dm_transe_locks

mssql_sqlserver_sys.dm_transe_locks


第三步:处理死锁对象的方法:
新开一个查询分析器窗口,输入:

	  kill [进程Id]