Locking database records

Question

What I have right now:

1. A table with lot of records.
2. About 200 users can select a record according to some rules based on the data in each record.
3. There is a high chance that 2 or more users will select the same record.
4. A user updates the record then saves it back to the db. That record won't be selected again (soft deleted).
5. Optimistic locking was tried and the performance wasn't accepted by the PO.
6. Our solution was once a user selects a records it updates a "lock" field in the record.

The problems:

1. Collision is still occurring as users are still selecting the same record. I need to prevent 2 users from selecting the same record.
2. 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.

Answer 1

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.

• Songo: Thanks for the reply. This sounds great, but this does the problem of multiple users selecting the same record. Between the moment a record is selected and updated to be locked another query may select the record. I was hoping for automatic lock handling like in SQS, but it seems inapplicable in mysql.
• ratchet freak: you can use an update with an extra where lock<now() clause to ensure no update happened to the lock field. Then you test how many rows were affected and select another record when it fails.