Locking an item while waiting for human approval

Question

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:

1. 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.)
2. 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?

Thanks!

Answer 1

Add an enumeration field to your item called "Status," and a DateTime field to store the time that the item entered the approval process. Allow the Status field to have as many values as you need to represent points in the workflow. Some of the possible values for status might be Unapproved, Awaiting Approval, Approved and Not Approved.

Have another table that stores approvals. It should contain an itemID, a PersonID of the person approving, and a DateTime. When you want to obtain approval for an item, set its status to Awaiting Approval and set the item's DateTime field to Now.

Add a record to the Approvals table for this item each time an approval comes in. Periodically check the table for items awaiting approval having 4 approvals. When the approval count reaches 4, set the status of the item to Approved.

When you do your check, also check to see if the allowed time for approvals has expired. If that happens set the Status of the item to Not Approved.