下面这个procedure,sql job每10分钟执行一次;执行一次大概10秒左右;
几乎每个月左右都发生一次dead lock; dead lock 之前的job,我看过历史记录,都是正常10秒左右正常结束的;
没办法了,请高手帮忙
CREATE procedure dbo.SP_Update_EQC_Data
as
declare @LOT nvarchar(16)
declare @LOTJOBSEQUENCE numeric(18, 0)
declare @LOTJOBSTATUS nvarchar(16)
declare @ROUTE nvarchar(32)
declare @ROUTESEQUENCE numeric(18, 0)
declare @STEP nvarchar(32)
declare @STEPSEQUENCE numeric(18, 0)
set nocount on--if substring(convert(varchar(20),getdate(),120),12,2) = '08' and substring(convert(varchar(20),getdate(),120),15,2) < '10'
-- delete from EQCCheck where LOTJOBSTATUS = 'COMPLETE' and ( LotjobLastUpdateTmst < dateadd(day, -0.1, getdate()) or RVLastUpdateTmst < dateadd(day, -0.1, getdate()))
-- Update the completed lotjob`s status which needn`t check RV
declare cur_Update_EQC_Data cursor for select A.LOT, A.LOTJOBSEQUENCE, B.LOTJOBSTATUS
from dbo.EQCCheck A LEFT JOIN AMAS_PRO..DPAS.I_LOTJOB B
on A.LOT = B.LOT collate Chinese_PRC_CI_AI_WS and A.LOTJOBSEQUENCE = B.LOTJOBSEQUENCE
where A.LOTJOBSTATUS <> B.LOTJOBSTATUS
open cur_Update_EQC_Data
fetch cur_Update_EQC_Data into @LOT, @LOTJOBSEQUENCE, @LOTJOBSTATUS while @@fetch_status=0
begin
if @LOTJOBSTATUS = 'COMPLETE'
begin
delete from EQCCheck where LOT = @LOT and LOTJOBSEQUENCE = @LOTJOBSEQUENCE
end
else
begin
update EQCCheck set LOTJOBSTATUS = @LOTJOBSTATUS , LotjobLastUpdateTmst = getdate() where LOT = @LOT and LOTJOBSEQUENCE = @LOTJOBSEQUENCE
end
fetch next from cur_Update_EQC_Data into @LOT, @LOTJOBSEQUENCE, @LOTJOBSTATUS
end
close cur_Update_EQC_Data
deallocate cur_Update_EQC_Data
GO
几乎每个月左右都发生一次dead lock; dead lock 之前的job,我看过历史记录,都是正常10秒左右正常结束的;
没办法了,请高手帮忙
CREATE procedure dbo.SP_Update_EQC_Data
as
declare @LOT nvarchar(16)
declare @LOTJOBSEQUENCE numeric(18, 0)
declare @LOTJOBSTATUS nvarchar(16)
declare @ROUTE nvarchar(32)
declare @ROUTESEQUENCE numeric(18, 0)
declare @STEP nvarchar(32)
declare @STEPSEQUENCE numeric(18, 0)
set nocount on--if substring(convert(varchar(20),getdate(),120),12,2) = '08' and substring(convert(varchar(20),getdate(),120),15,2) < '10'
-- delete from EQCCheck where LOTJOBSTATUS = 'COMPLETE' and ( LotjobLastUpdateTmst < dateadd(day, -0.1, getdate()) or RVLastUpdateTmst < dateadd(day, -0.1, getdate()))
-- Update the completed lotjob`s status which needn`t check RV
declare cur_Update_EQC_Data cursor for select A.LOT, A.LOTJOBSEQUENCE, B.LOTJOBSTATUS
from dbo.EQCCheck A LEFT JOIN AMAS_PRO..DPAS.I_LOTJOB B
on A.LOT = B.LOT collate Chinese_PRC_CI_AI_WS and A.LOTJOBSEQUENCE = B.LOTJOBSEQUENCE
where A.LOTJOBSTATUS <> B.LOTJOBSTATUS
open cur_Update_EQC_Data
fetch cur_Update_EQC_Data into @LOT, @LOTJOBSEQUENCE, @LOTJOBSTATUS while @@fetch_status=0
begin
if @LOTJOBSTATUS = 'COMPLETE'
begin
delete from EQCCheck where LOT = @LOT and LOTJOBSEQUENCE = @LOTJOBSEQUENCE
end
else
begin
update EQCCheck set LOTJOBSTATUS = @LOTJOBSTATUS , LotjobLastUpdateTmst = getdate() where LOT = @LOT and LOTJOBSEQUENCE = @LOTJOBSEQUENCE
end
fetch next from cur_Update_EQC_Data into @LOT, @LOTJOBSEQUENCE, @LOTJOBSTATUS
end
close cur_Update_EQC_Data
deallocate cur_Update_EQC_Data
GO
不用cursor,如何实现抓出某一行,还要对这行内容判断,然后进行操作呢》?
不过我知道一般SQL编程都是有这样的规则:能不用临时表就不用临时表,能不用cursor就不用cousor看你上面的SQL代码完全没有用curosr的必要随便改写下:
if @LOTJOBSTATUS = 'COMPLETE'
begin
delete from EQCCheck where LOT = @LOT and LOTJOBSEQUENCE = @LOTJOBSEQUENCE
end
else
begin
update EQCCheck
set LOTJOBSTATUS = LOTJOBSTATUS,
LotjobLastUpdateTmst = getdate()
from EQCCheck T
left join
(
select A.LOT, A.LOTJOBSEQUENCE, B.LOTJOBSTATUS
from dbo.EQCCheck A LEFT JOIN AMAS_PRO..DPAS.I_LOTJOB B
on A.LOT = B.LOT collate Chinese_PRC_CI_AI_WS and A.LOTJOBSEQUENCE = B.LOTJOBSEQUENCE
where A.LOTJOBSTATUS <> B.LOTJOBSTATUS
) S
on LOT = S.LOT
where LOT = @LOT and LOTJOBSEQUENCE = @LOTJOBSEQUENCE
end