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.
The Stored Procedure:
/****** Object: StoredProcedure [dbo].[controlConcurrent] Script Date: 03/20/2013 17:02:53 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
ALTER PROCEDURE [dbo].[controlConcurrent]
@returnvalue int = 0 out
declare @concurrent int
declare @threshold int
declare @retVal int
-- 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
set @returnvalue = 1
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT TOP 1 @concurrent = Concurrent_count, @threshold = Threshold from dbo.Control WITH(READCOMMITTED)
If @concurrent < @threshold
UPDATE Control SET Concurrent_count = (Concurrent_count + 1), Last_modified = GETDATE() where ID = 1
set @returnvalue = 0
set @returnvalue = 1
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;
returnValue = (int)cmd.Parameters["@returnvalue"].Value;