CODEWITHSUNDEEP
sql-servertransactionscommitrollback
Gutti
Gutti

Reputation: 657

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

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?

Upvotes: 3

Views: 6363

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28890

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

enter image description here

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

  1. You have an active transaction which is presenting log space reuse
  2. 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

Upvotes: 1

Related Questions