Record Lock

Question

I am working on an application in which many employees can log in to resolve customer complaints. If one employee presses the "Start" button for a complaint, the record should be frozen. Frozen, I mean that it should not appear on the screen of other employees (when the screen is updated on their machine using Ajax). Further, it should not appear only for a certain period of time. If the employee does not respond to the complaint within a few minutes, he should again appear in the complaints queue.

How do I manage this duration? I have one approach, when the user clicks "Start", I can save this element in the Cache object and it is valid only until its expiration. Then, when another employee screen is refreshed, I show only those elements that are not present and are not present in the cache. If it is present in the cache, it means that someone is visiting it. Am I going the right way? Or is there a better way to do this?

Answer 1

One thing you could do is have a “lock” column in the database and have that column in the DateTime column.

When the user clicks the "Start" button, mark the time in this column.

When retrieving records for display, filter out anyone that has a nonzero value in the lock column, or who has a value in the lock column, at least x minutes ago (which you can calculate using the math in the GetDate () function).

Answer 2

The easiest way to do this is to have a “RecordStatus” column or something like that, which you can set for a special value to indicate that it is still being created or modified.

This forces you to modify all your queries to specifically exclude records that have this status, but in the long run it works better and is more reliable than any logical locks on the database or at the application level.

Answer 3

We decided this in my company in two situations, independently, using some variations in the record record "record lock". In one system, a lock record is created when the record enters the “queue” as a new item; another lock record is created when the user retrieves the record from the queue.

In any case, you cut it when a user copy of your software opens a queue entry for work, the lock record should be in the database with some unique identification information about your user written on it. It must be unique to lock the record, and possibly the lock level (which means that two locks of the same level cannot exist in the same record) and identifies the user who opened the record as “owning” it for the purpose of making changes. This record is a lock should be kept as long as the user has a record open in his software.

The ability to “break” locks can be achieved by simply reassigning the lock to another user in combination with the usual polling of the lock object using the source software to lock the user; if at any time the user is no longer the owner, the lock was “broken”, and the user has the option to re-lock the lock (violation of another new user lock) or simply switch.

Now, if the user software crashes, they will still have a write lock. It may also happen that the operation to remove the lock failed (this can happen in situations where real data is in another database and you cannot provide a universal transaction). In this case, you will need a mechanism to remove the "lost" locks or force the user to delete them. If the elements to be blocked are time sensitive, you will need to create several redundant levels of removal of the locks (perhaps setting a time that once a minute breaks any lock older than X minutes or, as you know, is orphaned because this user is no longer logged in )