3

I have a stored procedure that does the following:

BEGIN TRANSACTION

    -- Code to delete updated records from production (dbo) table
    DELETE FROM [dbo].[factMyTable]
    WHERE exists (SELECT *
        FROM [RAW].[MyTable]
        WHERE [RAW].[MyTable].[refno] = [dbo].[factMyTable].[refno]
        AND [RAW].[MyTable].[modification_dttm] >= [dbo].[factMyTable].[modification_dttm]
        )

    -- Code to perform the append of incremental records 
    INSERT INTO [dbo].[factMyTable]
    SELECT
         [refno]
        ,[field1]
        ,[field2]
        ,[field3]
        ,[FieldN]
        ,[modification_dttm]
    FROM [RAW].[MyTable]

-- Truncate stage table and get ready for next load
TRUNCATE TABLE [RAW].[MyTable]

COMMIT TRANSACTION

As you can see above, I have a truncate command that is contained within a BEGIN/COMMIT transaction block. However I got an error when executing this stored procedure at the insert command, where a field set as NOT NULL was receiving a NULL value. As a result:

  1. The insertion of records from the RAW table into the dbo table got rolled back; BUT
  2. The truncation of the RAW table did not roll back.

The idea is that if there is an error with the insertion of data, the truncation shouldn't happen.

According to this article, we can roll back the truncate command, but perhaps my stored procedure is not scripted correctly. Perhaps there is a more direct way of ensuring that the truncation only happens if the insertion returns no errors? How would I go about it?

4
  1. The insertion of records from the RAW table into the dbo table got rolled back; BUT
  2. The truncation of the RAW table did not roll back.

No, there was no rollback at all, and here is the repro.

With xact_abort off that is your default set option I created 2 tables, I then open transaction end do 2 inserts one of which(the second one) fails, I added select @@trancount and select from both tables so you can better see what happens:

--set xact_abort on

if object_id('dbo.t1') is not null drop table dbo.t1;
if object_id('dbo.t2') is not null drop table dbo.t2;
go


create table dbo.t1 (col1 int);
insert into dbo.t1 values(1), (null);

create table dbo.t2 (col1 int not null);
go

begin transaction

    insert into dbo.t2
    values(-1);

    insert into dbo.t2
    select col1
    from dbo.t1;

    select @@trancount as [@@trancount before truncate];

    truncate table dbo.t1;

commit transaction;

select @@trancount as [@@trancount after commit];

select *
from dbo.t1;

select *
from dbo.t2;

enter image description here

As you see, no rollback was made, only your commit. You insert (-1) into dbo.t2 and this row is permanently there. This is because the error

Msg 515, Level 16, State 2, Line 18 Cannot insert the value NULL into column 'col1', table 'dbo.t2'; column does not allow nulls.

is statement terminating only. The second statement fails so no row were inserted, but insert of (-1) was not rollbacked, and as you see after the error your transaction is still open. It's your commit that commits insert of -1 and table truncation.


Now the second test: uncomment set xact_abort on, this will make statment terminating only error be batch aborting, all the statements within transaction will be rolled back and execution will be interrupted as soon as the error occurs.

So t1 table will never be truncated and the insert of (-1) in t2 will be rolled back.

And now how your code should be written:

set xact_abort on;

if object_id('dbo.t1') is not null drop table dbo.t1;
if object_id('dbo.t2') is not null drop table dbo.t2;
go

create table dbo.t1 (col1 int);
insert into dbo.t1 values(1), (null);

create table dbo.t2 (col1 int not null);
go

begin try
begin transaction

    insert into dbo.t2
    values(-1);

    insert into dbo.t2
    select col1
    from dbo.t1;

    select @@trancount as [@@trancount before truncate];

    truncate table dbo.t1;

commit transaction;
end try

begin catch
    select @@trancount as [@@trancount in catch before rollback];
    if @@rowcount > 0 rollback;
    throw;
end catch;

Your code should always set xact_abort on and it should have try..catch block.

You should do rollback from catch ad throw the error

| improve this answer | |
  • Thanks so much for this. Clearly I had a misunderstanding here as I thought that the BEGIN/COMMIT TRANSACTION block was enough for rolling back everything that is inside the block in case of an error (wrong!). I wonder though if set xact_abort on; is really needed, since the other example by @JonathanFile doesn't have one. – pmdci Nov 26 '19 at 15:04
  • 1
    While Jonathan's code and comment are correct I ALWAYS set xact_abort on. There is a case when your code just has no chance to finish in the catch block: if someone interrupts its execution. One day my colleague was executing sp in SSMS and suddenly realize it passes wrong parameter. He cancel execution in SSMS and was sure the rollback was done since that sp makes all in one transaction. But it was not true, no rollback was made and it hangs some activity as no lock was released and I found it when users started to complain – sepupic Nov 26 '19 at 16:34
  • that JUST happened to me as I was testing. LOL – pmdci Nov 26 '19 at 16:46
  • No, your case was different, if you had try.. catch block your code finished there. I'm talking about correct execution flow where there is no error but suddenly the code is interrupted – sepupic Nov 26 '19 at 16:53
  • I was testing the code and I halted a query. Then as I tried to query the raw table again, the table was locked. I didn't have SET XACT_ABORT ON; then – pmdci Nov 26 '19 at 17:02
2

The reference from David is wonderful, but if you are looking for a practical example of what you should be doing, here is a block for you. I've not duplicated your example precisely, since you can see the issues without it.

I build up a sample table and pop some data in there. I intentionally am trying to insert a NULL into the dbo.Fact table, which isn't allowed.

I then wrap my statements in a TRY/CATCH block. In the Catch block I have added a rollback command as well as a throw so the original error will go back to the client. If you omit the throw then no error will go back.

If you want to see your original behavior then you need to remove the TRY/CATCH and just have the BEGIN TRANSACTION and COMMIT in there. Since even with the try/catch SQL properly rolls back the transaction.

/** Build up our sample tables and some data.
    **/ 

DROP TABLE IF EXISTS dbo.RawTable;
DROP TABLE IF EXISTS dbo.Fact;

CREATE TABLE dbo.RawTable
    (
    RefNo CHAR(10) NOT NULL
    , field1 CHAR(10) NULL
    , field2 CHAR(10) NULL
    );

CREATE TABLE dbo.Fact
    (
    RefNo CHAR(10) NOT NULL
    , field1 CHAR(10) NOT NULL
    , field2 CHAR(10) NOT NULL
    );

INSERT INTO dbo.RawTable 
    (RefNo, field1, field2)
VALUES ('T1', NULL, 'T1F2')
    , ('T2', 'T2F1', 'T1F2');

SELECT RefNo, field1, field2 FROM dbo.RawTable; 

GO

BEGIN TRY

    BEGIN TRANSACTION;

    INSERT INTO dbo.Fact 
    (RefNo, field1, field2)
    SELECT RefNo, field1, field2
    FROM dbo.RawTable AS T;

    TRUNCATE TABLE dbo.RawTable;

    COMMIT;

END TRY
BEGIN CATCH

    --If we are here and there are open transactions, then rollback.
    IF @@TRANCOUNT >= 1
    BEGIN
        ROLLBACK;
    END

    --We want the error to bubble back to the client so they know something went wrong.
    ;THROW;

END CATCH

GO

--Show some data.
SELECT RefNo, field1, field2 FROM dbo.Fact; 
SELECT RefNo, field1, field2 FROM dbo.RawTable;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.