sql server - locked table by stored procedures in sql -
this question exact duplicate of:
i have table name called cusdec_boi table publish transaction replication,and client update table always,problem times lock table. have stored procedure name called isautodochrg,this sp runing every 3min.when lock cusdec_boi table.i found schedule sp's taken long time running.i think problem in following sp's please tell me there errors found following sps
use [isdb] go /****** object: storedprocedure [dbo].[isautodocchrg] script date: 5/15/2010 2:12:44 ******/ set ansi_nulls on go set quoted_identifier on go create procedure [dbo].[isautodocchrg] /* ----------------------------------------curser valriable>>>>>>>>>>>>>>> */ declare @consigneetin varchar(17), @instanceid nchar(10), @ppc varchar(17), @officecd varchar(5), @noticedate datetime, @ide_typ_typ char(1), @declaranttin varchar(11), @sad_reg_serial varchar(1), /* ---------------------------------------curser valriables<<<<<<<<<<<<<< */ /*--------------------------------------svat variables >>>>>>>>>>>>>>>>>>>>> */ @svatamount money, @vatamount money, @nsvatamount money, @svatstatus varchar, @balance money, -- version 1.2 >> @investortin varchar(11), @invtin varchar(11), @investorppc varchar(17), @ppc_chkcount smallint, @typeofcompany varchar(1), @ppccount int, @deconacccount int, @invonacccount int, @onacc_deccount smallint, @onacc_consicount smallint, -- version 1.2 << /*-------------------------------------create references>>>>>>>>>>>>>>>>>>>>*/ @thecount1 int, @thecount2 varchar(4), @refno varchar(11) , @costcentercd varchar(3), @doc_chgcont smallint, @notetype char(2), @docsvatamount money, @docvatamount money, @docnsvatamount money, @grnsvatamount money, @grnvatamount money, @grnnsvatamount money, @chrgcatcd varchar(10) select @docsvatamount= rate,@docnsvatamount=total,@docvatamount=tax dbo.ischrgcatrate version='0' , chrgcatcd='010104' select @grnsvatamount= rate,@grnnsvatamount=total,@grnvatamount=tax dbo.ischrgcatrate version='0' , chrgcatcd='010105' /*--------------------------------------svat variables <<<<<<<<<<<<<<<<<<<< */ /*--------------------------------------create csr>>>>>>>>>>>>>>>>>>>>>>>>>*/ declare @getgetcusdec_boi cursor set @getgetcusdec_boi= cursor select consigneetin, instanceid,ppc,officecd,doc_chg_date,doc_chgcont,ide_typ_typ,declaranttin,ppc_chkcount,invtin,onacc_deccount,onacc_consicount,sad_reg_serial cusdec_boi doc_chg_flag='0' /*--------------------------------------create csr<<<<<<<<<<<<<<<<<<<<<<<<<*/ open @getgetcusdec_boi fetch next @getgetcusdec_boi @consigneetin,@instanceid,@ppc,@officecd,@noticedate,@doc_chgcont,@ide_typ_typ,@declaranttin,@ppc_chkcount,@invtin,@onacc_deccount,@onacc_consicount,@sad_reg_serial while @@fetch_status = 0 begin set @balance=0 set @svatstatus=null if (@sad_reg_serial='g'or @sad_reg_serial='y') begin set @svatamount =@grnsvatamount set @vatamount=@grnvatamount set @nsvatamount=@grnnsvatamount set @chrgcatcd = '010105' end else begin set @svatamount=@docsvatamount set @vatamount=@docvatamount set @nsvatamount=@docnsvatamount set @chrgcatcd = '010104' end set @investortin=null set @investorppc=null set @ppccount=0 print @instanceid + 'cosdec in service' set @notetype=null if (substring(@ppc,15,2)='00') begin --(a) print 'boi ppc' select @investortin=tin,@investorppc=warehousecd iswarehouse --where invppc=@ppc warehousecd=@ppc select @ppccount=count(warehousecd) iswarehouse --from isinvsubcontractlnk --where invppc=@ppc warehousecd=@ppc set @notetype='09' --notification /@ppc_chkcount end --(a) else -- if (substring(@ppc,8,1)='s') begin --(b) print 'sub ppc' select @investortin=tin,@investorppc=warehousecd iswarehouse warehousecd=substring(@ppc,1,14) + '00' select @ppccount=count(warehousecd) iswarehouse warehousecd=@ppc set @notetype='10' --notification /@ppc_chkcount end --(b) --if(@investortin=null , @investorppc=null , @ppc_chkcount=0) if(@ppccount=0 , @ppc_chkcount=0) --print ' <investor not identified... [ppccode/ link table]' -- top 7 if begin--(c) print '<investor not identified[warehouse table]' exec createindexcommon @indextype = 'on', @ref= @refno output begin tran t7 update cusdec_boi set ppc_chkdate=getdate(),ppc_chkcount=1 instanceid=@instanceid --insert isonlinenotification( tin_subtin, onrefno, notificationtypecd, referenceno, userentered) -- values(@consigneetin,@refno,'01',@instanceid,'sys') -- version 1.2 -- insert isonlinenotification( tin_subtin, onrefno, notificationtypecd, referenceno, userentered) insert isonlinenotification( tin_subtin, onrefno, notificationtypecd, referenceno, userentered) values(@declaranttin,@refno,@notetype,@instanceid,'sys') if @@error <> 0 rollback tran t7 commit tran t7 end --(c) --else if(@investortin<>null , @investorppc<>null) else if(@ppccount>0) -->> top 7 else begin begin --(d) print 'investor identified> ' + @investortin + '[tin]> ' + @investorppc + '[ppc]> ' --if (@invtin null , @investortin not null) if (@investortin not null) begin update cusdec_boi set invtin=@investortin instanceid=@instanceid end -- took below @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ >> set @deconacccount=0 select @deconacccount = count(tin_subtin) isonacctregistry tin_subtin=@declaranttin , status='1' if (@deconacccount>0) begin select @balance= balance isonacctregistry tin_subtin=@declaranttin , status='1' print 'declarant found in onaccount> ' + @declaranttin + ' balance> ' +convert(varchar,@balance) end else if (@deconacccount=0 , @onacc_deccount=0 ) begin print 'declarant not found in onaccount [cannot proceed]> ' + @declaranttin set @notetype='11' exec createindexcommon @indextype = 'on', @ref= @refno output begin tran t8 update cusdec_boi set onacc_decdate=getdate(),onacc_deccount=1 instanceid=@instanceid insert isonlinenotification( tin_subtin, onrefno, notificationtypecd, referenceno, userentered) values(@declaranttin,@refno,@notetype,@instanceid,'sys') if @@error <> 0 rollback tran t8 commit tran t8 end -- took below @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ << set @invonacccount=0 select @invonacccount=count(tin_subtin) isonacctregistry tin_subtin=left(@investortin,9) , status='1' if (@invonacccount>0 , @deconacccount>0) begin select @svatstatus=svatstatus isonacctregistry tin_subtin=left(@investortin,9) , status='1' print 'investor found in onaccount> ' + left(@investortin,9) + ' ' + 'svat status> ' + @svatstatus end else if (@invonacccount=0 , @onacc_consicount=0 , @deconacccount>0) begin print 'investor not found in onaccount svat status [cannot proceed]> ' + left(@investortin,9) set @notetype='12' exec createindexcommon @indextype = 'on', @ref= @refno output begin tran t9 update cusdec_boi set onacc_considate=getdate(),onacc_consicount=1 instanceid=@instanceid insert isonlinenotification( tin_subtin, onrefno, notificationtypecd, referenceno, userentered) values(@declaranttin,@refno,@notetype,@instanceid,'sys') if @@error <> 0 rollback tran t9 commit tran t9 end --print 'declarant>' + @declaranttin + ' ' + @svatstatus + 'declarant identified' -- version 1.2 --ischargesummary insert @investorppc , @declaranttin --isonlinenotification @declaranttin notifications sent him set @costcentercd=null set @notetype=null select @costcentercd=costcentercd isofficecode officecd=@officecd set @refno=null ------------------------------------ppppppppppppppppppppppppppppppppppppppppppppppppppppppp pppp> (1.0) if (@svatstatus='1' or @svatstatus='2') begin --create receipt reference----------------------------------------------------------- (1.1)< if (@balance>=@svatamount) begin exec createindexcommon @indextype = 'ch', @ref= @refno output -- trans (1) >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> begin tran t1 if (@svatstatus='1') begin insert ischrgdetail (chrefno, chrgcatcd, qty, amount, tax,netamount) values(@refno,@chrgcatcd,'1',@svatamount,@vatamount,@svatamount) insert ischrgsummary (chrefno, tin_subtin, ppc, totamount, tottax, netamount, userentered, dateentered, costcentercd, svatstatus,referenceno) -- version 1.2 --values(@refno,@consigneetin,@ppc,@nsvatamount,@vatamount,@svatamount,'sys',getdate(),@costcentercd ,'1',@instanceid) --values(@refno,@declaranttin,@investorppc,@nsvatamount,@vatamount,@svatamount,'sys',getdate(),@costcentercd ,'1',@instanceid) values(@refno,@declaranttin,@investorppc,@svatamount,@vatamount,@svatamount,'sys',getdate(),@costcentercd ,'1',@instanceid) end if (@svatstatus='2') begin insert ischrgdetail (chrefno, chrgcatcd, qty, amount, tax,netamount) values(@refno,@chrgcatcd,'1',@svatamount,'',@svatamount) insert ischrgsummary (chrefno, tin_subtin, ppc, totamount, tottax, netamount, userentered, dateentered, costcentercd, svatstatus,referenceno) -- version 1.2 --values(@refno,@consigneetin,@ppc,@nsvatamount,@vatamount,@svatamount,'sys',getdate(),@costcentercd ,'1',@instanceid) --values(@refno,@declaranttin,@investorppc,@nsvatamount,'',@svatamount,'sys',getdate(),@costcentercd ,'2',@instanceid) values(@refno,@declaranttin,@investorppc,@svatamount,'',@svatamount,'sys',getdate(),@costcentercd ,'2',@instanceid) end update cusdec_boi set doc_chg_flag='1', doc_chg_date=getdate() instanceid=@instanceid if @@error <> 0 rollback tran t1 commit tran t end -- put first notification if [@noticedate=null] --else if (@balance<@svatamount , (@noticedate=null or @noticedate<>convert(varchar(10),getdate(),101)) ) else if (@balance<@svatamount , (@noticedate null)) begin if (@ide_typ_typ='1') begin set @notetype='01' end else if (@ide_typ_typ='0') begin set @notetype='03' end --create notification reference----------------------------------------------------------- (1.1.2)> exec createindexcommon @indextype = 'on', @ref= @refno output --insert testt (ref) values(@refno) --create reference----------------------------------------------------------------------------- (1.1.2)< -- trans (2) >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> begin tran t2 update cusdec_boi set doc_chg_date=getdate(),doc_chgcont=1 instanceid=@instanceid --insert isonlinenotification( tin_subtin, onrefno, notificationtypecd, referenceno, userentered) -- values(@consigneetin,@refno,'01',@instanceid,'sys') -- version 1.2 -- insert isonlinenotification( tin_subtin, onrefno, notificationtypecd, referenceno, userentered) insert isonlinenotification( tin_subtin, onrefno, notificationtypecd, referenceno, userentered) values(@declaranttin,@refno,@notetype,@instanceid,'sys') if @@error <> 0 rollback tran t2 commit tran t2 end --put second reminders if first 1 sent >> convert(varchar(10),@noticedate,101) <>convert(varchar(10),getdate(),101)) else if (@balance<@svatamount , (convert(varchar(10),@noticedate,101) <>convert(varchar(10),getdate(),101)) , @doc_chgcont<10 ) begin if (@ide_typ_typ='1') begin set @notetype='02' end else if (@ide_typ_typ='0') begin set @notetype='04' end --create notification reference----------------------------------------------------------- (1.1.2)> exec createindexcommon @indextype = 'on', @ref= @refno output --create reference----------------------------------------------------------------------------- (1.1.2)< -- trans (3) >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> begin tran t3 update cusdec_boi set doc_chg_date=getdate(),doc_chgcont=doc_chgcont+1 instanceid=@instanceid --insert isonlinenotification( tin_subtin, onrefno, notificationtypecd, referenceno, userentered) -- values(@consigneetin,@refno,'02',@instanceid,'sys') insert isonlinenotification( tin_subtin, onrefno, notificationtypecd, referenceno, userentered) -- version 1.2 --values(@consigneetin,@refno,@notetype,@instanceid,'sys') values(@declaranttin,@refno,@notetype,@instanceid,'sys') if @@error <> 0 rollback tran t3 commit tran t3 end end ------------------------------------------------------pppppppppppppppppppppppppppppppppppppppppppppppppppppppp> (2.0) else if (@svatstatus='0') begin if (@balance>=@nsvatamount) begin --create receipt reference------------------------------------------------------------------ (2.1.1)> exec createindexcommon @indextype = 'ch', @ref= @refno output --create notification reference----------------------------------------------------------- (2.1.1)< -- trans (4) >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> begin tran t4 insert ischrgdetail (chrefno, chrgcatcd, qty, amount, tax,netamount) values(@refno,@chrgcatcd,'1',@svatamount,@vatamount,@nsvatamount) insert ischrgsummary (chrefno, tin_subtin, ppc, totamount, tottax, netamount, userentered, dateentered, costcentercd, svatstatus,referenceno) --version 1.2 --values(@refno,@consigneetin,@ppc,@nsvatamount,@vatamount,@nsvatamount,'sys',getdate(),@costcentercd ,'0',@instanceid) --values(@refno,@declaranttin,@investorppc,@nsvatamount,@vatamount,@nsvatamount,'sys',getdate(),@costcentercd ,'0',@instanceid) values(@refno,@declaranttin,@investorppc,@svatamount,@vatamount,@nsvatamount,'sys',getdate(),@costcentercd ,'0',@instanceid) update cusdec_boi set doc_chg_flag='1', doc_chg_date=getdate() instanceid=@instanceid if @@error <> 0 rollback tran t4 commit tran t4 end --end --else -- put first reminder/ reminder type code (1) >>>>>> (@noticedate=null) ) --else if (@balance<@svatamount , (@noticedate=null or @noticedate<>convert(varchar(10),getdate(),101)) ) else if (@balance<@nsvatamount , (@noticedate null) ) begin if (@ide_typ_typ='1') begin set @notetype='01' end else if (@ide_typ_typ='0') begin set @notetype='03' end --create notification reference----------------------------------------------------------- (2.2.1)> exec createindexcommon @indextype = 'on', @ref= @refno output --insert testt (ref) values(@refno) --create notification reference----------------------------------------------------------- (2.2.1)< /*insert isonlinenotification( tin_subtin, onrefno, notificationtypecd, remarks, userentered) values(@consigneetin,@refno,'1012',@instanceid,'sys') */ -- trans (5) >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> begin tran t5 --insert isonlinenotification( tin_subtin, onrefno, notificationtypecd, referenceno, userentered) -- values(@consigneetin,@refno,'01',@instanceid,'sys') insert isonlinenotification( tin_subtin, onrefno, notificationtypecd, referenceno, userentered) -- version 1.2 --values(@consigneetin,@refno,@notetype,@instanceid,'sys') values(@declaranttin,@refno,@notetype,@instanceid,'sys') update cusdec_boi set doc_chg_date=getdate(),doc_chgcont=1 instanceid=@instanceid if @@error <> 0 rollback tran t5 commit tran t5 end ----------------------------------------------------------------------------------------------------------------------< -- send second reminders/ reminder type code (3) >>>>> (convert(varchar(10),@noticedate,101) <>convert(varchar(10),getdate(),101)) else if (@balance<@nsvatamount , (convert(varchar(10),@noticedate,101) <>convert(varchar(10),getdate(),101))and @doc_chgcont<10 ) begin if (@ide_typ_typ='1') begin set @notetype='02' end else if (@ide_typ_typ='0') begin set @notetype='04' end --create notification reference----------------------------------------------------------- (2.2.1)> exec createindexcommon @indextype = 'on', @ref= @refno output --insert testt (ref) values(@refno) --create notification reference----------------------------------------------------------- (2.2.1)< /*insert isonlinenotification( tin_subtin, onrefno, notificationtypecd, remarks, userentered) values(@consigneetin,@refno,'1012',@instanceid,'sys') */ -- trans (6) >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> begin tran t6 --insert isonlinenotification( tin_subtin, onrefno, notificationtypecd, referenceno, userentered) -- values(@consigneetin,@refno,'02',@instanceid,'sys') insert isonlinenotification( tin_subtin, onrefno, notificationtypecd, referenceno, userentered) -- version 1.2 --values(@consigneetin,@refno,@notetype,@instanceid,'sys') values(@declaranttin,@refno,@notetype,@instanceid,'sys') update cusdec_boi set doc_chg_date=getdate(),doc_chgcont=doc_chgcont+1 instanceid=@instanceid if @@error <> 0 rollback tran t6 commit tran t6 end end ----------------------------------------------------------------------------------------------------------------------< --<< top 7 else end end --(d) set @consigneetin=null set @instanceid=null set @ppc=null set @officecd=null set @noticedate=null set @declaranttin=null set @ppc_chkcount=0 set @invtin=null set @onacc_deccount=null set @onacc_consicount=null set @sad_reg_serial=null fetch next @getgetcusdec_boi @consigneetin,@instanceid,@ppc,@officecd,@noticedate,@doc_chgcont,@ide_typ_typ,@declaranttin,@ppc_chkcount,@invtin, @onacc_deccount,@onacc_consicount,@sad_reg_serial end /*---------------------------------------------------------------------------------------------------------------*/ close @getgetcusdec_boi deallocate @getgetcusdec_boi go
mate, starting point, try using sp_who2 find out if long running times result of blocking sessions.
Comments
Post a Comment