Implementing Linq-to-Sql Transactions Through WCF - Part II

Question

I started here: http://stackoverflow.com/questions/753599/implementing-linq-to-sql-transactions-through-wcf

Since then I came to the following. I am using basicHttpBinding for legacy, and my WCF client is unmanaged C++ (gSOAP). So I am using ASP.NET Session and enable aspNetCompatibilityMode on WCF. This works, so now I can handle sessions properly.

Now my idea was to allocate separate LINQ-to-SQL DataContext per each session, and in that way achieve transactional behavior. What I wanted to do is to store the DataContext instance inside the Session, do all the inserts without calling SubmitChanges() and then just call SubmitChanges() once to commit the transaction.

Unfortunately, there is a bug in LINQ-to-SQL which prevents you to fetch previously inserted data from the DataContext before you call SubmitChanges() on it (see here) And I need to do that, because before inserting tender's documents and headers I need to retrieve the tender itself. So this won't work (at least without completely refactoring the code and loosing most of LINQ-to-SQL's beauty...)

So now the question is: how do I properly implement the per-DataContext transaction through WCF? I can't propogate the TransactionScope from the client, because the client is unmanaged. I can't use the internal LINQ-to-SQL transaction because of the bug above. The only thing I have is ASP.NET Session (which works). Perhaps I can store the TransactionScope in Session somehow?

Answer 1

It seems to me that the important thing is that you reuse the same connection and transaction. I would use the constructor on the DataContext that takes an existing connection. After your session start up, begin a transaction by allocating the connection and the transaction. Store both the connection and the transaction in the session. Then on each call, recreate the DataContext using the same connection and transaction. When done, call a method that commits the transaction. On session timeout, you'll need to make sure that you roll the transaction back.

 public bool CreateTransaction()
 {
     var connection = new SqlConnection( connectionString );
     var transaction = connection.BeginTransaction();
     Session["DBConnection"] = connection;
     Session["DBTransaction"] = transaction;

     return true;
 }

 private DBDataContext CreateContextFromSession()
 {
      if (Session["DBConnection"] == null)
          throw new NullReferenceException( "No connection available for transaction." );
      if (Session["DBTransaction"] == null)
          throw new NullReferenceException( "No transaction available." );

      var context = new DBDataContext( (IDbConnection)Session["DBConnection"] );
      context.Transaction = (DbTransaction)Session["DBTransaction"];

      return context;
 }

 public Tender CreateTender( ... )
 {
     var context = CreateContextFromSession();

     var tender = new Tender { ... };
     context.Tenders.InsertOnSubmit( tender );
     context.SubmitChanges();

     return tender;
 }

 public void CommitTransaction()
 {
     var transaction = (DbTransaction)Session["DBTransaction"];
     transaction.Commit();
     Session.Remove( "DBTransaction" );

     var connection = (IDbConnection)Session["DBConnection"];
     connection.Close();
     Session.Remove( "DBConnection" );
 }

Answer 2

I automatically get worried as soon as people start mentioning "transaction" and "session" in the same sentence. In my opinion and experience, it simply doesn't scale to consider multiple WCF calls for a single transactional unit of work:

• it means you have blocking dependent on an external client
• lots of potential for unresolvable deadlocks - i.e. that can only timeout, rather than db-centric deadlocks which can be detected
• you are forced to use sticky load-balancing

Personally, I would try to work on a single unit-of-work. That might mean that the client packages up a complete request and submits it as an atomic operation, or it might mean that you do that separately via the session - i.e. you buffer the "what" over multiple calls, but only start doing something when you have everything you need.

Either way, the net result is that you end up with your unit-of-work operating over a single implementation, which should make either db-transactions or ambient (TransactionScope) transactions work fine. The advantage of the ambient transaction (in your scenario) is that it'll work across multiple data-contexts, but I expect it should be possible to fix the Single call (as per the related question) without too much headache.

Or wait until .NET 4.0, when I am assured that the offending bug is resolved. OK, that last probably isn't useful in the short term...