近两周系统频繁死锁，业务无法正常开展，我们建立了语句每秒钟来监控死锁，监控数据库死锁语句是通过如下SQL抓取的：
SELECT
DB_NAME(Blocked.database_id) AS 'database',
Blocked.Session_ID AS 'blocked SPID',
Blocked_SQL.TEXT AS 'blocked SQL',
Waits.wait_type AS 'wait resource',
Blocking.Session_ID AS 'blocking SPID',
Blocking_SQL.TEXT AS 'blocking SQL',
sess.status AS 'blocking status',
sess.total_elapsed_time AS 'blocking elapsed time',
sess.logical_reads AS 'blocking logical reads',
sess.memory_usage AS 'blocking memory usage',
sess.cpu_time AS 'blocking cpu time',
sess.program_name AS 'blocking program',
GETDATE() AS 'timestamp'
FROM sys.dm_exec_connections AS Blocking
INNER JOIN sys.dm_exec_requests AS Blocked ON Blocked.Blocking_Session_ID = Blocking.Session_ID
INNER JOIN sys.dm_os_waiting_tasks AS Waits ON waits.Session_ID = Blocked.Session_ID
INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = Blocking.Session_ID
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS Blocking_SQL
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS Blocked_SQL
通过如下语句查询死锁记录：
select timestamp, [blocked SPID], [blocked SQL], [wait resource], [blocking SPID], [blocking SQL], [blocking program]
from [kdcloud_ops_lockinfo]
order by 1 desc
查询到的，语句包括如下两个
第一个、(@FUSERID int,@FTOKEN nvarchar(36))DELETE FROM T_BAS_NETWORKCTRLRECORDS WHERE (FUSERID = @FUSERID AND FTOKEN = @FTOKEN)
第二个、CREATE PROCEDURE [dbo].[_sjz_GetJSJSSelList]
-- Add the parameters for the stored procedure here
@FID int ,
@FCustID int ,
@isSelList int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @isPass int =1
declare @Err varchar(255)=''
exec _sjz_UpdateJSJSSecQty 1
-- select ROW_NUMBER() over(order by FMaterialID) as FRowID, FMaterialID ,FStock, FQty
-- into #TempList
--from ( select F_PAEZ_MATERIAL as FMaterialID,F_PAEZ_Base as FStock ,sum(F_PAEZ_POQTY ) as FQty
-- from PAEZ_t_Cust_Entry100004
-- where fid=@FID and F_PAEZ_CUST =@FCustID
-- group by F_PAEZ_MATERIAL ,F_PAEZ_Base ) t
update t set FORDERNO=substring(FORDERNO,0,50) ,FSRCBILLNO=substring(FSRCBILLNO,0,50) from T_STK_STKTRANSFERINENTRY_R t where len(FORDERNO)>50
select ROW_NUMBER() over(order by FSeq) as FRowID,t2.FBILLNO,tic.FSALEUNITID as FUnitID , t1.FID, t1.FEntryID,'' F_PAEZ_FLOWNO, '' as F_PAEZ_COMPANY, '' as F_PAEZ_PONO,
'' as F_PAEZ_SUP, '' as F_PAEZ_POSEQ, '' as F_PAEZ_SYSFPNO, '' as F_PAEZ_GZDATE, '' as F_PAEZ_SONO,
'' as F_PAEZ_OLDMATERIALNUMBER, F_PAEZ_MATERIAL, '' F_PAEZ_PGR,
'' as F_PAEZ_OUN, F_PAEZ_AMOUNT as F_PAEZ_AMOUNT , 0 as F_PAEZ_ISCHECK, '' as F_PAEZ_OLDCUSTNO, F_PAEZ_CUST,
0 as F_PAEZ_JSF, '' as F_PAEZ_FGZ, '' F_PAEZ_BB, '' F_PAEZ_DW, '' F_PAEZ_TEXTBB, '' as F_PAEZ_JSFGSMC,
'' as F_PAEZ_SDFGSMC, '' as F_PAEZ_WLMS, F_PAEZ_BASE, FSEQ, '' as F_NMA_K3DW, F_PAEZ_K3QTY as F_PAEZ_POQTY
into #TempList
from PAEZ_t_Cust_Entry100004 t1 inner join PAEZ_t_Cust_Entry100003 t2 on t1.FID=t2.FID
left join t_BD_MaterialSale tic on t1.F_PAEZ_MATERIAL=tic.FMATERIALID
where t1.fid=@FID and F_PAEZ_CUST =@FCustID
--select * from #TempList
declare @OrgID int
select @OrgID=F_PAEZ_OrgId from PAEZ_t_Cust_Entry100003 where FID=@FID
create table #tempSel
(
FMaterialID int,
FCustID int,
FStockID int,
FID int,
FEntryID int,
FQty decimal(28,10),
FStockLocID int,
FBillID int,
FBillEntryID int
)
declare @FRowID int
declare @MaxRowID int
declare @FQty decimal(28,10)
declare @FMaterialID int
declare @FStockID int
declare @FDBID int
declare @FDBEntryID int
declare @FDBQty decimal(28,10)
declare @FBillID int
declare @FBillEntryID int
declare @FStockloc int
select @MaxRowID=max(FRowID ) from #TempList
set @FRowID=1
--select @MaxRowID
while @FRowID<=@MaxRowID
begin
select @FQty=F_PAEZ_POQTY ,@FMaterialID=F_PAEZ_MATERIAL ,@FStockID=F_PAEZ_BASE,@FBillID=FID ,@FBillEntryID=FEntryID from #TempList where FRowID=@FRowID
--select @FQty
while @FQty>0
begin
set @FDBID=0
set @FDBEntryID=0
set @FDBQty=0
select @FDBID=FID,@FDBEntryID=FENTRYID ,@FDBQty=FJOINUNSETTLEQTY,@FStockloc=FDESTSTOCKLOCID from (
SELECT top 1 t0.fid,t1.FENTRYID ,t1.FMATERIALID ,t1_T.FJOINUNSETTLEQTY-isnull(tt.FQty ,0) as FJOINUNSETTLEQTY,t1.FDESTSTOCKLOCID
FROM T_STK_STKTRANSFERIN t0 LEFT OUTER JOIN T_STK_STKTRANSFERINENTRY t1 ON t0.FID = t1.FID
LEFT OUTER JOIN T_STK_STKTRANSFERINENTRY_T t1_T ON t1.FENTRYID = t1_T.FENTRYID
--left join T_STK_STKTRANSFERINENTRY_R t4 on t1.FENTRYID=t4.FENTRYID
left join ( select fid,FEntryID ,sum(FQty ) as FQty from #tempSel group by fid,FEntryID) tt on t1.FENTRYID=tt.FEntryID
WHERE (((((t0.FSALEORGID = @OrgID )
AND (t1.FKEEPERID IN (SELECT FCUSTID FROM T_BD_CUSTOMER WHERE fmasterid = (select fmasterid from T_BD_CUSTOMER where FCUSTID=@FCustID))
AND t0.FBILLTYPEID IN ('ce8f49055c5c4782b65463a3f863bb4a', '0bcc8f3ce0a64171b1a901344d1ac239', 'a7c7f20d5faa46ecbeb88ee4e207390e', 'b15923ad5e39421aa188380c89849cb5', '005056941128823c11e323525db18103')))
AND (t0.FSALEORGID = @OrgID ))
AND (((((t0.FDOCUMENTSTATUS = 'C' AND t0.FCANCELSTATUS = 'A') AND t0.FBILLTYPEID = '0bcc8f3ce0a64171b1a901344d1ac239')
AND t0.FTRANSFERDIRECT = N'GENERAL') AND t0.FTRANSFERBIZTYPE = N'InnerOrgTransfer') AND (t1_T.FJOINUNSETTLEQTY-isnull(tt.FQty ,0) > 0)))
AND t0.FOBJECTTYPEID = 'STK_TransferDirect')
and t1.FMATERIALID =@FMaterialID and t1.FDESTSTOCKID=@FStockID
order by t0.FDATE
) t
declare @QtyTemp decimal(28,10)
if(@FDBID>0 and @FDBQty >0)
begin
if(@FDBQty>@FQty)
begin
set @QtyTemp=@FQty
end
else
begin
set @QtyTemp=@FDBQty
end
insert into #tempSel
(FMaterialID,FCustID,FStockID,FID,FEntryID,FQty,FStockLocID,FBillID,FBillEntryID )
select @FMaterialID ,@FCustID,@FStockID,@FDBID,@FDBEntryID,@QtyTemp ,@FStockloc,@FBillID,@FBillEntryID
end
else
begin
set @FQty=0
end
set @FQty=@FQty-@QtyTemp
end
set @FRowID=@FRowID+1
end
set @Err=''
select @Err=@Err+'/'+tic.FNUMBER from (
select F_PAEZ_MATERIAL ,F_PAEZ_BASE ,sum(F_PAEZ_POQTY ) as FQty from #TempList group by F_PAEZ_MATERIAL ,F_PAEZ_BASE
) t1 left join (
select FMaterialID,FStockID,sum(FQty ) as FQty from #tempSel group by FMaterialID,FStockID
) t2 on t1.F_PAEZ_MATERIAL=t2.FMaterialID and t1.F_PAEZ_BASE=t2.FStockID
left join T_BD_MATERIAL tic on t1.F_PAEZ_MATERIAL=tic.FMATERIALID
where t1.FQty >isnull(t2.FQty ,0)
if(len(@Err)>0)
begin
set @Err=@Err+'未结算数量不足'
set @isPass=0
end
--select * from #tempSel
if @isSelList=1
begin
select @isPass as IsPass,@Err as ErrMsg,* from ( select FID,FEntryID,sum(FQty) as FQty from #tempSel group by FID,FEntryID) tt order by tt.FID,tt.FEntryID
end
else
begin
select @isPass as IsPass,@Err as ErrMsg,t1.FUnitID,t1.FBILLNO,
t1.FID, 0 as FEntryID,'' F_PAEZ_FLOWNO, '' as F_PAEZ_COMPANY, '' as F_PAEZ_PONO,
'' as F_PAEZ_SUP, '' as F_PAEZ_POSEQ, '' as F_PAEZ_SYSFPNO, '' as F_PAEZ_GZDATE, '' as F_PAEZ_SONO,
'' as F_PAEZ_OLDMATERIALNUMBER, F_PAEZ_MATERIAL, '' F_PAEZ_PGR,
'' as F_PAEZ_OUN, sum(F_PAEZ_AMOUNT) as F_PAEZ_AMOUNT , 0 as F_PAEZ_ISCHECK, '' as F_PAEZ_OLDCUSTNO, 0 as F_PAEZ_CUST,
0 as F_PAEZ_JSF, '' as F_PAEZ_FGZ, '' F_PAEZ_BB, '' F_PAEZ_DW, '' F_PAEZ_TEXTBB, '' as F_PAEZ_JSFGSMC,
'' as F_PAEZ_SDFGSMC, '' as F_PAEZ_WLMS, F_PAEZ_BASE, 0 as FSEQ, '' as F_NMA_K3DW, sum(F_PAEZ_POQTY) as F_PAEZ_POQTY
,t2.FStockLocID ,sum(t2.FQty) FQty from #TempList t1 inner join ( select FStockLocID,FBillEntryID,sum(FQty) as FQty from #tempSel group by FStockLocID,FBillEntryID ) t2 on t1.FEntryID =t2.FBillEntryID
group by t1.FUnitID,t1.FBILLNO,t1.FID, F_PAEZ_MATERIAL, F_PAEZ_BB, F_PAEZ_BASE,t2.FStockLocID
end
drop table #TempList
END
其中第二个语句是创建我们的二开存储过程，这个是我们很有疑问的。死锁语句怎么可能是creat呢，执行是正常的，但是不可能是creat。
我们想确定到底是什么原因造成的死锁，另外我们账套有300多G，是否也对死锁有影响，
还请总部研发工程师与我联系帮我们处理掉这个问题，谢谢！
推荐阅读