Locking for multiple user access ?

[Guest]

My application will have a table being inserted to via a web page.
The Access database will be in constant use by multiple users.
Periodically, users will need to pull items (read then delete) from the
queue in first-in first-out basis.
My concern is to avoid conflicts between pulling from the queue and records
being inserted via the web page (via ODBC connection) and also between
different users pulling from the queue simultaneously. I guess I need some
type of exclusive lock which will force other users to wait and retry. Has
anyone done anything similar?

 

[Sylvain Lafontaine]

Excerpt if you have a very strange business case, usually you don't have to
use such a lock when multiple users are accessing a database from a web page
and insertions/deletions/updates will be managed correctly by the ODBC
driver. You might have problems if the *same* record is edited by multiple
users but this is another story.

Try it and see what happens. If this doesn't work, come back here
(preferably by starting a new thread) and show us an example of your data
with an explanation of why it doesn't work.

 

[Guest]

Well since multiple users could be pulling from the queue at the same time,
they could well both be trying to read the same record. It is impossible for
me to try this since I have only a single logon id.
I was hoping someone could offer guidance or experience.

 

[Sylvain Lafontaine]

Yes, you must add some flag to tell that a record have been read by someone.
However, this will not be easy to do with ODBC and JET. With ASP, you could
use something like Application.Lock and Application.Unlock to make sure that
only one person at a time read a record and set its flag or use SQL-Server
instead of JET because it will be much more easier to code this kind of
thing on it using a stored procedure than with JET/ODBC.

 

[Jamie Collins]

My application will have a table being inserted to via a web page.
The Access database will be in constant use by multiple users.
Periodically, users will need to pull items (read then delete) from the
queue in first-in first-out basis.
My concern is to avoid conflicts between pulling from the queue and records
being inserted via the web page (via ODBC connection) and also between
different users pulling from the queue simultaneously. I guess I need some
type of exclusive lock which will force other users to wait and retry. Has
anyone done anything similar?

Not exactly what you are doing but this MSDN article I think covers
the issues:

How To Implement Multiuser Custom Counters in Jet 4.0
http://support.microsoft.com/default.aspx/kb/240317

with the caveat that I don't think a DBMS table is the best way to
implement a queue. Are you using ASP.NET?

Jamie.

--