5

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)?

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).

Overview to demonstrate the crucial point:

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();
    }
}

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();
    }
}
nodots
  • 1,450
  • 11
  • 19
  • When I try purely in TSQL I get the error "The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction." - Just reading up on doomed transactions. – Martin Smith Mar 15 '11 at 16:35
  • Didn't find any documentation that explicitly states which errors lead to the transaction being aborted (or rendered uncommitable) but this error obviously appears to be one of them! – Martin Smith Mar 15 '11 at 17:07
  • Indeed, the lack of specific documentation on that topic is almost as annoying. – nodots Mar 15 '11 at 20:27
  • Just an extra comment SqlConnection, SqlTransaction and SqlCommand all have dispose methods so could probably benifit from being wrapped with using statements – Manatherin Mar 16 '11 at 09:13
  • Drastically amended my answer - hopefully it will help you address your problem. – Adam Houldsworth Mar 16 '11 at 09:25
  • In BOL, in the "Controlling Transactions" topic, it says "If an error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction" - but as @Martin says, there doesn't seem to be any decent documentation around on which statements/errors that sentence applies to. – Damien_The_Unbeliever Mar 16 '11 at 11:38
  • Ignoring the particular error you are getting, is there any reason why you'd want to insert into the table before the table structure is properly defined? – Adam Houldsworth Mar 16 '11 at 12:13
  • @Adam: Valid question indeed... :) The answer is that we're dealing with the typical mess of a legacy system (tons of existing data of questionable quality). Many tradeoffs had to be made. In this special case, we're opting for a "apply the constraint if you can" approach (the original context is applying the constraint to an existing table). It's definitely not my preferred design approach. ;) – nodots Mar 16 '11 at 12:29

2 Answers2

1

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.

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • I noticed when running the sample code the OP provided that the transaction would lose it's connection, but the connection would remain open - very strange behaviour indeed. – Adam Houldsworth Mar 16 '11 at 12:08
  • Thanks for digging up the MS Connect item, so it seems I can stop looking for exhaustive documentation. :( – nodots Mar 16 '11 at 12:34
0

I don't think you can intermingle save point usage in scripts and in C#. I perform the following SQL:

BEGIN TRANSACTION

INSERT INTO Foos (Fooname)
VALUES ('Bar1')

SAVE TRANSACTION MySavePoint;

INSERT INTO Foos (FooName)
VALUES ('Bar2')

ROLLBACK TRANSACTION MySavePoint

COMMIT TRANSACTION

This will work in SQL, and will work with the following code:

using (SqlConnection conn = new SqlConnection("connectionString"))
{
    conn.Open();

    using (SqlTransaction trans = conn.BeginTransaction())
    using (SqlCommand comm = new SqlCommand("The Above SQL", conn, trans))
    {
        comm.ExecuteNonQuery();
        trans.Commit();
    }
}

If you attempt to trans.Rollback("MySavePoint"); it will fail because the trans object is not in control of the save point - it doesn't know about it.

If you split the SQL out into the two independent inserts and use the following code:

using (SqlConnection conn = new SqlConnection("connectionString"))
        {
            conn.Open();

            using (SqlTransaction trans = conn.BeginTransaction())
            using (SqlCommand comm1 = new SqlCommand("INSERT INTO Foos(fooName) VALUES('Bar1')", conn, trans))
            using (SqlCommand comm2 = new SqlCommand("INSERT INTO Foos(fooName) VALUES('Bar2')", conn, trans))
            {
                comm1.ExecuteNonQuery();
                trans.Save("MySavePoint");
                comm2.ExecuteNonQuery();
                trans.Rollback("MySavePoint");
                trans.Commit();
            }
        }

It will work as you expect.

Just a note, always dispose of objects that implement IDisposable - preferably in a using statement.

Further reading:

http://www.davidhayden.com/blog/dave/archive/2005/10/15/2517.aspx

Update: after faffing with this for a while using your sample code, it looks like due to the error coming from SQL, the transaction is being rolled back and becomes unusable. As has been stated in another answer, it appears as though in conjunction with SQL, the transaction is being forcefully rolled back regardless of savepoints due to certain errors. The only recourse for this is to re-order the commands run against the database and not rely on savepoints, or at least not rely on that action being in a savepoint.

Adam Houldsworth
  • 63,413
  • 11
  • 150
  • 187
  • Sorry, I don't see why the savepoint idea would break ACID. It actually IS a transaction mechanism, see http://msdn.microsoft.com/en-us/library/ms188378.aspx – nodots Mar 15 '11 at 16:20
  • @nodots oh I see, I was coming from the perspective of an unknown error causing the rollback, not a conditionally chosen rollback due to a non-critical action failing or not being needed. I still stand by the idea that failures that occur outside of the pre-designed conditions should fail the entire transaction - I see those save-points as a chance to run actions that don't impact the transaction. – Adam Houldsworth Mar 15 '11 at 16:22
  • @Adam: I clarified the original post. There's no intermingling taking place. The problem is that the savepoint mechanism __basically__ works, but not always (a failing ALTER TABLE ... ADD CONSTRAINT statement renders the transaction unusable). Plus, I couldn't find any documentation which kinds of errors can be rolled back to a savepoint and which cannot. – nodots Mar 16 '11 at 11:46
  • @nodots - it's not so much "...which kinds of errors can be rolled back to a savepoint..." - it's that SQL Server has taken over and rolled back the transaction completely. The save point (along with the rest of the transaction) is history by the time you're making your `Rollback` call. – Damien_The_Unbeliever Mar 16 '11 at 13:29
  • @Damien: Yes, I know. I think we're trying to express the same fact from different angles. – nodots Mar 16 '11 at 14:44