Strange multi-user timing phenomenon

[mscertified]

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?

 

[Paul Shapiro]

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]

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]

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]

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]

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]

I have to do a Delete to remove the record I have processed. It is nothing to
do with the lock. I process the record from the table and then delete it - so
no-one else grabs it. Essentially the process is:
Place lock
Read record from table
Process record
Delete record from table <=== not completed
Release lock
What I was saying is that even though I have released the lock, the Delete
has not completed by the time the next user places his lock and then grabs
the same record.
I have tried placing a short delay after the Delete but have not tested it
yet.

 

[mscertified]

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'.