IGNORE_DUP_KEY, LINQ & Deadlocks

I am working on a project. The idea is to persist stack trace information for each test failure in a database so that we can automate/facilitate analysis of related test failures. For example, we can query for all tests that fail at a particular method or that have a subset of the given strack trace and so on. This allows testers to easily group together multiple test failures that arise due to the same reason and classify them as a single failure.

As I implemented these changes, I used LINQ from the beginning. But, recently an issue arose when I was testing reporting with multiple parallel test agents. The problem was that the entries for the same entity inserted twice. This would break analysis and so I created a unique index key on the columns that uniquely identify an entity. I set the IGNORE_DUP_KEY option on this index as well. Unfortunately, LINQ can't handle this. LINQ won't gracefully handle a duplicate insert.

On a side note, to handle this, I create a stored procedure to perform the check and insert within a serializable transaction and invoked this via LINQ. However, again, when going at it with multiple test agents, this approach breaks down because the database server kills one of the transaction as a deadlock victim. This will be propagated as a SqlException with an error code of 1209 which you may want to handle if you want to retry the submit after some random interval.