sql server应用——取消存储过程执行注意回滚原创
4人赞赏了该文章
189次浏览
编辑于2022年11月10日 20:10:38
最近在处理一个大数据量表数据清理任务,通过存储过程实现单次10w行记录的清理,通过go循环实现循环执行。
以下是演示的存储过程:
select * into test_BD_ACCOUNT from T_BD_ACCOUNT create proc Proc_BF_test as begin begin try begin transaction waitfor delay '00:00:02:00' insert into test_BD_ACCOUNT(FACCTID,FNUMBER,FPARENTID,FHELPERCODE,FGROUPID,FDC,FACCTTBLID,FISCASH,FISBANK,FISALLOCATE,FISCASHFLOW,FITEMDETAILID,FISQUANTITIES,FUNITGROUPID,FUNITID,FISDETAIL,FLEVEL,FCREATEORGID,FCREATORID,FCREATEDATE,FUSEORGID,FMODIFIERID,FMODIFYDATE,FAUDITORID,FAUDITDATE,FFORBIDSTATUS,FFORBIDDERID,FFORBIDDATE,FMASTERID,FISSYSPRESET,FDOCUMENTSTATUS,FCFItemID,FCFINDIRECTITEMID,FOCFITEMID,FOCFINDIRECTITEMID,FALLCURRENCY,FCURRENCYLIST,FCURRENCYS,FCONTROLORGID,FISSHOWJOURNAL,FAMOUNTDC,FISCONTACT,FUNMANUAL,FISINTERNALCONTACTS,FCONTACTSFLEX,FNUMBER1) select FACCTID,FNUMBER,FPARENTID,FHELPERCODE,FGROUPID,FDC,FACCTTBLID,FISCASH,FISBANK,FISALLOCATE,FISCASHFLOW,FITEMDETAILID,FISQUANTITIES,FUNITGROUPID,FUNITID,FISDETAIL,FLEVEL,FCREATEORGID,FCREATORID,FCREATEDATE,FUSEORGID,FMODIFIERID,FMODIFYDATE,FAUDITORID,FAUDITDATE,FFORBIDSTATUS,FFORBIDDERID,FFORBIDDATE,FMASTERID,FISSYSPRESET,FDOCUMENTSTATUS,FCFItemID,FCFINDIRECTITEMID,FOCFITEMID,FOCFINDIRECTITEMID,FALLCURRENCY,FCURRENCYLIST,FCURRENCYS,FCONTROLORGID,FISSHOWJOURNAL,FAMOUNTDC,FISCONTACT,FUNMANUAL,FISINTERNALCONTACTS,FCONTACTSFLEX,FNUMBER1 from T_BD_ACCOUNT commit transaction end try begin catch rollback transaction select ERROR_NUMBER() errorNumber, --错误代码 ERROR_SEVERITY() errorSeverity, --错误严重级别 ERROR_STATE() errorState, --错误状态码 ERROR_PROCEDURE() errorProcedure, --出现错误的存储过程或触发器的名称 ERROR_LINE() errorLine, --发生错误的行号 ERROR_MESSAGE() errorMessage end catch end
通过以下方式调用:
exec Proc_BF_test go 200
中间由于其他原因取消了存储过程
后续再次运行:
exec Proc_BF_test go 200
发现异常慢!!同时在另外的窗口查询test_BD_ACCOUNT数量完全没有变化!!
没想到是因为取消执行的时候,由于存储过程执行了 begin transaction,但是没有执行到提交或者回滚,到时窗口现在处于未提交的状态,后续置执行的脚本都是未提交的。自己先入为主认为ssms窗口还是自动提交的状态。
提醒自己,后续中途取消执行存储过程,如果内含事务,一定要根据情况提交或者回滚,再进行后续的执行。