SQLServer 2005死锁终极大法

发布时间：2020-12-12 14:08:08 所属栏目：MsSql教程来源：网络整理

导读：项目背景: 一个重要的项目，一个复杂的背景，每天大量人员频繁的操作，在过去的几个月内偶尔发生一两次死锁导致系统不能使用为缓解查询压力，实现了读写分离，已经增加了3台查询数据库与主数据库同步死锁解决办法： 1.首先创建表： /*********************

项目背景:

一个重要的项目，一个复杂的背景，每天大量人员频繁的操作，在过去的几个月内偶尔发生一两次死锁导致系统不能使用

为缓解查询压力，实现了读写分离，已经增加了3台查询数据库与主数据库同步

死锁解决办法：

1.首先创建表：

/************************************************************
 * Code formatted by SoftTree SQL Assistant ?v5.0.97
 * Time: 2013-3-1 23:17:23
 ************************************************************/

CREATE TABLE [dbo].[DeadLock]
(
	[ID]             [int],[标志]           [varchar](10),[进程ID]         [smallint],[线程ID]         [smallint],[块进程ID]       [smallint],[数据库ID]       [smallint],[数据库名]       [varchar](50),[用户ID]         [varchar](128),[用户名]         [varchar](100),[累计CPU时间]    [int],[登陆时间]       [datetime],[打开事务数]     [varchar](30),[进程状态]       [varchar](128),[工作站名]       [varchar](128),[应用程序名]     [varchar](120),[工作站进程ID]   [varchar](228),[域名  ]         [nchar](12),[网卡地址]       [varchar](120),[进程的sql语句]  [varchar](3000),[发生时间]       [datetime]
)

这个表是根据下面的存储过程结果创建的，不要问我创建过程，反正我调了好几次才成功

2.创建下面的存储过程：

/************************************************************
 * Code formatted by SoftTree SQL Assistant ?v5.0.97
 * Time: 2013-3-1 23:18:34
 ************************************************************/

CREATE PROC p_lockinfo
@kill_lock_spid BIT = 1,--是否杀掉死锁的进程,1 杀掉,0 仅显示
@show_spid_if_nolock BIT = 0 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
AS
DECLARE @count  INT,@s      NVARCHAR(1000),@i      INT

SELECT id = IDENTITY(INT,1,1),标志,进程ID = spid,线程ID = kpid,块进程ID = blocked,数据库ID = dbid,数据库名 = DB_NAME(dbid),用户ID = uid,用户名 = loginame,累计CPU时间 = cpu,登陆时间 = login_time,打开事务数 = open_tran,进程状态 = STATUS,工作站名 = hostname,应用程序名 = program_name,工作站进程ID = hostprocess,域名 = nt_domain,网卡地址 = net_address
       INTO #t
FROM   (
           SELECT 标志 = '死锁的进程',spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,STATUS,hostname,program_name,hostprocess,nt_domain,net_address,s1 = a.spid,s2 = 0
           FROM   MASTER..sysprocesses a
                  JOIN (
                           SELECT blocked
                           FROM   MASTER..sysprocesses
                           GROUP BY
                                  blocked
                       )b
                       ON  a.spid = b.blocked
           WHERE  a.blocked = 0
                  AND program_name LIKE '%jtds%'
           UNION ALL
           SELECT '|_牺牲品_>',blocked,s1 = blocked,s2 = 1
           FROM   MASTER..sysprocesses a
           WHERE  blocked <> 0
                  AND program_name LIKE '%jtds%'
       )a
ORDER BY
       s1,s2


 


 




SELECT @count = @@rowcount,@i = 1

IF @count = 0
   AND @show_spid_if_nolock = 1
BEGIN
    INSERT #t
    SELECT 标志 = '正常的进程',DB_NAME(dbid),net_address
    FROM   MASTER..sysprocesses
    
    SET @count = @@rowcount
END


