T-SQL 无法回滚 [英] T-SQL could not rollback
问题描述
我有一些代码具有纯顺序流，没有事务.我将它们夹在开始事务和提交事务中
I have some code that has a purely sequential flow, without transaction. I sandwich them with a begin transaction and commit transaction
begin transaction
......--My code here......
......
......--code to create Table1
......
ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT [FK_constraint] FOREIGN KEY([field1], [field2])
REFERENCES [dbo].[Table2] ([field3], [field4])
GO
....
......--End of My code here......
rollback transaction
commit transaction
当我运行脚本直到管理工作室中的回滚事务"正上方时，如果发生简单的错误，例如被零除，我运行回滚事务"，所有更改都将毫无问题地回滚.
when i run the script until just above "rollback transaction" in management studio, if a simple error occurs such as division by zero, I run "rollback transaction", all changes are rolledback without problem.
但如果由于 Table2 不存在而导致 alter table 语句失败，则会触发更多错误.
But if the alter table statement fails because Table2 doesn't exist, it then triggers further errors.
消息 1767，级别 16，状态 0，第 2 行外键 'FK_Constraint 引用了无效的表 'dbo.Table2'.
Msg 1767, Level 16, State 0, Line 2 Foreign key 'FK_Constraint references invalid table 'dbo.Table2'.
消息 1750，级别 16，状态 0，第 2 行无法创建约束.查看以前的错误.
Msg 1750, Level 16, State 0, Line 2 Could not create constraint. See previous errors.
消息 1767，级别 16，状态 0，第 2 行外键FK_xxxxxx"引用了无效的表Table1".
Msg 1767, Level 16, State 0, Line 2 Foreign key 'FK_xxxxxx' references invalid table 'Table1'.
当我运行回滚事务"时，我收到此错误消息ROLLBACK TRANSACTION 请求没有相应的 BEGIN TRANSACTION."这很愚蠢，因为我上面确实有一个开始事务！
When I run "rollback transaction", I got this error message "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." which is silly, because I DO HAVE a begin transaction on top!
请告诉我出了什么问题.任何帮助将非常感激.使用 SQL-Server 2008.
Please tell me what went wrong. Any help would be much appreciated. Using SQL-Server 2008.
我添加了
SELECT @@TRANCOUNT;
ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT"前后
before and after "ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT"
....
SELECT @@TRANCOUNT;
ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT [FK_constraint] FOREIGN KEY([field1], [field2]) REFERENCES [dbo].[Table2] ([field3], [field4])
GO
SELECT @@TRANCOUNT;
....
结果分别为 1 和 0.更改表会在出错时自动回滚我的事务！?我无法理解.
The results are 1 and 0 respectively. The alter table automatically rollbacks my transaction on error!? I can't understand this.
推荐答案
我认为对于带有 DDL 错误严重性处理的 Sql Server 处理，您无能为力，其中一些是由 Sql 自动处理的(例如强制回滚事务)服务器本身.
I think there's nothing you can do about Sql Server treatment with DDL error severity handling, some of it are handled automatically (forcibly rolling back transaction for example) by Sql Server itself.
你能做的就是让你的脚本代码处理它并为脚本用户提供描述性错误.
What you can just do is make your script code cope around it and provide script users with descriptive error.
示例:
-- drop table thetransformersmorethanmeetstheeye
-- select * from thetransformersmorethanmeetstheeye
-- first batch begins here
begin tran
create table thetransformersmorethanmeetstheeye(i int); -- non-erring if not yet existing
-- even there's an error here, @@ERROR will be 0 on next batch
ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT [FK_constraint] FOREIGN KEY([field1], [field2])
REFERENCES [dbo].[Table2] ([field3], [field4]);
go -- first batch ends here
-- second batch begins here
if @@TRANCOUNT > 0 begin
PRINT 'I have a control here if things needed be committed or rolled back';
-- @@ERROR is always zero here, even there's an error before the GO batch.
-- @@ERROR cannot span two batches, it's always gets reset to zero on next batch
PRINT @@ERROR;
-- But you can choose whether to COMMIT or ROLLBACK non-erring things here
-- COMMIT TRAN;
-- ROLLBACK TRAN;
end
else if @@TRANCOUNT = 0 begin
PRINT 'Sql Server automatically rollback the transaction. Nothing can do about it';
end
else begin
PRINT 'Anomaly occured, @@TRANCOUNT cannot be -1, report this to Microsoft!';
end
-- second batch implicitly ends here
这篇关于T-SQL 无法回滚的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
