How does BOARD handle concurrent execution of procedures?
Hello,
We have a procedure which clears some cubes and then runs some data flows to reload the cubes. The procedure may be triggered by user activity. If two users cause the procedure to run at about the same time what happens? Does one run and complete before the other is executed? If not is there a way to single thread this kind of thing?
Thank you
Answers
-
Good question Peter DeGregorio,
If I can add a followup... is there any way to confirm if these processes run sequentially or in parallel by viewing one of the logs? ...which one?
0 -
Hi Peter,
Board will run the procedure at the same time for both users. Their security, screen selection or interactive selection defines for which elements the calculations are done. If both users are running the procedure for the same elements, then the final result will be the one from the user who ran the procedure last. You should always keep this in mind when you build procedures which will be used several times a day by many users and think about the selections that you are using.
You can also build a procedure in a way that it only runs for one user at a time. You will need a cube and an if condition based on that cube which checks at the first step if that cube is empty or not. If it is empty it will receive a "1" and the procedures continues and the cubes gets cleared at the end of the procedure. But if it already has a "1", then it will go to a group in your procedure which tells the user that someone else is running it at the moment. I always have a "Wait" after that message for a certain amount of time and then the procedures jumps back to the locking cube check and continues if the cube was cleared during the waiting time.
The only problem with that is, if the procedure gets interrupted while someone is running it, the lock cubes does not get cleared and nobody can run the procedure anymore, an Admin will need to clear the cube manually in that case.
Let me know if this was not clear or if you have any other questions about it.
Cheers,
Daniel18 -
Thank you Daniel Zillmann for your thorough explanation; without my getting into the boring details I'll just say it was helpful beyond the question at hand.
4 -
Hello Bob,
The Board server handles the concurrency at procedure step level, not at procedure level.
So if you want to handle concurrency at procedure level you can do this as Daniel Zillmann suggested.
By default board handles concurrency at procedure step level.
There are several write actions you can do on the server via procedure:
Dataflow
Datareader
Cube clear
Cube align
Normalize
While a datareader is running other users cannot perform write actions, so they will be queued.
If we run dataflows on the same portion of data the last one that is triggered will overwrite previous changes.
if we run dataflows on separate portions of the same cube, dataflows can run in parallel.
Dataflows never lock other write actions, so dataentry clear cube (use current selection) will run.
fi two users are running the same procedure in parallel on different slices of data and you are not using datareader or clear cube (without use current selection) you should not have problems.
We are restyling concurrency to improve it, many news are coming on this topic!
To summarize:
Board server always runs procedures in parallel, then the single procedure step may wait for a lock to be unlocked (like a datareader running) or start in parallel
If you want a procedure to be exclusive for the db so that when that's running nobody else can modify the db, you can use the procedure steps "Db exclusive lock/unlock". If you do not want to lock the full db but have a custom queue on a procedure feel free to use Daniel Zillmann's idea.
To come back to your original question, both the capsule log and the db log will show you the sequence of actions on the server and the user, so you will see who ran the step first.
11 -
Hi Daniel Zillmann,
excellent explanation! This is also what we do, when we want to make sure that only one user at a time can execute a certain procedure. Maybe you even got this inspiration from one of my colleagues during your last project with SDG?
One additional hint, though: Instead of directly showing a message that someone else is currently executing the procedure and forcing the user to click again, I usually use the "Wait"-Action together with a second cube (by User) as a counter. I then set an IF-Statement to try 20 or 30 times if the procedure is still in use:
- User A starts the procedure. -> The procedure is "locked"
- User B tries to start the procedure. -> IF-Statement finds the locking cube populated and redirects to another IF-Statement which checks how often User B has already tried to execute.
- User B's procedure waits 1 second, adds a 1 to the counter cube and tries again as long as my limit is not reached
- Only if the pre-defined limit has been reached by User B, he or she will get a message asking to try again later.
btw: You can clear the locking cube during your regular update routine to make sure that the procedure isn't locked forever, if something goes wrong.
Kind regards,
Gerrit
0 -
I sure did, it was Maximilian Hallerstede @SDG Group who taught me that.
I have actually seen your enhancement as well and I have used it a few times now. The counter prevents that the user is stuck in the procedure forever if the cubes do not get cleared. Definitely a good idea to do that.
Cheers,
Daniel1
