Multi-user table access problem

[mscertified]

I've asked this several times and not gotten a very useful answer yet. Surely
someone must have done this...

This is a multi-user database.
I have a table that users can grab records from. No two users must grab the
same record. After the record has been processed, the record must be deleted
so it is not grabbed again.

I'm looking for a (simple?) technique to do this. Currently, I have a very
complex locking mechanism that involves inserting and deleting rows from a
locking table. This locking mechanism enforces single-threading but I'm
wondering if there is a method that allows multiple users to grab different
records at the same time.

 

[box2003]

The table you refer to, how do records intitially get into this table?
What do you mean by processing record, what are users doing to the record?

In similar situations, I have set a flag on an "editstatus" field I put into
some tables. When a user grabs a record, the flag gets set, and subsequent
users are messaged that the record is locked if they attempt to access. When
the record processing is complete, update, etc..., the record is again
saved/updated with new information, the flag is released, and the form and
affecting table is refreshed, allowing new users to retreived the record.

In your case, it seems like you could, transfer the record to a temp table,
on client side, run delete query for retreived record on server side,
processes completed, then record deleted from client table. I don't know if
this is on the right track or not.

 

[mscertified]

Replies inline.

The table you refer to, how do records intitially get into this table?

Inserted via a web page

What do you mean by processing record, what are users doing to the record?

Bring it up on a form and eventually save it to another table

In similar situations, I have set a flag on an "editstatus" field I put into
some tables. When a user grabs a record, the flag gets set, and subsequent
users are messaged that the record is locked if they attempt to access. When
the record processing is complete, update, etc..., the record is again
saved/updated with new information, the flag is released, and the form and
affecting table is refreshed, allowing new users to retreived the record.

In your case, it seems like you could, transfer the record to a temp table,
on client side, run delete query for retreived record on server side,
processes completed, then record deleted from client table. I don't know if
this is on the right track or not.

I still have the problem, while I am transferring, what if some other user
grabs it before I can delete it.

 

[box2003]

It will be necessary for you to add code so the first thing that gets done to
a specific record is set the editstatus flag to true. Then when other users,
who may want the same record, the first thing to happen is check the
edtistatus flag. If true, the record is free, if false, record in use. This
way the record is prevented from being accessed by users. You will likely
have to add code to your form to set and check the editstatus flag, this is
the first thing you will have to do.

Once the editstatus flag is set to true, you keep it set to true until, only
you delete it. Again, you will have to program in the logic to perform the
locking and release sequence.

Even simpiler would be to leave the record locked and then have a query
delete from the records with an editstatus flag set to true at conclusion of
each transfer event.

Any way you choose, you will have to develop your logic and sequence and
program this into your event.

 

[mscertified]

Thats pretty much the solution I have settled on. In my case, the 'in use'
column will be filled with the userid of the person grabbing the row. It's a
nuisance I have to a separate retrieval of the data since there is no way to
get it via the UPDATE statement.

-Dorian