【十二分火急】SQL SERVER 2005查看到有2000多个锁

【万分火急】SQL SERVER 2005查看到有2000多个锁
SQL SERVER 2005查看到有2000多个锁
执行SP_LOCK，还有SQL SERVER管理器里面都是显示，大约有2000左右个锁，共享锁，排他锁都有

问题：
1，这种情况正常吗？
2,如何解决，如何分辨出来哪个会带来死锁


[解决办法]

SQL code

Select distinct spid,blocked,loginame,hostname,open_tran,waittime,lastwaittype,waitresource,last_batch from master..sysprocesses where blocked <> 0 order by waittime desc/*其中blocked是引起锁的进程,在有多个锁的情况,要分析锁的引用关系,比如56号进程在等待50号70号进行在等待5677号进行在等待70这里最初引起锁的进程为50再查看50最后提交的sql*/dbcc inputbuffer(50)
[解决办法]
给你一段代码,
SQL code
USE tempdbif exists(select * from tempdb..sysobjects where name like 'CCLOCKS%' and type='u')DROP TABLE CCLOCKSCREATE TABLE CCLOCKS(SPID INT,DBID INT,OBJID INT,INDID INT,TYPE VARCHAR(10),RESOUSE VARCHAR(80),MODE VARCHAR(10),STATUS VARCHAR(10))if exists(select * from tempdb..sysobjects where name like 'CWHOS%' and type='u')DROP TABLE CWHOSCREATE TABLE CWHOS(SPID INT,ECID INT,STATUS VARCHAR(20),LOGINAME VARCHAR(50),HOSTNAME VARCHAR(20),BLK VARCHAR(3),DBNAME VARCHAR(50),CMD VARCHAR(50),request_id int)if exists(select * from tempdb..sysobjects where name like 'ACWHOS%' and type='u')DROP TABLE ACWHOSCREATE TABLE ACWHOS(SPID INT,ECID INT,STATUS VARCHAR(20),LOGINAME VARCHAR(50),HOSTNAME VARCHAR(20),BLK VARCHAR(3),DBNAME VARCHAR(50),CMD VARCHAR(50),request_id int)INSERT CWHOS EXEC sp_whoINSERT ACWHOS EXEC sp_who activeINSERT CCLOCKS EXEC SP_LOCK-- 取出前10个锁最多的进程SELECT top 10 spid,count(*)as s FROM CCLOCKS group by spid order by s desc-- 查进程执行的详细信息sp_who2 [spid号]    --> 进程详细信息DBCC INPUTBUFFER([spid号])   --> 执行的SQL-- 强制结束进程,慎用!切记.kill [spid号]
[解决办法]
探讨

锁越多，是不是代表越慢呢？


引用:

给你一段代码,
SQL code

USE tempdb
if exists(select * from tempdb..sysobjects where name like 'CCLOCKS%' and type='u')
DROP TABLE CCLOCKS
CREATE TABLE CCLOCKS
(SPID ……

[解决办法]
探讨

锁越多，是不是代表越慢呢？

[解决办法]
做生意的上来了
[解决办法]
探讨

给你一段代码,

SQL code


USE tempdb
if exists(select * from tempdb..sysobjects where name like 'CCLOCKS%' and type='u')
DROP TABLE CCLOCKS
CREATE TABLE CCLOCKS
(SPID INT,DBID INT,OBJID INT,INDID I……