We utilize database connections to drive many of our workflows, but lately we've had a convergence of bad luck where large amounts of data comes in at once and trigger a big series of workflows. We follow the model that our professional services layed out for us where we have a database monitor feeding a dispatcher workflow. This dispatcher spawns a process for each incoming event and passes the event to the spawned workflow.
It would appear from our experiences that BAO does not throttle incoming work or gracefully handle more incoming work than its resources allow for. In particular we are seeing OutOfMemoryException errors. So I'm wondering if anyone has any experience with implementing such throttling and if so where was it applied? I have experimented with implementing queues in my database tables and then using stored procedures to ensure that no more than X number of processes are in "processing" state at a given time. Although this works for the most part, it creates its own set of problems as the queue can become stuck when errors are encountered and you have to have some sort of mechanism to purge/bypass work items that can't make forward progress for whatever reason. It's a huge coding effort just to maintain the flow of work and not where I want to spend my time.
I briefly considered maintaing a global context variable to maintain the number of active processes as well, but was worried about losing updates to this variable. There doesn't seem to be an "atomic increment" so having a process that reads the current count and then setting it could lead lost updates when another process running at the same time is doing the same thing. This in turn would lead to an inaccurate count of the number of in-progress work items.
Is there a better way to go about this? Our current architecture consists of a dedicate Access Manager, dedicated repo, and a dedicated CDP and HA-CDP. Other than implementing lots of throttling, is it possible to scale out our environment other than just adding more memory to the CDP/HA-CDP?