I have a workflow that is designed to send shortcuts to different folders for a group of people that perform data entry tasks. I have a sql table, that has a queue number column with values 1 thru 15, I have a dateTime column, and I have a folder name. When a document comes thru the workflow, it query's the table, and sorts by the dateTime column where the dateTime is <= (NOW) and use the top row's queue number value to decide where to send the doc. The very next step after the initial query, is an update query that update the table with the current dateTime for the row that was returned in the previous query. What is happening, is several hundred documents are being processed at the same time, so about a dozen end up going to the same queues before it has a chance to update the table. Is there any way to "throttle" it down so that items that come thru are at least a full second apart from each other? The customer will not let me just use the round robin feature within Laserfiche because of high turnover, they don't want to maintain the default folder path for the users within the group. Any delay activity within the workflow would just delay the same dozen records which would just release them all at the exact same time after the delay. Any idea's would be welcome at this point.
Question
Question
Answer
I would try to solve this by atomically updating the table and selecting out the row that you updated in order to avoid the race condition. I believe you can use the OUTPUT clause for this, as in this SO answer.
The output clause is what seems to work best, this is what I ended up with, I really appreciate the input!
;with Update_batch
as
(
select top 1 * from dbo.WorkQueue With (xlock, rowlock)where DateTime <=(GETDATE()) and [Group]= 'eReq'
order by DateTime asc
)
update Update_batch
set DateTime = (GETDATE())
OUTPUT deleted.DateTime, deleted.EntryID, deleted.[Group], deleted.Name, deleted.Queue
You might find that a DateTime doesn't give you the precision you need to keep these strictly in order (in your select top 1). If that's a problem, look at having an integer order field that you update with 1 + the current max.
Replies
I still haven't used it, and would probably approach this the same way Brian suggested, however have you looked at wrapping in the SQL lookup/update/entry move in the Simple Synchronization Sequence? Just use something like the folder parent ID for the synchronization ID.
I appreciate the response guys, i'll look into it.
Why not use a scheduled workflow that runs every 5 min or so, gets the documents created in the last 5 min and assigns them to users? That would process documents one at a time.