Recently I discussed creating robust multi-host workflow architectures with WF using the SqlWorkflowPersistenceService. I talked about an issue with the way workflow ownership is implemented and  showed some code that would fix the issue but that I also said was a hack. Ayende rightly pointed out that for high volume systems it would be a disaster. The point of that post was to highlight the problem.

Ideally the workflow team will fix the persistence service to recover from abandoned workflows more robustly - in the meantime the following stored procedure will unlock the abandoned workflows and make then runnable. The idea is to make this a scheduled job in the database running every few seconds - this is essentially what BizTalk does.

CREATE PROCEDURE dbo.ClearUnlockedWorkflows
AS
BEGIN
  SET NOCOUNT ON

  UPDATE InstanceState Set ownerID=null, 
                           ownedUntil=null, 
                           unlocked=1,
                           nextTimer=getdate()
  WHERE NOT ownerID is NULL and 
            ownedUntil < getdate()

  RETURN
END

The only oddity in the code is the setting of the nextTimer to the current time. The issue is that straight persistence (as opposed to unloading on a delay) sets this value to 31st Dec 9999 which is obviously not going to be reached for some time. Unfortunately the workflow will only be scheduled due to an expired timer so I have to reset the timer such that it will expire immediately. I can't think of any issues this would cause but if there's a scenario I haven't thought of all comments are welcome.