SQL Server : BEGIN TRAN … COMMIT without ROLLBACK does not rollback depending on the error

Question

In Microsoft SQL Server, I create a test table with

CREATE TABLE [Test]
(
    [BookID] [int] NOT NULL,
    [Name] [varchar](512) NOT NULL,

    CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([BookID] ASC)
) ON [PRIMARY]

Then when I run:

BEGIN TRAN;

INSERT INTO Test (BookID, Name) Values (1,'one');
INSERT INTO Test (BookID, Name) Values (2,'Two');
INSERT INTO Test (BookID, Name) Values (1,'Three');
INSERT INTO Test (BookID, Name) Values (4,'Four');

COMMIT TRAN;

I expect to have nothing in Test as insert (1, 'Three') generates an error

Violation of PRIMARY KEY constraint 'PK_Test'

But actually rows with BookId = 1, 2, 4 are in the table.

If I SET XACT_ABORT ON, then I get the expected behaviour.

Then for another piece of code when the error is like

The transaction log for database 'MyDatabase' is full due to 'ACTIVE_TRANSACTION'

The transaction rollback works

To make sure I get rollback I should include the statement in a TRY ... COMMIT CATCH ROLLBACK statement.

But I am still wondering why the BEGIN TRAN without ROLLBACK does not work all the time. Does it really depend on the type of error as I guess?

When you need multiple statements to be atomic you should use a try/catch block. That is what they are for. — Sean Lange

TheGameiswar TheGameiswar · Accepted Answer · 2018-05-21T16:30:33

But I am still wondering why the BEGIN TRAN without ROLLBACK does not work all the time. Does it really depend on the type of error as I guess?

You are right,it depends on type of error.Below statement by Erland helped me understand more on different variations of error handling in SQL Server.

Error handling in SQL Server is a very messy story. I asked the same question as you did in comp.databases.sybase in 1993 or so. I don't remember exactly what answers I got, but I don't think they were very good.

But this is the story: when an error occurs in SQL Server, the batch may be aborted and the transaction rolled back. Or the statement may be terminated, and the transaction continues.

Please don't ask about the logic in this, because there isn't any. Data-integrity violations usually don't abort the batch. But a conversion error often do.

To Microsoft's defence it can be said, that many of these bad decisions were taken in California when the product was still Sybase. On the other hand, Microsoft has very much effort to straighten out this, rather the opposite.

See the below screenshot for different scenarios

This link has excellent info on each behaviour

Error and Transaction Handling in SQL Server

This error

The transaction log for database 'MyDatabase' is full due to 'ACTIVE_TRANSACTION

can occur for many reasons. Some include

You have an active transaction which is presenting log space reuse
Heavy transaction which may require lot of log space and your disk may be full

Additional reference:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6ae24a04-0ad3-4aba-b471-2bbbcd8d8626/with-the-transaction-primary-key-violation-error?forum=transactsql

SQL Server : BEGIN TRAN … COMMIT without ROLLBACK does not rollback depending on the error

1 Answers