TRANSACTION rollback not working as expected

Question

I'm doing some DB schema re-structuring.

I have a script that looks broadly like this:

BEGIN TRAN LabelledTransaction
    --Remove FKs
    ALTER TABLE myOtherTable1 DROP CONSTRAINT <constraintStuff>
    ALTER TABLE myOtherTable2 DROP CONSTRAINT <constraintStuff>

    --Remove PK
    ALTER TABLE myTable DROP CONSTRAINT PK_for_myTable

    --Add replacement id column with new type and IDENTITY
    ALTER TABLE myTable ADD id_new int Identity(1, 1) NOT NULL
    GO
    ALTER TABLE myTable ADD CONSTRAINT PK_for_myTable PRIMARY KEY CLUSTERED (id_new)
    GO
    SELECT * FROM myTable

    --Change referencing table types
    ALTER TABLE myOtherTable1 ALTER COLUMN col_id int NULL
    ALTER TABLE myOtherTable2 ALTER COLUMN col_id int NOT NULL

    --Change referencing table values
    UPDATE myOtherTable1 SET consignment_id = Target.id_new FROM myOtherTable1 AS Source JOIN <on key table>
    UPDATE myOtherTable2 SET consignment_id = Target.id_new FROM myOtherTable2 AS Source JOIN <on key table>

    --Replace old column with new column 
    ALTER TABLE myTable DROP COLUMN col_id
    GO
    EXEC sp_rename 'myTable.id_new', 'col_id', 'Column'
    GO

    --Reinstate any OTHER PKs disabled
    ALTER TABLE myTable ADD CONSTRAINT <PK defn>

    --Reinstate FKs
    ALTER TABLE myOtherTable1 WITH CHECK ADD CONSTRAINT <constraintStuff>
    ALTER TABLE myOtherTable2 WITH CHECK ADD CONSTRAINT <constraintStuff>   

    SELECT * FROM myTable

    -- Reload out-of-date views
    EXEC sp_refreshview 'someView'

    -- Remove obsolete sequence
    DROP SEQUENCE mySeq
ROLLBACK TRAN LabelledTransaction

Obviously that's all somewhat redacted, but the fine detail isn't the important thing in here.

Naturally, it's quite hard to locate all the things that need to be turned off/editted before the core change (even with some meta-queries to help me), so I don't always get the script correct first time.

But I put in the ROLLBACK in order to ensure that the failed attempts left the DB unchanged.

But what I actually see is that the ROLLBACK doesn't occur if there were errors in the TRAN. I think I get errors about "no matching TRAN for the rollback"?

My first instinct was that it was about the GO statements, but https://stackoverflow.com/a/11121382/1662268 suggests that labeling the TRAN should have fixed that? What's happening? Why don't the changes get rolled back properly if there are errors.

How can I write and test these scripts in such a way that I don't have to manually revert any partial changes if the script isn't perfect first time?

EDIT: Additional comments based on the first answer.

If the linked answer is not applicable to this query, could you expand on why that is, and why it's different from the example that they had given in their answer?

I can't (or rather, I believe that I can't) remove the GOs, because the script above requires the GOs in order to compile. If I remove the GOs then later statements that depend on the newly added/renamed columns don't compile. and the query can't run.

Is there any way to work around this, to remove the GOs?

Put the whole thing in a try/catch block with your rollback in the catch. — Sean Lange, Apr 03 '17 at 13:23
@SeanLange -`TRY`/`CATCH` aren't allowed to span batches either, so if the `GO`s are necessary, that will not work. — Damien_The_Unbeliever, Apr 03 '17 at 13:29
@Damien_The_Unbeliever - Goodness I didn't even notice it was multiple batches....that will teach me to comment on a Monday morning before I finish my first cup of coffee. — Sean Lange, Apr 03 '17 at 13:30

Damien_The_Unbeliever · Accepted Answer · 2017-04-04T05:57:21.273

If you have any error which automatically causes the transaction to be rolled back then the transaction will roll back as part of the current batch.

Then, control will return back to the client tool which will then send the next batch to the server and this next batch (and subsequent ones) will not be wrapped in any transaction.

Finally, when the final batch is executed that tries to run the rollback then you'll get the error message you received.

So, you need to protect each batch from running when its not protected by a transaction.

One way to do it would be to insert our old fried GOTO:

GO
IF @@TRANCOUNT=0 GOTO NBATCH
...Rest of Code
NBATCH:
GO

or SET FMTONLY:

GO
IF @@TRANCOUNT=0 BEGIN
    SET FMTONLY ON
END
...Rest of Code
GO

Of course, this won't address all issues - some statements need to be the first or only statement in a batch. To resolve these, we have to combine one of the above techniques with an EXEC of some form:

GO
IF @@TRANCOUNT=0 BEGIN
    SET FMTONLY ON
END
EXEC sp_executesql N'/*Code that needs to be in its own batch*/'
GO

