While using SqlWorkflowPersistenceService get SqlException deadlock in PersistenceDBAccessor.RetrieveNonblockingInstanceStateIds

Question

I'm using WF 3.5 and during the process of spinning up a couple of WorkflowRuntimes, I sometimes get the following exception:

System.Data.SqlClient.SqlException: Transaction (Process ID 108) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.HasMoreRows()
   at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
   at System.Workflow.Runtime.Hosting.PersistenceDBAccessor.RetrieveNonblockingInstanceStateIds(Guid ownerId, DateTime ownedUntil)
   at System.Workflow.Runtime.Hosting.SqlWorkflowPersistenceService.RecoverRunningWorkflowInstances()
   at System.Workflow.Runtime.Hosting.SqlWorkflowPersistenceService.OnStarted()
   at System.EventHandler`1.Invoke(Object sender, TEventArgs e)
   at System.Workflow.Runtime.WorkflowRuntime.StartRuntime()

I thought running multiple WorkflowRuntimes was ok. I'm spinning up multiples because I'm distributing the work load to different servers, but all are hitting the same database. Any thoughts?

Answer 1

Found the answer!

I should have used the SqlWorkflowPersistenceService constructor that enables locking:

SqlWorkflowPersistenceService Constructor (String, Boolean, TimeSpan, TimeSpan) http://msdn.microsoft.com/en-us/library/ms608171(v=VS.90).aspx

For more information: https://connect.microsoft.com/wf/feedback/details/541240/sqlexception-deadlock-in-persistencedbaccessor-retrievenonblockinginstancestateids