I think your solution is fine in general. It's an example of pessimistic locking and invaluable if you want to spare resources or process high-contention data.
In your case, as the purpose of these operators is solely to process your customer requests, contention seems likely. So, you're locking the record ensuring that it does not appear in other operator's queries. That is, you can distinguish locked and "available" records using your
IMO there is one thing missing. This is clearly a record stored in a database (i.e. you aren't removing requests from a queue or some internal message bus - e.g. JMS, MQ). So, you want to ensure that the record you lock was not locked during the time you acquired it (lost update problem).
So, I'd recommend an additional "lock" field that is effectively a lock on the record. This is to ensure that only you have locked the record in advance. Usually this would just be called something along the lines of "lock" or "version" but for to avoid confusion with
lock_status I'll call it "record_lock".
When you query the
record_lock in the query. Then, when executing the update to update your
lock_time your update should also include a new where clause
where record_lock = /*the value retrieved during your query*/
This ensures that you are the only operator updating that record and that it hasn't changed since your read. How do you know it hasn't changed? The where clause stipulates that the
record_lock is as it was when you queried it. What would have changed it? You! When you do the update above, you should also include an update to the
record_lock to some new value (sequence, record-hash, datetime derived? it's up to you).
To summarise, read
record_lock on your query and ensure it's unchanged when you update (the where clause). Include update to the
record_lock in the update itself to indicate you've changed it - and so invalidate anyone else trying to lock this record. Once it's locked, it should be yours to work with providing these record all abide by your
locked_status to query.
select request_id, lock_status, lock_time, record_lock
where lock_status = 0;
set lock_status = 1,
lock_time = ...,
record_lock = <somenewvalue>
where request_id = <requestid>
and record_lock = <originalrecordlock>;
- Pessimistic locking requires some overhead in managing the lock.
- There is a risk of deadlock if you require multiple lock acquisition.
- three is a risk of a record not being processed that you address with your timeout threshold.
- I would only name it "status" if it's a genuine status field. If it's just a binary true/ false, just call it "locked", "locked_flag", "is_locked" or whatever according to your standards.
- Interesting aside... When we update lock_status, this update is using optimistic locking (using a record lock field). When you process the customer_request, we're using pessimistic locking (we've locked the record in advance).