ADO.Net Async with Transactions... A Lesson Learned

Last night I was testing out a new WebApi Filter I've added to a project, its fairly simple, log some data that comes from a HTTP header to the DB.

This particular project has no Entity Framework or anything, just simple ADO.Net, in fact I think the project has 3 stored proc's in total.

I tend to Async all the things by default so when I originally wrote my DB code I had:

using (var conn = new SqlConnection(_connectionString))
{
     await conn.OpenAsync();
     using (var transaction = conn.BeginTransaction())
     {
          var command = conn.CreateCommand();
          command.CommandText = "[Api].[LogDeviceRequest]";
          command.CommandType = System.Data.CommandType.StoredProcedure;
          command.Transaction = transaction;

          command.Parameters.AddWithValue(//etc 

          try
          {
             await command.ExecuteNonQueryAsync();
          }
          catch
          {
             transaction.Rollback();
             throw;
          }
          transaction.Commit();
      } 
 } 

Looks fairly harmless.

Initial testing of me just poking around with my app showed it to be OK so I was happy and released it.... 20 mins later I had a shock when I looked at my DB looking unhappy.

Uh Oh....

Some very pretty locks there....

So I immediately started to look at my query, its fairly basic, if something exists update a timestamp, else insert new row. I tinkered with it and stopped it doing a key lookup but that wasn't the cause of the issue.

It was getting late so I decided to turn the feature back off and look at it again with fresher eyes this evening.

When looking at it this evening I realised I was calling the code above without awaiting it, I was simply firing and forgetting. Nothing obvious jumped out at me, until I removed the async parts of the code and made it syncronous. The deadlocks then went away. I could have left it like this but I wanted to see if I could make it async again more so for piece of mind.

Whilst sat pondering over this I remembered how async/await will by default try to return to the original syncronisation context after its await. https://blogs.msdn.microsoft.com/pfxteam/2012/04/12/asyncawait-faq/ explains alot of these things. I then had a theory (that wasn't based in a lot of knowledge but investigated anyway) that perhaps the transaction context and my syncronisation context was somehow messed up, was it trying to return on the original ASP.Net syncronisation context.

To test this I simply altered my await statements in the code above to use .ConfigureAwait(false);
When you do this you are telling .Net that you don't have to continue on the original SyncronisationContext continue on what ever you want.

Initial testing seemed fine again, so I deployed and triggered a load test (which previously gave deadlocks within minutes) to find the deadlocks all gone.

This does seem to point to the transactions not being committed properly and hanging around for a while, maybe when its trying to get back to the SyncronisationContext. A very weird issue, I wonder if its related to the issue TransactionScope has with async pre .Net 4.5.1. Hopefully someone can tell me :)

Hope this helps someone, it certainly reminds me to test things under load :)