Re: Strange multi-user timing phenomenon

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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?
>>
>>



.

Relevant Pages

  • Re: using clustered index to optimize inserts ...
    ... I will try to explain locking in terms of Sybase docs... ... Allpages Locking: Allpages locking locks both data pages and index ... the data page is locked with an exclusive lock. ... Clustered Index: The datarows will be arranged as per the clustered ...
    (comp.databases.sybase)
  • Re: [PATCH 17/18] fs: icache remove inode_lock
    ... If you understand inode locking today, ... can understand the inode scaling series quite easily. ... filesystems to lock down the object without taking a global ... Per-zone is problematic. ...
    (Linux-Kernel)
  • Re: CSingleLock - known behaviour?
    ... It is better to design code that doesn't require locking. ... If you don't need the resource, don't lock it. ... magnitude less efficient, than locking once. ...
    (microsoft.public.vc.mfc)
  • Re: Strange multi-user timing phenomenon
    ... table record with pessimistic locking and both edit the same record. ... the recordset will be sufficient to lock out other users. ... update the recordset (if you had to edit data) and then close it. ...
    (microsoft.public.access.formscoding)
  • Re: CMultiLock example
    ... Seriously, though, locking is *mandatory* if two or more threads are accessing non-scalar ... lists ever be modified while the threads are running. ... without a lock. ... You must prevent that one piece of data is modified from multiple ...
    (microsoft.public.vc.mfc)