Implement throughput control in BizTalk orchestration when calling long-running wcf/web service

There is a common scenario that sometimes we have to call some long-running wcf/web services through BizTalk orchestration but the backend wcf/web services cannot handle the concurrent requests sent from BizTalk. In this case, the built-in throttling feature is not an ideal way to go because it cannot guarantee the stability of outgoing load. One workable appraoch is to implement a 'Retry Pattern' in your orchestration to resend the message while a call is not successful. However a more straightforward design should be to implement a simple throughout control logic in ochestration:

1. Use a database table to log concurrent count of instances and the threshold. (for example: when the threshold is set to 4, it means a subsequent instance will always wait for the condition: (concurrent requests < 4) = true before sending the request message.

2. In the orchestration, before calling the send port, query database to check concurrent instance count first. If the concurrent volume < threshold, set concurrent count = concurrent count + 1 and then go ahead to call the send port. Otherwise, delay for 5 seconds and then check again in a loop.

3. When the call finishes or fails, set concurrent count = concurrent count - 1 .

4. In this case, ideally each time only 4 calls are fired to invoke the WCF service which implements the throughput control function as the customer needs.

5. Some difficulty of the implementation is we cannot put the DB check and update logic inside orchestration because BizTalk is a multi-threading server application. Otherwise you will see a batch of SELECT queries(15+) get executed at the same time and returns the same concurrent results. After that, a batch of UPDATE queries are execute to do the +1 action. Then the concurrent value will be far higher than the threshold setting which no longer makes sense. The solution is using a stored procedure to perform the concurrent volume checking and +1 action and set readcommited  lock on it.

Expression_1:

 

The Stored Procedure:

==================================

 USE [ThroughputControl]
 GO
 /****** Object: StoredProcedure [dbo].[controlConcurrent] Script Date: 03/20/2013 17:02:53 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 -- =============================================
 -- Author: <Author,,Name>
 -- Create date: <Create Date,,>
 -- Description: <Description,,>
 -- =============================================
 ALTER PROCEDURE [dbo].[controlConcurrent] 
 
 @returnvalue int = 0 out
 
 AS
 declare @concurrent int
 declare @threshold int 
 declare @retVal int
 
 BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 
 
 exec @retVal = sp_getapplock 'Control', 'Exclusive', 'Session', 0 
 if (@retVal < 0 ) -- Not Granted. Some other thread is trying to cleanup this process
 BEGIN
 set @returnvalue = 1
 return @returnvalue
 END
 
 SET NOCOUNT ON;
 
 -- Insert statements for procedure here
 SELECT TOP 1 @concurrent = Concurrent_count, @threshold = Threshold from dbo.Control WITH(READCOMMITTED)
 
 If @concurrent < @threshold
 begin
 UPDATE Control SET Concurrent_count = (Concurrent_count + 1), Last_modified = GETDATE() where ID = 1
 set @returnvalue = 0
 end
 else 
 begin
 set @returnvalue = 1
 end
 
 return @returnvalue
 
 END
 exec sp_releaseapplock 'Control', 'Session'

 C# component to call SP: 

  ================================== 

  public int callSp(string spname)
 {
 
 SqlConnection sqlConnection1 = new SqlConnection(connectionString);
 SqlCommand cmd = new SqlCommand();
 int returnValue = 0;
 
 cmd.CommandText = spname;
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Connection = sqlConnection1; 
 
 
 cmd.Parameters.Add("@returnvalue", SqlDbType.Int).Direction = ParameterDirection.Output;
 
 sqlConnection1.Open();
 
 cmd.ExecuteScalar();
 
 returnValue = (int)cmd.Parameters["@returnvalue"].Value;
 
 sqlConnection1.Close();
 
 return returnValue;
 }
 

 Best regards,

 WenJun Zhang

Comments

• Anonymous
  September 10, 2014
  Hi - Can you comment on how you implemented the "When the call finishes or fails, set concurrent count = concurrent count - 1". Does it need to call another stored proc with sp_getapplock  in a while loop to first get the lock and then call update or can we just update the table directly via a ADO.NET call without going through the while loop and stored proc? Would appreciate any feedback as I am currently looking at your solution for our implementation where we have same needs.Thanks.