sqlserver之定位死锁(经验分享)

[comszsoft]

很多人都遇到过这样的情况，当网站达到一定的访问量，数据库就会成为瓶颈，有可能发生死锁，进而引起阻塞。

有人认为这可能就是硬件的极限了，于是想办法增加硬件设备。而我本人认为问题的元凶可能是性能不高的sql脚本，引起了死锁，进而导致阻塞。

如果你和我有相同的看法，那我们就一起想办法找出问题的源头。

案例1.

某一天我被告知，我们的书城网站不能访问了，我马上查看，发现书城的有两台iis服务器均显示service unavailable，如果我断定是sqlserver数据库发生了死锁。

要知道是否发生了死锁，当然要看master库的sysprocess表,看看是否有什么进程堵住了别的进程,语句如下:

Select * from master..sysprocesses where blocked > 0

很快我发现，有一个blocked = 51 堵住了很多进程(查看blocked列可见)，果然和我的判断吻合；为了进一步找出发生死锁的语句，我用到的如下的语句

dbcc inputbuffer(51);

结果如下：

EventType Parameters EventInfo

------------------------------------------------

RPC Event 0 p_Book_content;1

从上面就可以看出是p_Book_content 这个过程引起的阻塞，但是这个过程里面同时对多个表进行了操作，到底是那个语句出了问题呢？

下面我们再来进一步定位死锁的位置：

Sp_lock

结果如下（大部分数据略）

Spid dbid objid indid type resource mode status

-------------------------------------------------------------------------------------------------

51 14 206623779 0 TAB X WAIT

52 14 0 0 DB S GRANT

53 14 0 0 DB S GRANT

。。。

。。。

。。。

现在我们来看看spid = 51 这行, mode = X 表示排它锁, status = WAIT表示正在等待（即阻塞了），dbid = 14 是数据库的id，objid = 206623779 是被锁的对象id,我们可以通过下列函数得到数据库和表:

Select db_name(@dbid) -----》book_db

select object_name(@objid) -------》 t_book

即book_db库的t_book表被锁住了,这时候再回投仔细检查 p_Book_content 存储过程，发现只有一个语句对t_book进行了操作:

update t_book set hitcount = hitcount + 1 where bookid = @bookid

这个语句的作用是更新书本的点击次数，为什么上面这个语句会引起死锁呢？我认为最可能的情况应该是同时访问的人过多，同时对表进行过多的update操作引起的，所以最终改用别的方式，不再实时对t_book表进行update操作，而是每次访问都先insert一条记录到一个中间表中，然后再用一个作业，每隔10分钟定时更新书本的点击次数，如此改进之后，此问题终于圆满解决了。

相关资料在book online可以找到, 关键字: sp_lock , sysprocesses , dbcc inputbuffer , db_name(), object_name()

------------------------------------------------
读别人的经验是不错的学习方式，希望可以看到高手们的经验

[中国风]

O

[hery2002]

O O

[being21]

牛人，带刀来的！！！

[xiaoku]

偶学习的份...

[zhnzzy]

不错不错,赞一个

[comszsoft]

多谢海爷和尚等高手指教

[comszsoft]

[Quote=引用 24 楼 being21 的回复:]
牛人，带刀来的！！！
[/Quote]

我非京城的那位，不要误会

[comszsoft]

多谢各位，经各位提醒，终于发现自己才疏学浅，不过发了这个帖子倒有不少收获。看来以后多发这样的帖子还是值得的。

另google得到如下说明：
----------------------------------
阻塞和死锁是两个不同的概念。
举个例子，现在有线程1和线程2，线程1占用了资源A，线程2占用了资源B。此时线程2需要使用资源A才能继续，但是资源A被线程1所占用，那么线程2只能等待资源A被线程1释放掉，这种情况称为线程2被阻塞。
但是，如果此时线程1也许要资源B才能继续，那么两个线程都会等待对方释放资源，这种情况就是死锁。
简单地讲，阻塞一般是没有循环调用的，而死锁是有循环调用情况的。

以下一些内容供您参考：
了解和解决 SQL Server 7.0 或 2000 阻塞问题
http://support.microsoft.com/?id=224453

阻塞是没有办法避免的，只要有并发的任务执行，就有可能发生这个现象。
死锁是我们应该重点避免的，主要的方法就是在编写程序的时候尽量考虑使用各种方法避免循环调用，同时设置一个最长等待时间。
------------------------------------

看来我没分清死锁和阻塞

[-狙击手-]

应该是被阻

[lsd123]

study

[HK_A_001]

来了！

[wynlc]

[Quote=引用 24 楼 being21 的回复:]
牛人，带刀来的！！！
[/Quote]

[gundanx10]

又学了点东西，看看你们发表的文章我才知道还有多少差距啊～

[bluemood80824633]

[Quote=引用 7 楼 wzjpsq 的回复:]
study
[/Quote]

[zhuyx808]

[Quote=引用 43 楼 liangpei2008 的回复:]
请楼下的谈一下
大家在写SQL语句时，如何避免这种潜在的问题！
[/Quote]
[Quote=引用 28 楼 comszsoft 的回复:]

死锁是我们应该重点避免的，主要的方法就是在编写程序的时候尽量考虑使用各种方法避免循环调用，同时设置一个最长等待时间。
[/Quote]

[xyzvalue]

好东西，学习了

[wubaozhang]

[Quote=引用 14 楼 perfectaction 的回复:]
这叫堵塞，不叫死锁。
[/Quote]
同意

查看堵塞也不用这么麻烦，直接查看企业管理器当前活动进程就可以直观了解了，要不然说不定等你明白过来ＳＱＬ已经执行完了，你就看不到那个ＳＱＬ造成的了

你说的判断依据也不合适，数量不是问题，可能这个进程被另一个进程锁了，然后他导致很多个进程堵塞都是有可能的

[wangxiao2008]

[Quote=引用 46 楼 xyzvalue 的回复:]
好东西，学习了
[/Quote]

[kofkyo]

学习

[lao_bulls]

学习