If you need to implement a custom queue process, one of the simplest solutions would be SQL Server table based solution. Especially, this is beneficial if there is a UI Control panel involved with capabilities to provide a real-time information about queue and dequeue progress.
BizTalk based Advent Geneva WorkFlow Manager (WFM) use a similar approach where the local SQL Server DB preserve information about WFM's activities in the "Activities" table. The only challenge is to prevent subscriber process from pulling the same record more then one time. This is why the "Status" field instoduced: when subscriber pulls data, the flag(status) must be updated. Flag also must be a one of the conditions for pulling data select statement, so when subscriber pulls, it selects only "Open" records (Status = 'O'). As soon as it red it - status must be flipped to "Processed" (Status='P').
Here is how it would looks in T-SQL:
*******************************************************************
Declare @ID as int
Set @ID= (Select Top 1 [ID] From [Queue] Where [Status]='O')
Update [Queue] Set [Status]='P' Where [ID]=@ID
Select * From Queue Where [ID]=@ID
*******************************************************************
At first, this approach looks solid. However, we have to remember that this queue table would be accessed by many processes simultaneously, so when subscriber try to read and update the same records at the same time - the SQL DB table immediately introduce a table lock. In the case of Advent Geneva WFM, all activities suddenly become frozen and the only quick fix in this situation is to restart subscription process(restart BizTalk host instances).
From my experience, at the time when I was part of New York Life Investments Geneva development team, our infrastructure team were slated to restart BizTalk host instance 3-5 times per day. They actually had a scripting approach to do it (can you believe it?).
Finally, at some point, I was sick and tired of hearing about Geneva WFM stall problems, so, after spending some time on research, I found the way or resolving this problem.
The fix is really simple and at the same time very elegant. Please see below:
Here is the same statement, but written differently:
****************************************************************
DECLARE @Updated TABLE
(
[ID] [int] NOT NULL,
[Status] [nchar](1)
)
UPDATE TOP (1) [Queue]
SET[Queue].[Status] = 'P'
OUTPUT INSERTED.* INTO @Updated
WHERE [Queue].[Status] = 'O'
SELECT * FROM @Updated
********************************************************************************
As you see from the script, I took advantage from the SQL Server build-in INSERT, UPDATE triggers. So, instead of two separate actions (select first and then update selected) - the updated stored procedure have only one action : "to update status". To output the result of a select query - I am using "INSERTED" trigger table.
As soon as we deployed modified script - all table locks disappeared and from that day on Geneva WorkFlow Manager was newer frozen, all activities were processed on time.
Subscribe to:
Posts (Atom)
