While @@Fetch_Status = 0 Begin INSERT INTO [server].MyDatabase.dbo.Mytabletobeinserted ( UPC,Sale_date) VALUES( @UPC,@Sale_date) 'Inserting the error trapping here' IF (@@ERROR <> 0) BEGIN ROLLBACK TRANSACTION; RETURN; END Update t_sale_from_pos set been_sent = 'y' where UPC = @UPC and sale_date=@sale_date Fetch Next from CursorSale into @UPC,@Sale_date end close CursorSale deallocate CursorSale
此存储过程每天都使用调度程序运行,并且数据通过网络传递.当此存储过程执行时,然后沿着执行过程突然出现网络超时.这将执行每一行以通过网络发送到其他服务器.
现在,我的问题是:
>这将如何影响在循环内调用回滚事务的数据?
>它是否会再次读取所有行并将其发送到服务器进行插入,或者只是读取执行期间失败的行?
需要一些建议.谢谢
解决方法
您可以尝试
begin distributed transaction.
Distributed transaction在这种情况下是完美的,因为分布式事务被设计为跨越两个或更多服务器.通过事务,即使系统崩溃,或者断电,系统仍然能够恢复到其一致状态.
BEGIN DISTRIBUTED TRANSACTION; BEGIN TRY //Your code here to create the cursor. While @@Fetch_Status = 0 Begin INSERT INTO [server].MyDatabase.dbo.Mytabletobeinserted(UPC,Sale_date) VALUES(@UPC,@Sale_date) Update t_sale_from_pos set been_sent = 'y' where UPC = @UPC and sale_date=@sale_date Fetch Next from CursorSale into @UPC,@Sale_date END close CursorSale deallocate CursorSale END TRY BEGIN CATCH close CursorSale deallocate CursorSale IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH; IF @@TRANCOUNT > 0 COMMIT TRANSACTION; GO
实际上,对于分布式事务,您不需要使用游标.在您的情况下,最好还考虑通过创建临时表来避免并发问题.原因是:insert语句可能需要一些时间,当它插入数据时,您的表可能会被另一个用户更新,之后发生的更新语句可能会更新错误的行.
BEGIN DISTRIBUTED TRANSACTION; BEGIN TRY CREATE TABLE #LocalTempTable(UPC int,Sale_date datetime) INSERT INTO #LocalTempTable(UPC,Sale_date) SELECT UPC,Sale_date FROM YourTable INSERT INTO [server].MyDatabase.dbo.Mytabletobeinserted(UPC,Sale_date) SELECT UPC,Sale_date FROM #LocalTempTable Update t_sale_from_pos set been_sent = 'y' where EXISTS (SELECT * FROM #LocalTempTable WHERE #LocalTempTable.UPC = t_sale_from_pos.UPC AND #LocalTempTable.Sale_date = t_sale_from_pos.Sale_date) END END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH; IF @@TRANCOUNT > 0 COMMIT TRANSACTION; GO