locked
SqlWorkflowPersistenceService - how does it work? RRS feed

  • Question

  • I'm going through one of the Hands-On Labs for Workflow Foundation, and am curious about the SqlWorkflowPersistenceService. The lab has me creating a workflow that outputs the time to the console, delays for 5 seconds, and outputs the time again. If I run this, the two times are 5 seconds apart, as expected. If I change the delay to one minute and 5 seconds, the two times are more than 1:05 apart (sometimes almost 30 seconds longer). Does the runtime periodically poll the database to see if there are any instances that need to be loaded from the database? How often does this happen?

    Thanks

    Tuesday, August 1, 2006 8:30 PM

Answers

  • Yes - there is a timer that the SqlWorkflowPersistence runs periodically to see if any timers need to "awaken".  This is controlled by the loadingInterval parameter to the constructor (or set via the configuration file).  You want this to be smaller than your smallest Delay value.
    Tuesday, August 1, 2006 9:20 PM

All replies

  • Another question - why is my workflow instance persisted if it's never idle? I have it set to just execute a couple simple code activities and then finish, and it's being persisted when the activities finish.
    Tuesday, August 1, 2006 9:07 PM
  • Yes - there is a timer that the SqlWorkflowPersistence runs periodically to see if any timers need to "awaken".  This is controlled by the loadingInterval parameter to the constructor (or set via the configuration file).  You want this to be smaller than your smallest Delay value.
    Tuesday, August 1, 2006 9:20 PM
  • <<why is my workflow instance persisted if it's never idle? I have it set to just execute a couple simple code activities and then finish, and it's being persisted when the activities finish.>>

    I don't know if it's the correct answer but I took your question as an opportunity to run SQLProfiler on a workflow made of just code activities and I've noticed that the Persistence Service called the stored procedure "InsertInstanceState" with param @status=1 : with @status=1 the stored procedure tries to delete the instance from the instanceState Table (it doesn't if you analyse the AND condition) . This is probably the reason why we get the WorkflowPersisted event. Status=1 means workflow completed.

     

    Here is my SQLProfiler dump :

    declare @p10 int
    set @p10=0
    declare @p11 uniqueidentifier
    set @p11=NULL
    exec InsertInstanceState @uidInstanceID='0D40BDB5-02C1-4F2C-B0DC-D192A3ECD2F2',@state=0x,@status=1,@unlocked=1,@blocked=0,@info=N'',@ownedUntil=''2006-08-02
    08:10:08:177'',@ownerID='932A192A-41CA-41A4-9C67-28E13103A3C2',@nextTimer=''9999-12-31 23:59:59:997'',@result=@p10 output,@currentOwnerID=@p11 output
    select @p10, @p11

     

    Code in InsertInstanceState stored procedure:

        IF @status=1 OR @status=3
        BEGIN
     DELETE FROM [dbo].[InstanceState] WHERE uidInstanceID=@uidInstanceID AND ((ownerID = @ownerID AND ownedUntil>=@now) OR (ownerID IS NULL AND @ownerID IS NULL ))

     

    So my guess is that somewhere (if you use refector , you'll probably find it, but reverse engineering is not allowed on this planet), when the SqlWorkflowPersistenceService method that calls this stored procedure is called, a WorkflowPersisted event is probably triggered.

     

    Wednesday, August 2, 2006 8:01 AM
  • I am going through that lab as well and I wonder how the persistence service identifies different instances of the runtime.  If I am running the same workflow from two different systems, how does the service keep the persistences separate?  Is it possible to persist a workflow from one system's runtime and load it and restart it from another runtime? 

    Michael S. Scherotter
    Solution Platform Product Manager
    Mindjet Corporation

    Wednesday, August 2, 2006 1:29 PM
  • Michael,

     

    the workflows instances are identified by a Guid (WorkflowInstance::InstanceId) and can be stored in the persistence store. An application running on one system can load the workflow, persists it and another app running on another system can reload it if it knows the workflow id; locking can occurs too.

    Locking is illustrated by Sonali (MS) here; I've tried to summarize this in my blog too.

     

    Wednesday, August 2, 2006 2:21 PM
  • Hi Serge,

    I am also running a SQLProfile session to work out why my workflow instances arent persisting.  I'm using WF 3.0 June CTP.  I find that when the InsertInstanceState SP is being called, it is being passed the following information:

    SP:Starting declare @P1 int
    set @P1=0
    declare @P2 uniqueidentifier
    set @P2='00000000-0000-0000-0000-000000000000'
    exec InsertInstanceState @uidInstanceID = '0518AF41-D282-44A0-9761-E929E42023E3', @state = 0x, @status = 3, @unlocked = 1, @blocked = 0, @info = N'Cannot serialize a Session while connected Cannot serialize a Session while connected', @ownedUntil = 'Dec 31 9999 11:59:59:997PM', @ownerID = default, @nextTimer = 'Dec 31 9999 11:59:59:997PM', @result = @P1 output, @currentOwnerID = @P2 output
    select @P1, @P2

    I cant seem to find out what a status of 3 means?  How did you find out that status 1 means completed?

    I guess my hint should be what is shown in the info param: Cannot serialize a session while connected.  If only I knew what that meant.  It sounds suspiciously like an nHibernate error message.  Quite strange.

    To hook up the persistence service I just did the following:

     

    NameValueCollection persistenceParams = new NameValueCollection();

    persistenceParams.Add("ConnectionString", "Data Source=(local);Integrated Security=SSPI;Initial Catalog=WFPersistence");

    persistenceParams.Add("UnloadOnIdle", Convert.ToString(true));

    wfRuntime.AddService(new System.Workflow.Runtime.Hosting.SqlWorkflowPersistenceService(persistenceParams));

     

    It should be as simple as that right?

    Thanks in advance

    Steve

    Monday, August 14, 2006 5:32 PM
  • Well, first mystery solved ... the status of 3 means the workflow was terminated.  I think I'm on the scent of the solution now ...

    Monday, August 14, 2006 6:00 PM
  • <<I cant seem to find out what a status of 3 means?  How did you find out that status 1 means completed?>>

    Hi Steve

    After some researches   it seems that this 'status' is the WorkflowStatus (there is a "WorkflowStatus" enum in the sdk).

    Monday, August 14, 2006 6:26 PM