sql-server - SQL Server : BEGIN TRAN . .. COMMIT without ROLLBACK 不回滚取决于错误

Question

在 Microsoft SQL Server 中，我创建了一个测试表

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

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

然后当我运行时:

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;

我希望 Test 中没有任何内容，因为 insert (1, 'Three') 会生成错误

Violation of PRIMARY KEY constraint 'PK_Test'

但实际上 BookId = 1, 2, 4 的行在表中。

如果我SET XACT_ABORT ON，那么我会得到预期的行为。

然后在报错的时候再换一段代码

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

事务回滚有效

为确保我得到回滚，我应该将该语句包含在 TRY ... COMMIT CATCH ROLLBACK 语句中。

但是我还是很疑惑为什么不带ROLLBACK的BEGIN TRAN总是不行。它真的像我猜测的那样取决于错误类型吗？

Answer 1

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?

你是对的，这取决于错误的类型。Erland 的以下陈述帮助我更多地了解 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.

不同场景见下图

此链接包含有关每种行为的极好信息

Error and Transaction Handling in SQL Server

这个错误

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

发生的原因有很多。一些包括

1. 您有一个事件事务，该事务正在提供日志空间重用
2. 繁重的事务可能需要大量日志空间并且您的磁盘可能已满

附加引用:

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