引子:

在基于数据库的应用系统的上线使用过程中, 由于程序本身的Bug, 运行非常耗资源的SQL语句, 或者数据库服务器资源紧张等情况, 都会使锁长时间得不到释放, 导致程序执行超时, 或者系统卡死.

什么是锁:

简单来说锁本来是数据库的一种对象, 锁加在SQL Server资源上（如在一个事务中读取或修改的行），以防止各种事务并发使用资源.

当锁出现时:

大多数的时候, 锁是由事务自建的, 所以有事务的地方就有它, 当事务提交后, 该事务建立的锁自然就会释放, 但是由于程序的原因, 比如一个显示建立的事务, 在程序完成后, 没有即使提交, 或者回滚, 导致事务和锁一直存在. 这时相同资源请求的语句就会排队等待. 如果锁不及时处理, 会导致更多的资源被锁定.

比如 语句1 更新 Table 1 的锁没有释放, 语句2 提交一个事务中需同时更新 Table 2 和 Table 1, 因为更新 Table 1 不能成功需排队等候, 这样导致 Table 2 也被锁住, 以此类推. 最后可能导致整个系统无法正常运转.

所以当出现问题时候, 第一要务是找到锁, 分析是哪种类型的锁, 哪个语句造成的锁, 在必要的情况下, 第一时间杀掉生成锁的进程.

处理办法:

利用系统表,master库中的系统表 sysprocesses 来得到以上需要关注的三个信息.

查找引发锁的进程

SELECT ProcessType = 'Locked Process', 
      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 = 
FROM     master..sysprocesses a
        JOIN
(
    SELECT blocked
    FROM   master..sysprocesses
    GROUP BY blocked
) b
            ON a.spid = b.blocked
WHERE   a.blocked =

查找排队的进程

SELECT 'Sacrificed Process', 
      spid, 
      kpid, 
      blocked, 
      dbid, 
      uid, 
      loginame, 
      cpu, 
      login_time, 
      open_tran, 
      STATUS, 
      hostname, 
      program_name, 
      hostprocess, 
      nt_domain, 
      net_address, 
      s1 = blocked, 
      s2 = 1
FROM   master..sysprocesses a
WHERE  blocked <>

查看进程当前/最后执行语句

DBCC InputBuffer('your_process_id')

杀掉问题进程

kill your_process_id

最后, 可以用这四个核心语句来封装成 Stored Procedure 来迅速查看和处理锁的异常.

Exec dbo.SP_DBLock @kill_lock_spid      BIT = 1, --Kill locked process, 1:Yes,   0:No   
                   @show_spid_if_nolock BIT = 1  --Show normal process if no lock occur, 1:Yes, 0:No