Re: Strange multi-user timing phenomenon

Well my latest test is not good news. 2 independent users may both open a
table record with pessimistic locking and both edit the same record. Access
only complains when the .update is done. Eilther user can do it first then it
complains on the second one. I think this means I cannot use this technique
to place my locks at all. I don't know whether this is sometghing to do with
my Tools--Options--Advanced setting which says 'default locking is 'no locks'.

"Paul Shapiro" wrote:

I wouldn't insert and delete a record at all. It could be that just opening
the recordset will be sufficient to lock out other users. I think it should
be enough, so try that first. If that's not enough to stop the 2nd user, try
editing a data value in that record when you open the recordset. When you're
finished, update the recordset (if you had to edit data) and then close it.

I think if you open the recordset with pessimistic locking, the sql
statement to delete the record should be blocked. It won't be processed
until the lock is released. If you need to do any work on the locked row(s)
it should all be done via the recordset.

For testing, you should be able to open two copies of Access on your own
computer. Each copy of Access is an independent user. So grab the lock from
one Access instance, and then try to do the same from the other instance. It
should be blocked.

"mscertified" <rupert@xxxxxxxxxxxxx> wrote in message
news:FE576028-9C4B-4176-BFDB-B5DB0D2B1F98@xxxxxxxxxxxxxxxx
I tried your locking method using a perssimistic recordset and it appears
to
work. I'm not sure my last test really tested it as the 2 users did not
click
the buttons at exactly the same time according to my trace. Nevertheless,
my
problem now is that when I delete the processed record and release the
lock,
the record has not actually been removed from the table by the time the
next
user places his lock and grabs the same record. I'm using a DELETE SQL
command to do the delete, maybe I should try doing that via a recordset as
well ?

-Dorian

"Paul Shapiro" wrote:

When I had this issue I was using SQL Server, which makes transaction
control a very simple setting. But I would expect Access supports similar
declarative transaction control. When you design an Access form, for
example, you can specify the recordset locking type. If you set it to
lock
the whole table, no other user can open the form. Look at the
OpenRecordset
method's lock parameters. You definitely want to specify pessimistic
locking- optimistic "locking" actually means do NOT lock anything.

From the ADO documentation:
TYPES OF LOCKS:
adLockPessimistic: Indicates pessimistic locking, record by record. The
provider does what is necessary to ensure successful editing of the
records,
usually by locking records at the data source immediately before editing.
Of
course, this means that the records are unavailable to other users once
you
begin to edit, until you release the lock by calling Update. Use this
type
of lock in a system where you cannot afford to have concurrent changes to
data, such as in a reservation system.

I would try opening a fixed single-row recordset with pessimistic
locking,
and then edit some data value. When you finish your operation, call the
Update method to complete the edit and then close and release the
recordset.
That might be more than what's required, but it should reliably keep it
to a
single user at a time. If that works, you could see if the editing step
is
necessary. I would expect just opening a recordset with pessimistic
locking
should keep other users locked out but the documentation doesn't seem
clear
enough to be sure.

"mscertified" <rupert@xxxxxxxxxxxxx> wrote in message
news:17D79DE2-F4FD-4AC0-8AB8-2F3F28FF5992@xxxxxxxxxxxxxxxx
Thanks for the response.
I've posted questions related to this for about a week and have not had
much
feedback so far.
The essential problem is in a multi-user system to alllow different
users
to
grab records (read then eventually delete) from a table whilst ensuring
no
two users EVER get the same record.
My locking mechanism is to insert a record to a special table whose
only
purpose is to force single-threading. I insert a single record with a
single
primary key. I delete this record at the end of processing. If the
insert
fails I wait for .5 seconds and try again up to a max of 6 tries then
display
a message.
This is my 4th attempt to get this to work - all previous attempts
having
failed as well. However, I think I'm getting closer...



"Paul Shapiro" wrote:

Look at how you're "locking", because as you say, it's not locking
what
you
think it is. If your lock was exclusive and was working, then user 2
would
indeed not be able to place the "impossible" lock. If you want help
you
have
to give us a little more info. What are you locking and how are you
locking
it?

"mscertified" <rupert@xxxxxxxxxxxxx> wrote in message
news:646353B4-4FFF-4FEA-B33E-53C42D37324E@xxxxxxxxxxxxxxxx
Problem: 2 users appear able to insert the same (unique) primary key
to
the
same table.
I'm attempting to enforce single-threading of a process by inserting
a
record to a table. All users use the same primary key so only one
(theoretically) can succeed. Other users go into a wait and retry
loop.
After
processing, I delete the record thus releasing the lock. I've traced
the
processing for 2 users (using timer function). The processing
sequence
is:
user 1 places lock
user 2 lock fails
user 1 processes record
user 2 places lock (should be impossible!)
user 2 processes record
user 1 releases lock
user 2 releases lock

A couple of questions?
Can the timer function be compared between 2 different users?
I'm assuming that inserted and deleted rows are inserted and deleted
immediately and immediately visible to other users, is this the
case?






.