T-SQL could not rollback

Question

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.

But if the alter table statement fails because Table2 doesn't exist, it then triggers further errors.

Msg 1767, Level 16, State 0, Line 2 Foreign key 'FK_Constraint references invalid table 'dbo.Table2'.

Msg 1750, Level 16, State 0, Line 2 Could not create constraint. See previous errors.

Msg 1767, Level 16, State 0, Line 2 Foreign key 'FK_xxxxxx' references invalid table 'Table1'.

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!

Please tell me what went wrong. Any help would be much appreciated. Using SQL-Server 2008.

EDIT:

I added

SELECT @@TRANCOUNT;

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;
....

The results are 1 and 0 respectively. The alter table automatically rollbacks my transaction on error!? I can't understand this.

Answer 1

As far as I know, the ALTER TABLE command will create its own new transaction, and when it fails, will rollback that transaction. A single rollback within a proc will cause all the open transactions within that proc to be rolled back. So you're seeing the error because the failure of the ALTER TABLE statement is implicitly rolling back your transaction before you try to do it..

You can confirm this easily enough by checking the @TRANCOUNT within your code, and only calling rollback when it is not zero

Answer 2

Given this:

create table z
(
i int identity(1,1) not null,
zzz int not null
);

When you try the following..

begin try

    begin transaction

    alter table z drop column aaa;

    commit tran;

end try
begin catch 

    print 'hello';
    SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;

    IF (XACT_STATE()) = -1
    BEGIN
        PRINT
            N'The transaction is in an uncommittable state. ' +
            'Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

end catch

print 'reached';

..the error can be caught:

ErrorNumber ErrorMessage
4924    ALTER TABLE DROP COLUMN failed because column 'aaa' does not exist in table 'z'.

But try changing alter table z drop column aaa; to alter table z add zzz int;, Sql Server can catch the error..

Column names in each table must be unique. Column name 'zzz' in table 'z' is specified more than once.

..but won't yield back the control to you, CATCH block will not be triggered. Seems there's no hard and fast rules what errors are catch-able and which are not.

To illustrate the difference, here's the error catch-able by your code

Here's an error un-catch-able by your code, which is similar to your problem.

Notice that there's no grid there(via SELECT ERROR_NUMBER() as ErrorNumber, ERROR_MESSAGE() as ErrorMessage;). That means, Sql Server did not yield back the control to you after it detected an exception.

Maybe you can see other details here that might help: http://msdn.microsoft.com/en-us/library/ms179296.aspx

See this guideline for error handling @@ERROR and/or TRY - CATCH

---

By the way, on Postgresql all kind of DDL errors are catch-able by your code.

do $$


begin

    -- alter table z drop column aaa;
    alter table z add zzz int;


exception when others then 

    raise notice 'The transaction is in an uncommittable state. '
                     'Transaction was rolled back';

    raise notice 'Yo this is good! --> % %', SQLERRM, SQLSTATE;
end;


$$ language 'plpgsql';

Here's the dev-rendered error message for alter table z drop column aaa; on Postgresql:

Here's the dev-rendered error message for alter table z add zzz int; on Postgresql; which by the way in Sql Server, when it has an error on this type of statement, it won't yield back the control to you, hence your CATCH sections are sometimes useful, sometimes useless.

Answer 3

The only way this happens is if there is no open transaction in that SPID.

That's it. And the only way there's no open transaction is that either:

• You never started a new transaction after the old one committed or rolled back
• You have another commit or rollback somewhere you didn't notice
• Something killed your connection or forced a rollback from outside your spid (like a kill command from another session)

You don't provide much code. Is there any error trapping or any other conditional logic in your query that's not shown?

Answer 4

The error from the ALTER TABLE statement is a compile error rather than a runtime error - and so the whole batch in which that statement occurs is never executed. I'm guessing that there's no GO between BEGIN TRANSACTION and ALTER TABLE - hence the BEGIN TRANSACTION never executed, and what SQL Server is telling you is perfectly true.

Try adding a GO immediately after the BEGIN TRANSACTION.

Answer 5

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.

An example:

--  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