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.
- 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.