How to avoid ASP.NET SqlServer session to interfer with the ambient Transaction-Per-Request?

Question

Hi,

I am trying to use a transaction scope in a transaction-per-request pattern. So I have a http module that do (simplified):

private void Application_BeginRequest(object sender, EventArgs e)
{
    var scope = new TransactionScope(TransactionScopeOption.RequiresNew);
    PutScopeInHttpContext(scope);
}

private void Application_EndRequest(object sender, EventArgs e)
{
    var scope = GetScopeFromHttpContext();
    try
    {
        if (HttpContext.Current.Error == null)
        {
            scope.Complete();
        }
    }
    finally
    {
        scope.Dispose();
    }
}

Then, in my web.config, I have:

<httpModules>
    <clear/>
    <add name="OutputCache" type="System.Web.Caching.OutputCacheModule"/>
    <add name="Session" type="System.Web.SessionState.SessionStateModule"/>
    <add name="Profile" type="System.Web.Profile.ProfileModule"/>
    <add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
    <add name="UrlRoutingModule" type="System.Web.Routing.UrlRoutingModule, System.Web.Routing, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
    <add name="TransactionPerRequestWebModule" type="Acme.Web.TransactionPerRequestWebModule, Acme.Web"/>
</httpModules>
<sessionState mode="SQLServer" sqlConnectionString="Data Source=localhost\SQLEXPRESS;Integrated Security=SSPI;" cookieless="false" timeout="360"/>

Now, at what seem like randomly rate, roughly 1 page out of ten gives me the following error:

[SqlException (0x80131904): Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.]
  System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1951450
  System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4849003
  System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
  System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2394
  System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
  System.Data.SqlClient.SqlDataReader.get_MetaData() +83
  System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
  System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
  System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
  System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
  System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
  System.Data.SqlClient.SqlCommand.ExecuteReader() +89
  System.Web.SessionState.SqlSessionStateStore.DoGet(HttpContext context, String id, Boolean getExclusive, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags) +516

[HttpException (0x80004005): Unable to connect to SQL Server session database.]
  System.Web.SessionState.SqlSessionStateStore.ThrowSqlConnectionException(SqlConnection conn, Exception e) +229
  System.Web.SessionState.SqlSessionStateStore.DoGet(HttpContext context, String id, Boolean getExclusive, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags) +649
  System.Web.SessionState.SqlSessionStateStore.GetItemExclusive(HttpContext context, String id, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags) +48
  System.Web.SessionState.SessionStateModule.GetSessionStateItem() +117
  System.Web.SessionState.SessionStateModule.BeginAcquireState(Object source, EventArgs e, AsyncCallback cb, Object extraData) +487
  System.Web.AsyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +66
  System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

What I (think I) understand is that the connection to the ASP.NET session database is sometimes enlisted in my business transaction, and I get this error when my business transaction is completing first.

There is a couple of problem with that:

• I don't think the transaction for the session state should be the same as my business one. These are 2 separate concerns.

• It makes the transaction automatically escalate to a distributed (MSDTC) one, which impacts my performance.

  How do I decouple my business transaction from the ASP.NET session one?

  Thanks in advance,

  Julien

Answer 1

I'm not entirely sure of how your code is working, but you can suppress the ambient transaction like this, which may help:

using(TransactionScope scope1 = new TransactionScope())
{
     try
     {
          //Start of non-transactional section 
          using(TransactionScope scope2 = new
             TransactionScope(TransactionScopeOption.Suppress))
          {
               //Do non-transactional work here
          }
          //Restores ambient transaction here
   }
     catch
     {}
   //Rest of scope1
}