T-SQL transactions - is this begin/commit implementation sufficient?

Question

I'm creating a SQL Server batch file to create new database objects, insert data into tables, etc. If one of these actions fails, then I don't want any of the other actions to be committed.

Is the following wrapper sufficient to accomplish what I'm trying to do?:

BEGIN TRANSACTION

--script #1 - create Table1
--script #2 - create Sproc1
--script #3 - insert data load into Table1

COMMIT

So if SS encounters an error within any of the scripts nested within the transaction defined above then none of the SQL DDL or data loads will be committed, correct?

I'm assuming that no explicit ROLLBACK is needed here. Are there any scenarios where I would need to explicitly include a ROLLBACK statement?

Are you using InnoDb or MySQL? MySQL processes `CREATE TABLE` as a single transaction. So it will not be able to be undone even if you put it in an outer transaction wrapper. — Edward, Jul 09 '18 at 22:36
You'LL want to make use to `BEGIN TRY... CATCH` with an explicit `ROLLBACK`. — Thom A, Jul 09 '18 at 22:54
@Larnu - wouldn't the latest version of sql server simply rollback the current transaction behind the scenes if an error occurs after "BEGIN TRANSACTION" but before "COMMIT"? — user9393635, Jul 10 '18 at 15:59
@GordonLinoff I created a sql file with multiple sql statements including a sproc and wrapped all of this sql with "begin transaction / commit". I didn't get any sql compile errors so this setup was valid. I'm assuming that it enforced transactional logic for all nested sql but I didn't test this explicitly — user9393635, Jul 10 '18 at 16:01

score 1 · Answer 1 · answered Jul 09 '18 at 23:45

See http://sommarskog.se/error-handling-I.html#whathappens. Some errors will only terminate the running statement, and execution will continue on the next statement. That would be bad.

You can make this work by setting XACT_ABORT ON, ensuring that it's not set OFF in your script, and rolling back or closing the connection on error. EG:

SET XACT_ABORT ON
BEGIN TRANSACTION

--script #1 - create Table1
--script #2 - create Sproc1
--script #3 - insert data load into Table1

COMMIT

But as @Larnu noted, it's best to use TRY..CATCH and explicitly ROLLBACK the transaction.

Note that some DDL statements must be in their own batch, or be the first statement in a batch. So you'll have to use dynamic SQL or multiple batches in your script. And TRY..CATCH only works within a single batch.

that is interesting info about XACT_ABORT. I think that most non sql specialists would assume that a standard "BEGIN TRANSACTION / COMMIT" would achieve full rollback when an error occurs within a nested sql statement. Can you think of any real-world scenarios where someone would need to run "BEGIN TRANSACTION / COMMIT" without XACT_ABORT ON? — user9393635, Jul 10 '18 at 16:13
I'm not sure what you mean by the following statement: "Note that some DDL statements must be in their own batch, or be the first statement in a batch." Will you please elaborate? — user9393635, Jul 10 '18 at 16:19

score 0 · Answer 2 · answered Jul 10 '18 at 16:13

The easiest way to show this is going to be with some sample code. let's firstly , have a set up like you have:

BEGIN TRANSACTION;

    DECLARE @SQL nvarchar(MAX);

    CREATE TABLE dbo.Table1 (ID int);
    --Needs to be dynamic, as CREATE PROC must be in its own batch
    SET @SQL = N'
CREATE PROC dbo.Proc1 @i int AS

    SELECT ID
    FROM dbo.Table1
    WHERE ID = @i;';
    EXEC sp_executesql @SQL;

    --Works fine
    INSERT INTO dbo.Table1 (ID)
    SELECT 1;
    --Fails
    INSERT INTO dbo.Table1 (ID)
    SELECT 0/0;   

COMMIT;

Now, this will have errored, however, if we try this:

SELECT *
FROM dbo.Table1;

You'll notice this works, and returns data. The following statements also don't return any error:

EXEC dbo.Proc1 @i = 1;
GO
--Both work
DROP PROC dbo.Proc1;
DROP TABLE dbo.Table1;

None of the other statements were rolled backed on the divide by zero error, which isn't what you're after. Like I said in the comments, you need to use a TRY...CATCH; thus your batch would look something like:

BEGIN TRY
    BEGIN TRANSACTION Migration;

    DECLARE @SQL nvarchar(MAX);

    CREATE TABLE dbo.Table1 (ID int);

    --Needs to be dynamic, as CREATE PROC must be in its own batch
    SET @SQL = N'
CREATE PROC dbo.Proc1 @i int AS

    SELECT ID
    FROM dbo.Table1
    WHERE ID = @i;';
    EXEC sp_executesql @SQL;

    --Works fine
    INSERT INTO dbo.Table1 (ID)
    SELECT 1;
    --Fails
    INSERT INTO dbo.Table1 (ID)
    SELECT 0/0;   

    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION Migration;
END CATCH

Now, if we try any of the above statements, they will all fail:

--Doesn't work
SELECT *
FROM dbo.Table1;
GO
--Doesn't work
EXEC dbo.Proc1 @i = 1;
GO
--Deosn't work
DROP PROC dbo.Proc1;
DROP TABLE dbo.Table1;
GO

Like i say in the above comments, this means that the SELECT, EXEC and DROP statements all fail, as the objects don't exist.

T-SQL transactions - is this begin/commit implementation sufficient?

2 Answers2