Delete within transaction during error still occurs?

Question

BEGIN TRANSACTION
   DELETE FROM TableA WHERE Col1 = 'someval'
   INSERT INTO TableB (col1,col2,col3,coln)
   SELECT col1,col2,col3,coln FROM SomeOtherTable

COMMIT TRANSACTION

I was running into a scenario where the Insert into failed due to some formatting issues, but I also noticed the Delete succeeded. I thought the transaction would have prevented this? What am I missing. Obviously this is pseudo-code, but the real table names and code are nearly as simple. What the heck could have lead to this?

I suppose one solution would be to add try/catch around the insert and rollback on error, but I was under the assumption that it would auto roll back.

Thom A · Accepted Answer · 2023-08-04T14:40:51.670

The transaction simply means that any changes are all committed at the same time. If you encounter a non-batch aborting error then any prior changes (and any that occur afterwards) will still be committed when you reach the COMMIT.

For example, take the following table and sample data:

CREATE TABLE dbo.SomeTable (ID int IDENTITY(1,1),
                            SomeDate date,
                            SomeString varchar(3));
GO

INSERT INTO dbo.SomeTable (SomeDate, SomeString)
VALUES(GETDATE(),'abc'),
      (GETDATE(),'def'),
      (GETDATE(),'ghi');
GO

Now I'm going to DELETE and INSERT 2 rows, effecting 1 row with each operation inside a transaction. In one I'll provide an invalid date, an aborting error, and in the other a value that will cause truncation, a non-aborting error:

BEGIN TRANSACTION
    DELETE FROM dbo.SomeTable
    WHERE ID = 1
    INSERT INTO dbo.SomeTable (SomeDate, SomeString)
    VALUES('20231301','xyz')
COMMIT;
GO

BEGIN TRANSACTION
    DELETE FROM dbo.SomeTable
    WHERE ID = 2
    INSERT INTO dbo.SomeTable (SomeDate, SomeString)
    VALUES('20231201','mnop');
COMMIT;
GO

Notice the results you get back; ID 1 remains (batched aborted) but ID 2 doesn't (batch wasn't aborted):

ID	SomeDate	SomeString
1	2023-08-04	abc
3	2023-08-04	ghi
4	2023-08-04	jkl

If you want the transaction to be aborted when an error is encountered then use XACT_ABORT:

SET XACT_ABORT ON;

BEGIN TRANSACTION
    DELETE FROM dbo.SomeTable
    WHERE ID = 3
    INSERT INTO dbo.SomeTable (SomeDate, SomeString)
    VALUES('20231301','xyz')
COMMIT;
GO

SET XACT_ABORT ON;

BEGIN TRANSACTION
    DELETE FROM dbo.SomeTable
    WHERE ID = 4
    INSERT INTO dbo.SomeTable (SomeDate, SomeString)
    VALUES('20231201','mnop');
COMMIT;
GO

Notice that ID 4 is not DELETEd now too.

thank you! Will TRY/CATCH allow one to also avoid the delete from occurring if the insert fails? — bitshift, Aug 04 '23 at 14:40
On it's own, [no](https://dbfiddle.uk/iV6IK8XG) @bitshift , you'd need to also put a `ROLLBACK` into the `CATCH` and likely `THROW` the error. Why do all of that when you can tell the data engine to abort the entire current transaction on an error? — Thom A, Aug 04 '23 at 14:45

Delete within transaction during error still occurs?

1 Answers1