create proc [dbo].[sp_monitor_lock]
as
begin
declare
@spid_1 smallint,
@spid_2 smallint,
@spid smallint, @kpid smallint, @blocked smallint , @waittype binary(2),@waittime bigint, @lastwaittype nchar(32),
@waitresource nchar(256),@login_time datetime, @last_batch datetime,@status nchar(30),
@sid binary(86), @hostname nchar(128),@program_name nchar(128),@hostprocess nchar(10), @cmd nchar(16), @sql_handle binary(20),
@cur_cursor CURSOR;
SET @cur_cursor = CURSOR LOCAL SCROLL FOR
Select spid, kpid, blocked, waittype,waittime, lastwaittype,waitresource,login_time, last_batch,status,sid, hostname,program_name,hostprocess, cmd, sql_handle from master..sysprocesses where blocked > 0;
open @cur_cursor
FETCH NEXT FROM @cur_cursor INTO @spid, @kpid, @blocked, @waittype,@waittime, @lastwaittype,@waitresource,@login_time, @last_batch,@status,@sid, @hostname,@program_name,@hostprocess, @cmd, @sql_handle
begin
begin tran
insert into mon_lock_object values(@spid, @kpid, @blocked, @waittype,@waittime, @lastwaittype,@waitresource,@login_time, @last_batch,@status,@sid, @hostname,@program_name,@hostprocess, @cmd, @sql_handle) ;
declare
@v varchar(3000)
set @v= 'dbcc inputbuffer('+ +CONVERT(VARCHAR(20),@spid) + ')';
insert into mon_spid(eventtype , parameters , eventinfo)
exec(@v);
update mon_spid set spid = @spid where spid is null;
set @v= 'dbcc inputbuffer('+ +CONVERT(VARCHAR(20),@blocked) + ')';
insert into mon_spid(eventtype , parameters , eventinfo)
exec(@v);
update mon_spid set spid = @blocked where spid is null;
insert into mon_lock
exec sp_lock
delete from mon_lock where not exists(select 'x' from mon_spid t2 where mon_lock.spid = t2.spid);
commit
end
CLOSE @cur_cursor
end
然后设置一个定时任务，定时执行改存储过程。通过查看跟踪表，找到阻塞的语句。经过一段时间时间观察后，发现了问题所在：
| spid | kpid | blocked | waittype | waittime | lastwaittype | waitresource |
| 159 | 12336 | 76 | 0x0004 | 60396 | LCK_M_U | KEY: 7:72057594347847680 (5e02a9cade30) |
| 110 | 10880 | 144 | 0x0004 | 93584 | LCK_M_U | KEY: 7:72057594362462208 (be01fe0b23c4) |
| 98 | 8020 | 95 | 0x0004 | 12089 | LCK_M_U | KEY: 7:72057594347257856 (f7013312862a) |
| 110 | 10880 | 144 | 0x0004 | 993618 | LCK_M_U | KEY: 7:72057594362462208 (be01fe0b23c4) |
| 110 | 10880 | 144 | 0x0004 | 1293628 | LCK_M_U | KEY: 7:72057594362462208 (be01fe0b23c4) |
| 110 | 10880 | 144 | 0x0004 | 693604 | LCK_M_U | KEY: 7:72057594362462208 (be01fe0b23c4) |
可以看到在跟踪期间，110一直被114阻塞。再看看110和114分别是什么
| 110 | Language Event | 0 | (@P0
nvarchar(4000))delete from mes_product_data_acquirement_transit where id=
@P0 |
| 144 | Language Event | 0 | exec p_data_acquirement_from_wms |
发现110正是我们的删除操作，而144为一个存储过程。是改存储过程阻塞了该删除操作。
| 110 | 7 | 1924917929 | 2 | KEY | (be01fe0b23c4) | U | WAIT |
| 144 | 7 | 1924917929 | 2 | KEY | (be01fe0b23c4) | X | GRANT |
再细看存储过程，发现该存储过程就一条SQL语句类似：
insert into ... select ..
的形式 ，而insert的表就是110要delete的表，而且后面的select 执行相当缓慢。最后经过优化后面的select 语句。问题解决。