TransactionScope timeout

[louiskruger4c]

When running queries that take a long time (e.g. creating an index on a large table) we are getting timeouts:

Failure: The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.
Erroring script was run in a transaction and was rolled back.

When running the same query using --NOTRANSACTION it does not time out. This leads me to believe that the TransactionScope is timing out. Further reading revealed that TransactionScope has a hidden timeout setting of 1 minute (which corresponds to the timeouts that we are seeing).

I noticed that you increased the SqlCommand timeout to infinite in an earlier change. Would it be prudent to do the same for TransactionScope?

[cravelight]

Hmm... the only places I can find for setting command timeouts are in AliaSQL/source/AliaSQL.Core/Services/Impl/QueryExecutor.cs and both of those are set to 0. Is there another place that would control "TransactionScope"? As far as I can tell - everything is already set to unlimited timeouts.

I've looked into this as we have this issue from time to time also. Whilst we can work around it by manually running the timing-out script in SQL Server Management Studio, manually inserting this script filename into the usd_AppliedDatabaseScript table, and then rerunning AliaSQL, it's a bit of a pain.

I think what you want to do is set the transaction scope options. So in the file AliaSQL/source/AliaSQL.Core/Services/Impl/QueryExecutor.cs, replace the ExecuteNonQueryTransactional method with this:

public void ExecuteNonQueryTransactional(ConnectionSettings settings, string sql)
{
    //do all this in a single transaction
    var transactionOptions = new TransactionOptions {
        IsolationLevel = IsolationLevel.ReadCommitted,
        Timeout = TransactionManager.MaximumTimeout
    };
    using (var scope = new TransactionScope(TransactionScopeOption.Required, transactionOptions)
    {
        string connectionString = _connectionStringGenerator.GetConnectionString(settings, true);
        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (var command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandTimeout = 0;
                var scripts = SplitSqlStatements(sql);
                foreach (var splitScript in scripts)
                {
                    command.CommandText = splitScript;
                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        ex.Data.Add("Custom", "Erroring script was run in a transaction and was rolled back.");
                        throw ex;
                    }
                }
            }
            scope.Complete();
        }
    }
}

This actually does two things. The transaction timeout is now the maximum defined for the machine, but it also changes the isolation level used during the transaction, which may also improve performance, and if you're upgrading your database online, help reduce the potential for deadlocks. If you don't want to change the isolation level, simply choose the default one here.

The default maximum-allowed transaction timeout is 10 minutes, with the default transaction timeout being 1 minute, apparently. However, in my testing, my transaction was aborting at random intervals. I've seen ~2.5, 3.5, and 6 minute aborts. With the above code change, the script committed successfully.

If you need a transaction to run longer than 10 minutes, you'll need to change the machine level config.

As an alternative, it appears you can set the transaction timeout in the app.config. I don't know if this will just work with the current code, or whether the transaction options explicitly needs to be told to use the default timeout, Timeout = TransactionManager.DefaultTimeout. See https://stackoverflow.com/a/15764391 but note that if this value exceeds the machine maximum, it will be capped at the machine maximum.

Sources:
https://blogs.msdn.microsoft.com/dbrowne/2010/06/03/using-new-transactionscope-considered-harmful/
https://blogs.msdn.microsoft.com/dotnetinterop/2005/12/16/system-transactions-and-timeout/
https://stackoverflow.com/a/6402826

If you need long running transactions but don't want to change the machine default (and you should think about whether this is good idea!), you could try this option (untested by me):
https://stackoverflow.com/a/28844237