2

I am working on an application that requires manual approval of requests put in by customers. Now lets say there are 10 people involved in this operation and each of them has a webpage in front of them that serves one request after the other. The problem I am trying to solve is that the same customer request should not be served to multiple operations' people to prevent duplication of work. Right now I am solving it in the following way :

MySQL Table:

CREATE TABLE customer_request
(
   id             int PRIMARY KEY AUTO_INCREMENT,
   request_type   tinyint NOT NULL,
   request_body   varchar(100) NOT NULL,
   insert_date    timestamp DEFAULT current_timestamp,
   lock_status    tinyint,
   lock_time      datetime
);

Whenever I serve a request to an operations person, I set the lock_status column to 1 and and the lock_time to now(). When the request has been processed(accepted/rejected/escalated) the lock is released.

Secondly there is a background thread that clears any lock older than x minutes.

I think there should be a better way to handle this. Any help is greatly appreciated.

  • 1
    We should also map the customer_request to the operational personnel in a new mapping table. Under the mapping table, you can have columns like customer_request_id, operation_person_id and other cols. You can enforce the composite primary key on the set of cols, so that the same request_id cannot be mapped to multiple operational professionals. This way one customer_request would be assigned to at most one operational professional. When the request finally processed, the lock_status should be set to 0. – Karan Aug 20 '15 at 7:00
3

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

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 customer_query, include record_lock in the query. Then, when executing the update to update your lock_status and 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.

Example:

select request_id, lock_status, lock_time, record_lock
  from customer_request
  where lock_status = 0;
...
update customer_request
  set lock_status = 1, 
      lock_time = ...,
      record_lock = <somenewvalue>
  where request_id = <requestid>
    and record_lock = <originalrecordlock>;

Implications:

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

Notes:

  • 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).
2

What you're talking about is called a pessimistic offline lock. There are a number of ways of implementing them - the most common is similar to the way you propose, except instead of a background task to clear out the expired locks you simply clear them when you try to lock them and they're too old.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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