将事务回滚到失败的 ALTER TABLE ... 添加约束时的保存点 [英] Rollback transaction to savepoint on failing ALTER TABLE ... ADD CONSTRAINT

问题描述

有没有办法在事务中添加检查约束和，以防失败回滚到前一个保存点(而不是回滚整个事务)?

Is there any way to add a check constraint in a transaction and in case of failure rolling back to a previous savepoint (instead of rolling back the entire transaction)?

就我而言，当 ALTER TABLE ... ADD CONSTRAINT 命令失败时，事务无法回滚到保存点(尝试这样做会引发 InvalidOperationException).

In my case, when an ALTER TABLE ... ADD CONSTRAINT command fails, the transaction cannot be rolled back to the savepoint (the attempt to do so throws an InvalidOperationException).

概述以证明关键点:

SqlTransaction transaction = connection.BeginTransaction();

// ... execute SQL commands on the transaction ...

// Create savepoint
transaction.Save("mySavepoint");

try
{
    // This will fail...
    SqlCommand boom = new SqlCommand(
        "ALTER TABLE table WITH CHECK ADD CONSTRAINT ...", 
        connection, 
        transaction);

    boom.ExecuteNonQuery();
}
catch
{
    // ...and should be rolled back to the savepoint, but can't.
    try
    {
        transaction.Rollback("mySavepoint");
    }
    catch (InvalidOperationException)
    {
        // Instead, an InvalidOperationException is thrown.
        // The transaction is unusable and can only be rolled back entirely.
        transaction.Rollback();
    }
}

这里是要测试的准备运行的演示代码(您需要一个名为test"的数据集):

And here's ready-to-run demo code to test (you need a datase named "test"):

public class Demo
{
    private const string _connectionString = "Data Source=(local);Integrated security=true;Initial Catalog=test;";
    private const string _savepoint = "save";
    private static readonly string _tableName = DateTime.Now.ToString("hhmmss");
    private static readonly string _constraintName = "CK" + DateTime.Now.ToString("hhmmss");

    private static readonly string _createTable = "CREATE TABLE [dbo].[" + _tableName + "] ([one] [int] NULL,[two] [int] NULL) ON [PRIMARY]";
    private static readonly string _insert1 = "INSERT INTO [" + _tableName + "] VALUES (1,1)";
    private static readonly string _addConstraint = "ALTER TABLE [dbo].[" + _tableName + "] WITH CHECK ADD  CONSTRAINT [" + _constraintName + "] CHECK (([one]>(1)))";
    private static readonly string _insert2 = "INSERT INTO [" + _tableName + "] VALUES (2,2)";


    public static void Main(string[] args)
    {
        // Example code! Please ignore missing using statements.

        SqlConnection connection = new SqlConnection(_connectionString);
        connection.Open();

        SqlTransaction transaction = connection.BeginTransaction();

        SqlCommand createTable = new SqlCommand(_createTable, connection, transaction);
        createTable.ExecuteNonQuery();

        // Create savepoint
        transaction.Save(_savepoint);

        SqlCommand insert1 = new SqlCommand(_insert1, connection, transaction);
        insert1.ExecuteNonQuery();

        try
        {
            // This will fail...
            SqlCommand boom = new SqlCommand(_addConstraint, connection, transaction);
            boom.ExecuteNonQuery();
        }
        catch
        {
            // ...and should be rolled back to the savepoint, but can't
            transaction.Rollback(_savepoint);
        }

        SqlCommand insert2 = new SqlCommand(_insert2, connection, transaction);
        insert2.ExecuteNonQuery();

        transaction.Commit();
        connection.Close();
    }
}

推荐答案

我在 TSQL 中尝试时得到相同的行为.

I get the same behaviour when I tried in TSQL.

BEGIN TRAN

CREATE TABLE foo (col int)

INSERT INTO foo values (1)

SAVE TRANSACTION ProcedureSave;

BEGIN TRY
ALTER TABLE foo WITH CHECK ADD  CONSTRAINT ck CHECK (col= 2)
END TRY
BEGIN CATCH
    SELECT XACT_STATE() AS XACT_STATE
    /*Returns -1, transaction is uncommittable. Next line will fail*/

    ROLLBACK TRANSACTION ProcedureSave 
   /*Msg 3931, Level 16, State 1: The current transaction cannot be committed and
   cannot be rolled back to a savepoint. Roll back the entire transaction.*/
END CATCH

GO

SELECT @@TRANCOUNT AS [@@TRANCOUNT] /*Zero the transaction was rolled back*/

我没有在文档中找到任何说明哪些错误会导致交易以这种方式注定失败的信息.我认为 中不存在此类文档此连接项注释.

I didn't find any information in the docs that states which errors would lead to the transaction becoming doomed in this way. I think no such documentation exists from this connect item comment.

答案是，错误处理是逐案处理.这不仅取决于serverity，还有错误类型和上下文.不幸的是，有没有公布的错误处理列表不同错误的行为.在一般，只有服务器错误应该杀死连接和极端连接关闭服务器.但是当谈到语句中止与事务中止，很难总结规则——即视具体情况而定.

The answer is, the error handling is case-by-case. It depends on not only the serverity, but also the error type and context. Unfortunately, there is no published list of error handling behavior for different errors. In general, only servere errors should kill the connection and extremely ones shutdown server. But when it comes to statement abort vs transaction abort, it is hard to summarize the rules -- i.e. it is case-by-case.

这篇关于将事务回滚到失败的 ALTER TABLE ... 添加约束时的保存点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！