SQLServer 2005死锁终极大法

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

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

为缓解查询压力，实现了读写分离，已经增加了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 不显示ASDECLARE @count  INT,@s      NVARCHAR(1000),@i      INTSELECT 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 #tFROM   (           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%'       )aORDER BY       s1,s2  SELECT @count = @@rowcount,@i = 1IF @count = 0   AND @show_spID_if_nolock = 1BEGIN    INSERT #t    SELECT 标志 = '正常的进程',DB_name(dbID),net_address    FROM   MASTER..sysprocesses        SET @count = @@rowcountENDIF @count > 0BEGIN    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添加记录结束ENDGO

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


运行1：

/************************************************************ * Code formatted by SoftTree sql Assistant ?v5.0.97 * Time: 2013-3-1 23:19:16 ************************************************************/BEGIN   TRAN UPDATE t_a_sexSET    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_sexWHERE  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。 总结

以上是内存溢出为你收集整理的SQLServer 2005死锁终极大法全部内容，希望文章能够帮你解决SQLServer 2005死锁终极大法所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。