Transaction support in Workflow Foundation

Posted on 8/6/2006 @ 2:47 PM in #Vanilla .NET by | Feedback | 5523 views

Around the March/April 2006 timeframe, I went on a whirlwind tour of the United States and I delivered my talk on Transactions at 6 different places (3 were thanks to INETA) across the country. (If you want me to come and speak at a location near you, please contact any of these individuals here.)

In each one of those talks, I insisted/emphasized that System.Transactions is important, and you’d better be paying attention to it. Folks who attended those talks are probably at an advantage. For those that are virgins to System.Transactions, I would recommend checking up on a number of articles I have written on System.Transactions and Transactions in general before continuing to read with this post.

Here is a convenient, but possibly incomplete list for your convenience. I’ll meet you at the other end and talk about Transaction support in Workflow foundation when you are done reading the below.

Fairly comprehensive resource: Chapter 11 in my book.

Articles:

System.Transactions: Implement Your Own Resource Manager   

System.Transactions and ADO.NET 2.0

Newer Blog Posts:

A Quick Primer on System.Transactions

System.Transactions: Choosing your Enlistment Method

The definitive TableAdapters + Transactions blog post

Older blog posts (pre Beta2), but still a good reading:

How do I convert a System.Transaction.Transaction to an IDBTransaction?

System.Transactions: Judicious use of Transactional FileSystem in Windows Vista

ParameterizedThreadStart - new .NET 2.0 Enhancement, and a QUIZ

Neat little sneaky trick using SQLCLR & System.Transactions

A very insidious Visual Studio 2005 bug - Re: System.Transactions

SqlTransactions inside System.Transactions - Important consideration re: TransactionScope and IsolationLevel

SqlTransaction vs. System.Transactions

So you’re done reading? Wow you’re quick !! :-)

Okay, so let us get back to Transaction support in Workflow Foundation. To make the long story short, you have Transaction support in Workflow Foundation – thanks to the System.Transactions namespace. In fact, there are well defined activities for establishing a TransactionScope, Compensation etc. It may be worth adding however that there are significant differences between how transactions are implemented in WF, and how they are implemented in WF that is specific to SharePoint 2007.

The best way to look at this is, is an example.

So I am going to go ahead and create a Sequential Workflow. (See the Hello World example of Workflow to get started). In there, I am going to drag drop a transactionscope activity.

Now, in order to use a transactionscope activity, I need to specify a persistence service, or else an exception would be thrown at runtime. You can create your own persistence service by inheriting from the abstract base class, or you can use the persistence service that comes with the framework.

Since I am lazy, I am going to use the persistence service that comes with the framework. To do so, create a database called WorkflowPersistenceStore on a SQL Server. Then go ahead and run the following two TSQL files that come with your .NET 3.0 installation:

%windir\Winfx\v3.0\Windows Workflow Foundation\SQL\<language>\SqlPersistence_Schema.sql

And

%windir\Winfx\v3.0\Windows Workflow Foundation\SQL\<language>\SqlPersistence_Logic.sql

Great, now add the following two lines to Program.cs, to ensure that your WorkflowRuntime understands this new funky persistence service you wish to use.

string connectString =
      "..valid connex string to WorkfkflowPersistenceStore database..";
workflowRuntime.AddService(new SqlWorkflowPersistenceService(connectString));

Now back to your workflow, drop a “transaction aware” Activity. You could use anything that works with System.Transactions, I am going to use SqlConnection. To do so, I am going to drop a CodeActivity and write the following code in it’s ExecuteCode handler.

private void codeActivity1_ExecuteCode(object sender, EventArgs e)
{
    string connectString = "Initial Catalog=Test;Data Source=localhost;Integrated Security=SSPI;";
    using (SqlConnection testConn = new SqlConnection(connectString))
    {
        SqlCommand cmd = testConn.CreateCommand();
        cmd.CommandText = "Insert Into TestTable (TestValue) Values (1)";
        testConn.Open();
        cmd.ExecuteNonQuery();
    }

}

Now this would work transactionally because it is inside a TransactionScopeActivity, and thus under a TransactionScope. But how do we know? Well you could check System.Transaction.Transaction.Current.TransactionInformation at debug time, but I am going to throw in a second activity that throws an exception. This exception will cause the entire transaction to rollback.

The second code activity looks really simple as shown below:

private void codeActivity2_ExecuteCode(object sender, EventArgs e)
{
    throw new Exception("Because I'm a party pooper!");
}

