insert and update

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?

Answer 1

It's difficult to provide a great solution for you without knowing a lot more about your application. Make sure the transaction is as short as possible. Consider putting the creation of the audit record before the transaction starts, because you want a record even if the transaction rolls back. There are more aggressive strategies like using Query Notifications. However, that could require a major rewrite of your application.

It might be interesting to use Profiler to see how long your transactions are taking.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 2

Hi Emy,

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: http://msdn.microsoft.com/en-us/library/aa260630(SQL.80).aspx. 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:http://msdn.microsoft.com/en-us/library/ms178104.aspx

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.

---

Iric Wen
TechNet Community Support

Answer 3

Hi Emy,

You can try one thing....

Login on your SQL server start Management studion there.

Open SQL Server Profiler and then start your application with some ppl trying to do some operation.

You will find exact the line which is causing deadlock....