一次惨痛的数据库锁死经历（都是加锁的祸根，索引的祸根）

站长苏飞

一次惨痛的数据库锁死经历（都是加锁的祸根，索引的祸根）

数据库锁我就不多说了，相信大家都知道，先说说我的经历吧
上午10左右，突然平台用户登录缓慢，甚至无法登录，但登录成功之后网站速度很快，没有任何异常

因此我们判断是登录方法出了问题

于是赶紧检查代码进行本地调试结果发现是访问数据库的其实两个表缓慢，因在在登录时需要记录日志等。

于是我优先处理方案是把这两行记录日志的代码给注释上传，先让用户正常使用，少记一点日志也没事

然后马上打开数据库日志库进行排查问题

然后我通过一条

[SQL] 纯文本查看 复制代码

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [UserId]
      ,[LoginIP]
      ,[LoginDate]
      ,[LoginRecordType]
  FROM [jjoobb_statistic].[dbo].[Pub_UserLoginLog]

发现查询很快，没有任务情况，

然后又加了一个条件

[SQL] 纯文本查看 复制代码

SELECT TOP 1000 [UserId]
      ,[LoginIP]
      ,[LoginDate]
      ,[LoginRecordType]
  FROM [jjoobb_statistic].[dbo].[Pub_UserLoginLog] where UserId=23

好吧这一条直接就查不出来了，一直在执行无响应，很明显是整个表甚至整个库被锁了

这样一个有索引的简单语句，应该是瞬间就能执行完成的，现在竟然无响应。

然后我通过以下语句查看锁的占用情况

[SQL] 纯文本查看 复制代码

--死锁检测 
use master 
Select * from sysprocesses where blocked<>0 
--找到SPID   
exec sp_lock 
--根据SPID找到OBJID 
select object_name(2073058421) 
--根据OBJID找到表名

执行如下

很明显有很多的锁，整个数据库怕是什么也查不到了。
没办法现在只能强制结束了，
执行如下语句

[SQL] 纯文本查看 复制代码

Exec dbo.sp_lock
begin
declare @i int--定义要清除的线程id
declare @SQL nvarchar(3000);
set @i =0;
while(@i<10000)
begin
--清除所有的占用线程
set @SQL=N'kill '+convert(varchar(20),@i)
exec sp_executesql  @SQL;
set @i=@i+1;
end
end

然后没过几分钟就全部Ok了，
但问题还是没有找到，于是就打开锁检查工具

通过检查发现其实一个表不管怎么查数据都慢，
好吧，因为我们的工程师忘记给这个表建索引了，时间久了数据增多，现在已经有40W数据了，外加上高频次的访问

导致整个数据库被锁，惨痛的教训啊。

站长苏飞

解决死锁

这里有几个方法可以帮助我们解决死锁问题。

      优化查询



      我们在写查询语句时，要考虑一下查询是否Join了没有必要的表？是否返回数据太多（太多的列或行）？查询是否执行表扫描？是否能通过调整查询次序来避免死锁？是否应该使用Join的地方使用了Left Join？Not In语句是否考虑周到？



      我们在写查询语句可以根据以上准则来考虑查询是否应该做出优化。



      慎用With(NoLock)



      默认情况下SELECT语句会对查询到的资源加S锁(共享锁)，由于S锁与X锁(排他锁)不兼容，在加上With(NoLock)后，SELECT不对查询到的资源加锁(或者加Sch-S锁，Sch-S锁可以与任何锁兼容)；从而使得查询语句可以更好和其他语句并发执行，适用于表数据更新不频繁的情况。



     也许有些人会提出质疑With(NoLock)，可能会导致脏读，首先我们要考虑查询的表是否频繁进行更新操作，而且是否要读回来的数据会被修改，所以衡量是否使用With(NoLock)还是要根据具体实际出发。



     优化索引



     是否有任何缺失或多余的索引？是否有任何重复的索引？



     处理死锁



     我们不能时刻都观察死锁的发生，但我们可以通过日志来记录系统发生的死锁，我们可以把系统的死锁错误写入到表中，从而方便分析死锁原因。



     缓存



     也许我们正在执行许多相同的查询非常频繁，如果我们把这些频繁的操作都放到Cache中，执行查询的次数将减少发生死锁的机会。我们可以在数据库的临时表或表，或内存，或磁盘上应用Cache，或是磁盘文件。

钢板-王玉松

吸取教训

mingsn

强烈支持楼主ing……

wtf3505

强烈支持楼主ing……