Sql-server – change table structure in a transaction and then roll it back if there is an error

There is nothing you can really do because a rollback is being done via the UNDO tablespace inside ibdata1, which should have grown immensely.

If you kill the mysqld process and restart mysql, it will just pickup where it left off as part of the crash recovery cycle.

DISCLAIMER : Not Responsible for Data Loss

What you could do may result in data loss for other tables, but there is something you can do to circumvent InnoDB's normal crash recovery cycle.

There is a startup option called innodb_force_recovery, which allows you to bypass various stages of InnoDB crash recovery.

According to MySQL Documentation on Forcing InnoDB Recovery, here are the settings and its effects:

1 (SRV_FORCE_IGNORE_CORRUPT)

Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

2 (SRV_FORCE_NO_BACKGROUND)

Prevent the master thread from running. If a crash would occur during the purge operation, this recovery value prevents it.

3 (SRV_FORCE_NO_TRX_UNDO)

Do not run transaction rollbacks after crash recovery.

4 (SRV_FORCE_NO_IBUF_MERGE)

Prevent insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.

6 (SRV_FORCE_NO_LOG_REDO)

Do not do the redo log roll-forward in connection with recovery.

With transactional changes buried in the UNDO and REDO logs, you run the risk of

• losing data meant to be written
• keeping data meant to be deleted

In case you expect bad side effects, backup the entire /var/lib/mysql and put it somewhere in case you want to copy ibdata1, ib_logfile0, and ib_logfile1 and retry normal recovery.

If mysql is fully up in one of the modes

• mysqldump all data except the offending table
• shutdown mysql
• remove everything in /var/lib/mysql except /var/lib/mysql/mysql
• start mysql
• reload the mysqldump

CAVEAT : Make sure you backup everything !!!

I hope this helps !!!

I would rather recommend a pattern like the one from Exception Handling and Nested Transactions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end

This pattern checks the XACT_STATE() in the catch block to guard against uncommittable transactions:

Uncommittable Transactions and XACT_STATE
If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction cannot execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION. The XACT_STATE function returns a value of -1 if a transaction has been classified as an uncommittable transaction. When a batch finishes, the Database Engine rolls back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application. This indicates that an uncommittable transaction was detected and rolled back.

Your code is checking for @@TRANCOUNT in places where it cannot be 0, it uses a mixture of informational PRINT messages and SELECT result sets for communicating success, it does not handle errors that are recoverable. Ideally the exceptions should propagate to the client, in this case to the Agent job (ie. your catch should re-raise).