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

Question

Hi,

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

Why are you updating the auditing table? If you were only doing inserts you might help prevent lock escalations. Also have you examined the deadlock trace to determine what exactly you were deadlocking?

You can do this by enabling trace flag 1204. Or running SQL Profiler. This will give you detailed information that will let you know what kind of deadlock (locks, threads, parrallel etc...).

Check out this article on Detecting and Ending Deadlocks.

One other way to do auditing is to decouple from the business transaction completly by sending all logging events to a queue at the application tier, this minimizes the impact logging has on your business transaction but is probally a very large for an existing application.

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.

Answer 3

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