I'm working on adding Approval workflows around our system which manages CRUD operations for our business item.
Till now, I have been using an optimistic locking strategy to handle race conditions when two users try to edit the same item. The user whose request is saved first wins, and the other user has to try again with the new value.
However, this won't work very well once we start requiring Approvals because an Approval workflow may take several days to complete and may need the approval of 4-5 people. If I serialize the approved requests (in the order they were approved) and apply them one by one using Optimistic locking, the user who lost the race will not be happy since they jumped through so many hoops for nothing.
One strategy would be to use pessimistic locking and lock the item before entering the approval workflow. However, this seems problematic for two reasons:
- An item could potentially be locked for days on end, causing frustration to other users. (I could work around this by setting a timeout to the lock, and doing attribute-level locking to minimize friction.)
- Another one of our use-cases is Bulk Edit, where I would have to potentially check if 100s of items have any locks applied on them, which may increase latency and reduce performance.
Most questions I saw on this forum are about using two tables (one is the actual table and the other for pending changes), but nothing about managing locks. eg: Pending and Approval process Conversely, there are questions about locking, but without any time-consuming approval process around them. eg: Should I lock rows in my cloud DB while they're being edited by a user
I'm guessing this issue should be fairly common given how many systems use approvals to restrict editing on data. What are some of the common ways to solve this problem?