What I have right now:
- A table with lot of records.
- About 200 users can select a record according to some rules based on the data in each record.
- There is a high chance that 2 or more users will select the same record.
- A user updates the record then saves it back to the db. That record won’t be selected again (soft deleted).
- Optimistic locking was tried and the performance wasn’t accepted by the PO.
- Our solution was once a user selects a records it updates a “lock” field in the record.
The problems:
- Collision is still occurring as users are still selecting the same record. I need to prevent 2 users from selecting the same record.
- We need to release the lock manually if the user takes no action for a long time.
I searched and it seems Amazon SQS was a good solution, but it had a problem that I can’t search the Queue.
Is there a good technology to solve my problem? I need to implement a searchable queue where messages are locked when selected and the lock expires after a certain amount of time.
You can allow the user to atomically select and “softly” lock the record by adding a timestamp to the record with when it was locked or when it will be unlocked. You prevent locked records being accessible by excluding them based on the time at locking and the lock field.
Manual unlock (for a rollback/cancel) can be implemented by setting the timestamp to back to epoch.
2