Now you will note that throwing such an exception is like throwing an exception all the way to the Workflow itself. So at this time, the workflow will want to quit. (Say Microsoft, how about giving us a Try Catch Finally activity?). One thing you could do here is, you can add a FaultHandlersActivity, in which you can add a number of FaultHandlerActivity instances. Each one of these is sitting and waiting for a specific Exception type to be thrown. I am going to listen for a System.Exception, you can be more specific just like regular .NET if you wish. And inside this FaultHandlerActivity, I am going to drop a third CodeActivity with it’s ExecuteCode as below.

private void codeActivity3_ExecuteCode(object sender, EventArgs e)
{
    Console.WriteLine("BOOBOO Occurred");

}

Great, your entire workflow should look like as below:

<SequentialWorkflowActivity ..somegoo..>
      <TransactionScopeActivity x:Name="transactionScopeActivity1">
            <TransactionScopeActivity.TransactionOptions>
                  <WorkflowTransactionOptions TimeoutDuration="00:00:30" IsolationLevel="Serializable" />
            </TransactionScopeActivity.TransactionOptions>
            <CodeActivity x:Name="codeActivity1" ExecuteCode="codeActivity1_ExecuteCode" />
            <CodeActivity x:Name="codeActivity2" ExecuteCode="codeActivity2_ExecuteCode" />
      </TransactionScopeActivity>
      <FaultHandlersActivity x:Name="faultHandlersActivity1">
            <FaultHandlerActivity ..somegoo..>
                  <CodeActivity x:Name="codeActivity3" ExecuteCode="codeActivity3_ExecuteCode" />
            </FaultHandlerActivity>
      </FaultHandlersActivity>
      <CancellationHandlerActivity x:Name="cancellationHandlerActivity1" />

</
SequentialWorkflowActivity>

The code behind should look like this:

private void codeActivity1_ExecuteCode(object sender, EventArgs e)
{
    string connectString = "..";
    using (SqlConnection testConn = new SqlConnection(connectString))
    {
        SqlCommand cmd = testConn.CreateCommand();
        cmd.CommandText = "Insert Into TestTable (TestValue) Values (1)";
        testConn.Open();
        cmd.ExecuteNonQuery();
    }
}

private void codeActivity2_ExecuteCode(object sender, EventArgs e)
{
    throw new Exception("Because I'm a party pooper!");
}

private void codeActivity3_ExecuteCode(object sender, EventArgs e)
{
    Console.WriteLine("BOOBOO Occurred");

}

Visually, the SequentialWorkflow should look like this:

And the faulthandlersactivity should look like this:

What I didn’t show here is that you can get a hold of the actual exception you threw in the workflow inside the faultHandlerActivity1. That’s pretty simple & straightforward, so I’ll skip that (basically create a private variable or property and assign the property).

Great, now let us go ahead and run our workflow.

You would note that the output looks like this:

BOOBOO Occurred

And that now rows are inserted in the Test database. (because the transaction rolled back).

Now, another important and interesting and related concept is a CompensatingActivitiy. In a CompensatingActivity, you can write code to UNDO an already completed transaction. This could be done based on some condition that occurs afterwards. This is incredibly helpful in case of long running transactions where you may want to release the resources and get back to them under specific conditions. I will blog about CompensatingActivity in the near future.

Few Important notes of mention:

1. You cannot use a suspend activity inside a transaction scope. Well that makes sense. But you can use suspend within a workflow outside of a transaction scope, and you can have a compensation activity elsewhere in the workflow.

2. I noticed that even with volatile RMs and specified ReadCommitted isolation level, the transaction likes to promote to MSDTC. This is a bit yucky. First I figured that maybe the WorkflowPersistenceStore connection is causing the transaction to promote. So I removed the SqlConnection in my code activity and replaced it with my custom volatile RM (that I wrote for codeguru – the link is above somewhere). I noticed that the transaction still promotes. This is definitely yucky, and I hope this will be fixed before RTM.

3. As I said above, SharePoint’s WF transaction implementation is quite different. I can understand why, because I don’t really like how the transaction promotes on every damn thing. In fact, if WF was shipped “as is”, I would probably write my own implementation for transactions and not use WF’s implementation. (Hey, I think it sucks, but who the heck am I). But to be fair, I’m playing with an early CTP, so hopefully things will change.

Sound off but keep it civil:

Older comments..


On 8/10/2006 9:22:00 AM Scott said ..
Your transaction covers both your connection and the connection to the persistence database (assuming you are using the SQL persistence service). Two connections = MSDTC :(


On 8/10/2006 1:42:20 PM Sahil Malik said ..
Scott - I wrote my own Volatile RM and the transaction still promotes. How u 'splain dat? :)

SM