IF @count > 0
BEGIN
    CREATE TABLE #t1
    (
    	id         INT IDENTITY(1,a          NVARCHAR(30),b          INT,EventInfo  NVARCHAR(255)
    )
    
    
    
    
    IF @kill_lock_spid = 1
    BEGIN
        DECLARE @spid  VARCHAR(10),@标志  VARCHAR(10)
        
        WHILE @i <= @count
        BEGIN
            SELECT @spid = 进程ID,@标志 = 标志
            FROM   #t
            WHERE  id = @i
            
            INSERT #t1
            EXEC ('dbcc inputbuffer(' + @spid + ')')
            
            IF @标志 = '死锁的进程'
                EXEC ('kill ' + @spid)
            
            SET @i = @i + 1
        END
    END
    ELSE
        WHILE @i <= @count
        BEGIN
            SELECT @s = 'dbcc inputbuffer(' + CAST(进程ID AS VARCHAR) + ')'
            FROM   #t
            WHERE  id = @i
            
            INSERT #t1
            EXEC (@s)
            
            SET @i = @i + 1
        END
    
    SELECT a.*,进程的SQL语句 = b.EventInfo
    FROM   #t a
           JOIN #t1 b
                ON  a.id = b.id
    --hellolongbin添加记录
    
    
    INSERT INTO deadlock
    SELECT a.ID,a.标志,a.进程ID,a.线程ID,a.块进程ID,a.数据库ID,a.数据库名,a.用户ID,a.用户名,a.累计CPU时间,a.登陆时间,a.打开事务数,a.进程状态,a.工作站名,a.应用程序名,a.工作站进程ID,a.域名,a.网卡地址,b.EventInfo,GETDATE()
    FROM   #t a
           JOIN #t1 b
                ON  a.id = b.id
                    
                    
                    
                    
                    ----hellolongbin添加记录结束
END
GO

3.存储过程创建后，可以创建一个死锁测试一下。死锁创建：

运行1：

/************************************************************
 * Code formatted by SoftTree SQL Assistant ?v5.0.97
 * Time: 2013-3-1 23:19:16
 ************************************************************/


BEGIN   TRAN 
UPDATE t_a_sex
SET    sex = '00'
WHERE  sex_name = '请选择'
 
WAITFOR DELAY '00:02:30'
COMMIT   TRAN

打开新的查询窗口运行：

运行2：

/************************************************************
 * Code formatted by SoftTree SQL Assistant ?v5.0.97
 * Time: 2013-3-1 23:19:34
 ************************************************************/


BEGIN   TRAN 
SELECT *
FROM   t_a_sex
WHERE  sex_name = '请选择'


COMMIT   TRAN

? 4.运行exec p_lockinfo查看运行结果 5.打开deadlock表查看记录 6.最后，设置数据库任务，自动定时执行，我设置的是主数据库每隔一分钟执行一次，查询数据库2分钟执行一次 ? 每台数据库上均做如上操作 ? (双保险:程序代码可以在数据库建立连接时设置 "set lock_timeout 30000" 此步骤与上面的措施没有任何关系,可以忽略不做) ? ? 这种方式不是最完美的，因为被杀掉的操作无法恢复，如果用在网站充值或其他方面还需要进一步完善，但对我们的系统来说已经足以应付了 ---------------------- 2010-7-16 补充上面的存储过程存在风险，会杀掉一些系统后台执行的锁，在这两天的跟踪过程中，发现先后数次杀掉了 EXEC dbo.sp_MShistory_cleanup @history_retention = 48 sys.sp_MSadd_logreader_history;1 等语句，今天早晨更是把数据库同步的一个事务给干掉了，导致查询机无法与正式库同步，所以上面的存储过程要优化为只杀业务程序造成的死锁，就是上面添加的红色字体部分 and program_name like '%jtds%' ? 这里的名子根据业务程序名自行修改 ? DBCC INPUTBUFFER ，DBCC OUTPUTBUFFER 当数据库发生死锁时，可以使用这些查看发生死锁的语句或指令DBCC INPUTBUFFER 显示从客户端发送到 Microsoft? SQL Server? 的最后一个语句。语法 DBCC INPUTBUFFER(spid) 参数 spid 是 sp_who 系统存储过程的输出中所显示的用户连接系统进程 ID (SPID)。 DBCC OUTPUTBUFFER 以十六进制或 ASCII 格式返回指定系统进程 ID (SPID) 的当前输出缓冲区。语法 DBCC OUTPUTBUFFER ( spid ) 参数 spid 是 sp_who 系统存储过程的输出中所显示的用户连接系统进程 ID。注释使用 DBCC OUTPUTBUFFER 时，DBCC OUTPUTBUFFER 显示发送到指定客户端 (spid) 的结果。对于不包含输出流的进程，则返回错误信息。若要显示所执行的、其返回的结果由 DBCC OUTPUTBUFFER 所显示的语句，请执行 DBCC INPUTBUFFER。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!