Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question

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

share|improve this answer
    
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. –  Songo Jul 7 at 13:29
1  
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. –  ratchet freak Jul 7 at 13:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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