index > Windows Workflow Foundation > Transactional persistence of multiple workflow instances: pooled ...

Transactional persistence of multiple workflow instances: pooled ...

I?? trying to poke WF in various ways to make sure we can use it in a large project. The system will largely create the workflows in batches. For example every night we may create 4000 or so workflows.

If I?? dealing with a batch of 4000 I don?? think I??l be creating the workflows and starting them right away like this:
WorkflowInstance instance2 = workflowRuntime.CreateWorkflow(typeof(Workflow1));
instance2.Start();

I?? more thinking I??l be creating all the workflows, getting the WorkflowInstanceID, recording this against an item in the database which the workflow is tied to. This needs to be in a transaction as I need to ensure the workflow is persisted and the WorkflowInstanceID is also recorded against the relative item in the database.

Given this I have read that the ??F runtime supports flowing in ambient (TransactionScope) transactions for the WorkflowInstance.Unload method?? (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=533811&SiteID=1). That?? cool I can now do something like this:

??create workflow runtime and other inti here

// create multiple workflows and record their instance id elsewhere, do this in one
// transaction
using (TransactionScope scope = new TransactionScope())
{
foreach (BusinessItem businessItem in aBusinessItemCollection)
{
WorkflowInstance instance = workflowRuntime.CreateWorkflow(typeof(Workflow1));
instance.Unload();
CallSomeOtherUpdateToABusinessRecord(businessItem.ID, instance.InstanceId);
}

scope.Complete();
}

??start the workflows later

This works great until I get to the past 101 workflows. After 100 worflow get created I get a 'System.Workflow.Runtime.Hosting.PersistenceException??The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

As a simple test do this:
using (TransactionScope scope = new TransactionScope())
{
for (int i = 0; i < 100; i++)
{
WorkflowInstance instance = workflowRuntime.CreateWorkflow(typeof(Workflow1));
instance.Unload();
}

scope.Complete();
}

Then change the loop to loop for 101 and it throws the exception mentioned above.

Is its that WorkflowInstance.Unload() is not properly closing the DB connections or perhaps I?? not fully understanding what?? happing here? Sure I could bump up the connection pool limit but really I shouldn?? have to.

Any advise on suggestions for transactional consistency across multiple workflows (at transactional consistency across the creation as above) and also the connection pool being maxed out would be greatly appreciated.

rccopter

Well - the connections can't close yet can they? They are still being held open because the transaction is still being held open (it won't commit until you call close at the end of the using statement).

http://www.quicklearn.com/workflow.htm

Jon Flanders

Hi Jon, thanks for your reply.

Yes I thought that but one of the WF samples that dealt with transactions was explicitly closing connection within the transaction (see the sample http://windowssdk.msdn.microsoft.com/en-us/library/ms741694.aspx). I??e also done some further tests to prove this appears not be the case.

For example,

using (TransactionScope scope = new TransactionScope())
{
    for (int i = 200; i < 400; i++)
    {
        using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(Settings.Default.ferDummyDBConnection))
        {
            connection.Open();
            SqlCommand command = new SqlCommand
                (
                    @"INSERT INTO [FerDummy].[dbo].[FerCase]
                    ([CaseID] ,[WorkflowInstanceID])
                    VALUES
                    ('CASE:" + i.ToString() + @"', '" + instance.InstanceId + @"')",
                     connection
                );
            command.ExecuteNonQuery();

            // if (i == 275)
            //    throw new ApplicationException();
        }
    }
    scope.Complete();
}
If you run this code as is above (assuming the appropriate DB exists) whereby you passively dispose the connection via the using statement you can easily insert more records that there are connections in the pool, say 4000 records. Even though you close the connection the transaction is still upheld. You can prove this by uncomment the line that throws the ApplicationException() and you will find that no update were made to the database. However, and not surprising, if you remove the using statement and don?? close the connections you find the pooled connections get used up.

Normally you would just use one connection for the above, however I?? just trying to prove that you can call Close() and still participate in a transaction.

This makes me concerned with this code I mentioned above:

using (TransactionScope scope = new TransactionScope())
{
    for (int i = 0; i < 600; i++)
    {
        WorkflowInstance instance = workflowRuntime.CreateWorkflow(typeof(Workflow1));
        instance.Unload();
    }

    scope.Complete();
}

As this causes the pooled connections to be used up, it would appear that the System.Workflow.Runtime.Hosting.SqlWorkflowPersistenceService I?? specifying when I start my workflow runtime is not closing its connections when it?? operating within a TransactionScope.

Any thoughts?

rccopter

This suggestion may be too simple: What if you move the "using TransactionScope" inside your for-loop?

I.e. Do you need transactional consistency around the creation of all 4000 workflows? ...or around each the creation of each individual workflow (and the corresponding storage of its instanceId in the database)?

Michael.

Michael Herman -Parallelspace-

Hi Michael

Thanks for the suggestion, indeed we have tried that and it does work. It takes a fair while to run, which isn?? that bad think if we run it at night but this may not always be the case. It also means that we have to then handle what?? getting persisted at a more granular level, either on a workflow instance by instance basis or perhaps separate in sub-batches of less than 100 and then make sure the entire batch of 4000 was successful. Ideally we would like to do it in one shot and be done with it. If its expected behavior then fine we??l code around it, I just want to be sure at this code is low level in the system and we need it to be simple, easy to understand along with fast and robust.

I would have thought this would be a fairly common requirement, a system gets a batch of business items (e.g. orders) creates some workflows and then persists the workflows and within the same transaction records their instance ID against business logic elsewhere (according to my tests if its >101 workflows your going to have a problem if they have to be done in one transaction as discussed above).

rccopter

If you enlist a connection in a transaction and then close the connection the connection is kept open under the covers until the transaction is committed. If the connection could be closed there would be no way to tell Sql to commit the transaction and you'd get an orphaned transaction for that spid in sql (or it would abort). In the original code that you have you really do have 100 connections open at once and so on the 101st attempt to get a connection you timeout because all connections in the pool are busy (waiting for the transaction to complete).

If you really need to make all 4000 workflows consistent you'll need to share the same connection for each of the Unload calls. The SqlWorkflowPersistenceService can't do this in V1. If you write your own persistence service you can get this to work. The logic would be something like: open a transaction scope, create a connection, stick in threadlocal storage, create and call Unload on all of your workflows. In your WorkflowPersistenceService's implementation of SaveWorkflowState you'd look for the connection in threadlocal storage and use that connection. Just before you complete your transaction scope you'd close the shared connection.

Thanks,
Joel West
MSFTE - SDE in WF runtime and hosting

This posting is provided "AS IS" with no warranties, and confers no rights

Joel West

Thanks Joel, you stated:
"If you enlist a connection in a transaction and then close the connection the connection is kept open under the covers until the transaction is committed. If the connection could be closed there would be no way to tell Sql to commit the transaction and you'd get an orphaned transaction for that spid in sql (or it would abort). In the original code that you have you really do have 100 connections open at once and so on the 101st attempt to get a connection you timeout because all connections in the pool are busy (waiting for the transaction to complete)."

rccopter

rccopter,

I have reproduced your case with the same behavior. IMO, In the case of enlisted connection, the closed connection is placed into the subpool (restricted pool) where it is waiting for DTC notification such as complete/abort or timeout (default timeout is 1 minute) to return back to the general pool. The enlisted connection in the subpool can be pooled again, but only within the same distributed transaction, that?? why your sql test passed it.

Roman Kiss

reply 8