I'm working to add approval workflows in our system that manage CRUD operations for our business element.
So far, I've used an optimistic locking strategy to control race conditions when two users try to edit the same item. The user whose request is first saved wins and the other user must try again with the new value.
However, this does not work very well once we start soliciting approvals because an approval workflow can take several days and may require the approval of 4-5 people. If I serialize the approved requests (in the order in which they were approved) and apply them one at a time with Optimistic Locking, the user who lost the race is not happy because he jumped through so many tires in vain.
One strategy is to pessimize the element before invoking the approval workflow. However, this seems problematic for two reasons:
- An item may be locked for days, resulting in it
Frustration for other users. (I could avoid that by putting a
Timeout for lock and attribute-level locks for minimization
- Another of our use cases is Bulk Edit, where I would
may need to check if locks on 100 items are enabled
This can increase latency and decrease performance.
Most of the questions I've seen in this forum are about using two tables (one is the actual table and one for pending changes), not the management of locks. eg: pending and approval process
Conversely, there are questions about blocking, but without time-consuming approval processes. For example, should I lock lines in my cloud database while they are being edited by a user?
I think this issue is likely to be fairly common as many systems use permissions to restrict data manipulation. What are some of the common ways to solve this problem?
Thank you so much!