This SqlTransaction has completed; it is no longer usable. - Cannot figure out what is wrong

Question

I have a piece of code that connects to the database to do logging; then calls a function and if that function is true, I call my database again to alter it. If it is false, I return a System.Web.WebException which I need to handle.

However, no matter how I handle it, when it returns this exception, my 'transaction' return that it's already finished. I've tried removing the 2nd part of my code, so that's not my issue at all, but I'll include it so it makes more sense.

Before this revision, I used two catch statements and in each I had a rollback method, but converted it to one catch in order to see if that caused the issue, but apparently not.

public bool CreateNewLicense(LogInformation logInfo, string product, string email, string seats, out string responseQLM, out string responseLogger, out ILicenseInfo license)
        {
            bool status = false;

            using (SqlConnection connection = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["#REDACTED#"].ConnectionString))
            {
                connection.Open();
                int logId = -1;
                using (SqlCommand cmd = new SqlCommand(SQL, connection))
                {


                    SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                    cmd.Transaction = transaction;

                    #region Parameter definition from method input.

                    cmd.Parameters.Add("@ActionID", SqlDbType.Int).Value = logInfo.ActionId;
                    cmd.Parameters.Add("@ActionDesc", SqlDbType.VarChar).Value = string.IsNullOrEmpty(logInfo.ActionDesc) ? (object)DBNull.Value : logInfo.ActionDesc;
                    cmd.Parameters.Add("@Timestamp", SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@CustomerEmail", SqlDbType.VarChar).Value = string.IsNullOrEmpty(logInfo.CustomerEmail) ? (object)DBNull.Value : logInfo.CustomerEmail;
                    cmd.Parameters.Add("@KeyValueAfter", SqlDbType.VarChar).Value = string.IsNullOrEmpty(logInfo.KeyValueAfter) ? (object)DBNull.Value : logInfo.KeyValueAfter;
                    cmd.Parameters.Add("@LicenseKey", SqlDbType.VarChar).Value = string.IsNullOrEmpty(logInfo.LicenseKey) ? (object)DBNull.Value : logInfo.LicenseKey;
                    cmd.Parameters.Add("@GUID", SqlDbType.VarChar).Value = logInfo.Guid;
                    #endregion
                    try
                    {
                        logId = Convert.ToInt32(cmd.ExecuteScalar());
                        transaction.Commit();
                        responseLogger = "Successfully Logged";
                        if (!_licenseHandler.CreateNewLicense(product, email, seats, out responseQLM, out license))
                            throw new System.Net.WebException("Error Creating License");
                        responseQLM = "Successfully made a new License Key:";


                    }
                    catch (Exception e)
                    {
                        if (e.GetType() == typeof(System.Net.WebException))
                        {
                            responseQLM = e.Message;
                            responseLogger = "";
                        }
                        else
                        {
                            responseLogger = e.Message;
                        }
                        try
                        {
                            transaction.Rollback();
                        }
                        catch (SqlException sqlException)
                        {
                            responseLogger += @"\n" + sqlException.Message;
                        }

                        throw;
                    }
                    status = true;
                    if (logId >= 0)
                    {
                        string alterSQL = @"UPDATE [PLISLMTTraceback]
                                                SET [LicenseKey] = @LicenseKey
                                                WHERE [LogIndex] = @LogId; ";

                        using (SqlCommand alterCmd = new SqlCommand(alterSQL, connection))
                        {

                            SqlTransaction transactionTwo = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                            alterCmd.Transaction = transactionTwo;
                            alterCmd.Parameters.Add("@LicenseKey", SqlDbType.VarChar).Value = string.IsNullOrEmpty(license.ActivationKey) ? (object)DBNull.Value : license.ActivationKey;
                            alterCmd.Parameters.Add("@LogId", SqlDbType.Int).Value = logId;
                            try
                            {
                                alterCmd.ExecuteNonQuery();
                                transactionTwo.Commit();
                            }
                            catch (Exception sqlAlterException)
                            {
                                responseLogger = "License Key Logging Failed: " + sqlAlterException.Message;
                                try
                                {
                                    transactionTwo.Rollback();
                                }
                                catch (SqlException sqlException)
                                {
                                    responseLogger += @"\n" + sqlException.Message;
                                }
                                throw;
                            }
                        }
                    }
                }
            }
            return status;
        }

    }

I'd like for it to do the rollback after throwing that exception.

Answer 1

throw new System.Net.WebException("Error Creating License");

You are throwing an exception, and then trying to rollback a transaction that you already committed. This can't work. Once you have committed it, you can't roll it back.

I'd suggest one of two approaches.

Approach One:

Change your logic to create the licence key up front then do a single INSERT to the database. This will be faster, hold very few DB locks (and for a short window of time) and not require any use of explicit transactions (since you will be doing a single insert). The code will likely be substantially simpler.

Approach Two:

Remove all use of transactions. Insert the record (basically how you do now). Then, without keeping a DB connection / transaction open, create the licence. If the licence was created successfully, then run a DB update (of the record you just inserted). If the licence was not created successfully, then run a DB delete (of the record you just inserted).