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.