0

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!

  • 1
    Why would a business workflow (approval) come into play when designing locking mechanism for the data? Specifically, your record should most likely be unlocked in the db, and the fact that it is not approvable to others is a Business Rule and thus belongs to the Business Layer of your application. Using database locking to enforce business rules seems an expensive and unnecessary construct. What will happen if you need to restore a db backup while that lock is held? Can your db engine persist locking information across backups? – zaitsman Oct 18 '19 at 2:31
  • Good point. How would the business layer handle it? Would I just store the list of items (and possible attributes) that are being edited and then lookup if the item that the user is trying to edit is in that list? Would that scale in case of bulk-edit? – MadN Oct 18 '19 at 16:19
  • Just like it was suggested below, that information belongs on the record itself. – zaitsman Oct 18 '19 at 22:27
2

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.

|improve this answer|||||
  • Thanks. That makes sense. Would it be possible to avoid the periodic polling with something event-based? I'm assuming running a polling job would either be too slow for the users (say per hour) or too heavy for the system to handle (say per minute). – MadN Oct 18 '19 at 16:17
  • It depends on the database system that you're using. For example, SQL Server has Triggers. – Robert Harvey Oct 18 '19 at 16:22
  • But if approvals are coming into your system, you should already know about those. Do your checks when that happens. – Robert Harvey Oct 18 '19 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.