Concurrent Data Processing by Multiple People

Question

Let's say I have a page, accessible by several people concurrently, that lists information that must be processed in some way by the user, after which it is either marked as "completed" and effectively disappears, or is left unprocessed and thus left in the list to be tried again later. What is the best way to ensure that only one person is handling a given item in the list at a time, assuming that I must show the list (i.e. no one user/one item requirement, which is what I would prefer), and that any given user can click on any item in the list at any time?

This is an issue that has come up several times where I work, and I'm not really satisfied with the solution that I've implemented up until now. I was hoping someone had a better idea.

Basically my solution involved creating a table in a database to track who accessed what item and when, and then enabling/disabling features based on that (e.g. not allowing a user to edit info if it's checked out by someone else). It looks something like the following (using Oracle 11g...these are stripped way down for brevity here, with INFO columns acting as a stand in for all other columns that are irrelevant here).

CREATE TABLE "SOME_INFO"
(
  "INFO_ID"       NUMBER(19,0)      NOT NULL ENABLE,
  "INFO"          VARCHAR2(20 BYTE) NOT NULL ENABLE,
  "IS_PROCESSED"  CHAR(1)           NOT NULL ENABLE,
  CHECK ("IS_PROCESSED" IN ('Y', 'N')) ENABLE,
  CONSTRAINT "PK_SOME_INFO" PRIMARY KEY ("INFO_ID") USING INDEX
); /

CREATE TABLE "SOME_PERSON"
(
  "PERSON_ID" NUMBER(19,0)      NOT NULL ENABLE,
  "INFO"      VARCHAR2(20 BYTE) NOT NULL ENABLE,
  CONSTRAINT "PK_SOME_PERSON" PRIMARY KEY ("PERSON_ID") USING INDEX
); /

CREATE TABLE "PROCESS_HISTORY"
(
  "INFO_ID"       NUMBER(19,0)  NOT NULL ENABLE,
  "PROCESSOR_ID"  NUMBER(19,0)  NOT NULL ENABLE,
  "CHECKED_OUT"   DATE          NOT NULL ENABLE,
  "CHECKED_IN"    DATE
); /

"Checking out" a record from SOME_INFO for a given user is accomplished by INSERTing a new record into PROCESS_HISTORY with INFO_ID = the selected record's INFO_ID, PROCESSOR_ID = the users PERSON_ID, and CHECKED_OUT = the current time (CHECKED_IN is left NULL).

"Checking In" is accomplished by simply setting CHECKED_IN to the current time. An automated process is used to periodically check in records that have been checked out for more than some predefined maximum amount of time (the time varies from page to page based on what's involved in processing the info).

From this I can tell if a given record is currently checked out or not, and thus alter the page's behavior accordingly. Having said that, I can't help but think there's got to be a better way to do this. I'm hoping it's a common enough problem that there is a standard or semi-standard method of doing this that I simply haven't been able to find.

Answer 1

I'm hoping it's a common enough problem

Yes and no.

It's common.

But.

In many cases, a check-in/check-out feature usually indicates a design problem somewhere else.

Let's say I have [data], accessible by several people concurrently, that lists information that must be processed in some way by the user, after which it is either marked as "completed" and effectively disappears, or is left unprocessed and thus left in the list to be tried again later.

This is a very, very typical "work queue" situation. Often, you can save yourself mountains of grief by "assigning" the work to the individual "sub-queues" so that a person only sees work assigned to them. There's no complex "check-out/check-in"; instead the work is allocated to "sub-queues".

There's no check-out, because there's no possibility for one person to see work outside their sub-queue. The contents of their queue may change, as things are removed from someone else's queue and assigned to theirs.

If they decline to work on something, it may get assigned to another work queue. If something has waited too long it can go to a high-priority queue. You can have tiers of people working on slightly different things. Splitting up the work queue usually allows a lot of expansion, tuning and adjustment.

there is a standard or semi-standard method of doing this that I simply haven't been able to find.

Not really. "Standard" doesn't apply, since each situation is unique.

Answer 2

You have a typical case of resource-sharing problem. You should read about http://en.wikipedia.org/wiki/Semaphore_(programming) http://en.wikipedia.org/wiki/Monitor_(synchronization)

Probably you should use a transaction approach, not allowing different users work on the same data at the same time, but reading those two will clear your mind about the problems you are facing.