(You'll also have to employ this technique if a batch of code relies on work a previous batch has performed which introduces new database objects (tables, columns, etc), since if that previous batch never executed, the new object will not exist)

I've also just discovered the existence of the -b option for the sqlcmd tool. The following script generates two errors when run through SSMS:

begin transaction
go
set xact_abort on
go
create table T(ID int not null,constraint CK_ID check (ID=4))
go
insert into T(ID) values (3)
go
rollback

Errors:

Msg 547, Level 16, State 0, Line 7
The INSERT statement conflicted with the CHECK constraint "CK_ID". The conflict occurred in database "TestDB", table "dbo.T", column 'ID'.
Msg 3903, Level 16, State 1, Line 9
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

However, the same script saved as Abortable.sql and run with the following commandline:

sqlcmd -b -E -i Abortable.sql -S .\SQL2014 -d TestDB

Generates the single error:

Msg 547, Level 16, State 1, Server .\SQL2014, Line 1
The INSERT statement conflicted with the CHECK constraint "CK_ID". The conflict
occurred in database "TestDB", table "dbo.T", column 'ID'.

So, it looks like running your scripts from the commandline and using the -b option may be another approach to take. I've just scoured the SSMS options/properties to see if I can find something equivalent to -b but I've not found it.

Yep, this approach has worked. It's ugly as sin, but in the absence of anything better it seems the best approach. Will leave question open for a few days on principle. Feel free to pester if I don't come back and accept this answer. — Brondahl, Apr 03 '17 at 14:15
@Brondahl - I've just made a substantial addition - looks like there's another way to go that won't require significant changes to your scripts, if you're willing to use `sqlcmd` instead of `SSMS`. — Damien_The_Unbeliever, Apr 04 '17 at 05:58
Nice Find! shame that SSMS doesn't support it, but a really valuable thing to know for future. Thanks! — Brondahl, Apr 05 '17 at 08:00

score -2 · Answer 2 · answered Apr 03 '17 at 13:08

-2

Remove the 'GO', that finishes the transaction

answered Apr 03 '17 at 13:08

Matias Sincovich

52
2

As per my notes, the linked post asserted that the named transaction ought to circumvent this. Do you think this is innaccurate? If so please expand on why? Further, removing the `GO` isn't possible ... that script requires the `GO`s in order to compile. If I remove the `GO`s then later statements that depend on the newly added/renamed columns don't compile. – Brondahl Apr 03 '17 at 13:13

score -2 · Answer 3 · answered Apr 03 '17 at 13:25

Only ROLLBACK if completes - just use TRY/CATCH:

BEGIN TRANSACTION;
BEGIN TRY
     --Remove FKs
    ALTER TABLE myOtherTable1 DROP CONSTRAINT <constraintStuff>
    ALTER TABLE myOtherTable2 DROP CONSTRAINT <constraintStuff>
    --Remove PK
    ALTER TABLE myTable DROP CONSTRAINT PK_for_myTable
    --Add replacement id column with new type and IDENTITY
    ALTER TABLE myTable ADD id_new int Identity(1, 1) NOT NULL
    ALTER TABLE myTable ADD CONSTRAINT PK_for_myTable PRIMARY KEY CLUSTERED (id_new)
    SELECT * FROM myTable
    --Change referencing table types
    ALTER TABLE myOtherTable1 ALTER COLUMN col_id int NULL
    ALTER TABLE myOtherTable2 ALTER COLUMN col_id int NOT NULL
    --Change referencing table values
    UPDATE myOtherTable1 SET consignment_id = Target.id_new FROM myOtherTable1 AS Source JOIN <on key table>
    UPDATE myOtherTable2 SET consignment_id = Target.id_new FROM myOtherTable2 AS Source JOIN <on key table>
    --Replace old column with new column 
    ALTER TABLE myTable DROP COLUMN col_id
    EXEC sp_rename 'myTable.id_new', 'col_id', 'Column'
    --Reinstate any OTHER PKs disabled
    ALTER TABLE myTable ADD CONSTRAINT <PK defn>
    --Reinstate FKs
    ALTER TABLE myOtherTable1 WITH CHECK ADD CONSTRAINT <constraintStuff>
    ALTER TABLE myOtherTable2 WITH CHECK ADD CONSTRAINT <constraintStuff>   
    SELECT * FROM myTable
    -- Reload out-of-date views
    EXEC sp_refreshview 'someView'
    -- Remove obsolete sequence
    DROP SEQUENCE mySeq
    ROLLBACK TRANSACTION
END TRY
BEGIN CATCH
   print 'Error caught'
   select ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

You need to rollback the transaction inside the catch also or you will have a transaction left open. — Sean Lange, Apr 03 '17 at 13:29
Yes but if there is an error it will drop to your catch and it needs to rollback there also or the transaction will be orphaned. — Sean Lange, Apr 03 '17 at 13:52
It is orphaned so that the results can be tested in that state, as I understand. — cloudsafe, Apr 03 '17 at 14:06

TRANSACTION rollback not working as expected

3 Answers3