How to execute inserts and updates outside a transaction in T-SQL

Question

I have stored procedures in SQL Server T-SQL that are called from .NET within a transaction scope.

Within my stored procedure, I am doing some logging to some auditing tables. I insert a row into the auditing table, and then later on in the transaction fill it up with more information by means of an update.

What I am finding, is that if a few people try the same thing simultaneously, 1 or 2 of them will become transaction deadlock victims. At the moment I am assuming that some kind of locking is occurring when I am inserting into the auditing tables.

I would like to execute the inserts and updates to the auditing tables outside of the transaction I am executing, so that the auditing will occur anyway, even if the transaction rolls back. I was hoping that this might stop any locks occurring, allowing more than one person to execute the procedure at once.

Can anyone help me do this in T-SQL?

Thanks, Rich

Update- I have since found that the auditing was unrelated to the transaction deadlock, thanks to Josh's suggestion of using SQL Profiler to track down the source of the deadlock.

Answer 1

TranactionScope supports Suppress:

using (TransactionScope scope = new TransactionScope())
{

    // Transactional code...


    // Call a SQL stored procedure (but suppress the transaction)
    using (TransactionScope suppress = new TransactionScope(TransactionScopeOption.Suppress))
    {
        using (SqlConnection conn = new SqlConnection(...))
        {
            conn.Open();
            SqlCommand sqlCommand = conn.CreateCommand();
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.CommandText = "MyStoredProcedure";
            int rows = (int)sqlCommand.ExecuteScalar();
        }
    }

    scope.Complete();

}

But I would have to question why logging/auditing would run outside of the transaction? If the transaction is rolled back you will still have committed auditing/logging records and that's probably not what you want.

You haven't provided much information as to how you are logging. Does your audit table have Foreign keys pointing back to your main active tables? If so, remove the foreign keys (assuming the audit records only come from 'known' applications).

Answer 2

you could save your audits to a table variable (which are not affected by transactions) and then at the end of your SP (outside the scope of the transaction) insert the rows into the audit table.

However, it sounds like you are trying to fix the symptoms rather than the problem. you may want to track down the deadlocks and fix them.