RecordLocks in Multi-user Environment

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We use Access 2000 the track call information in our call center (35-45 users at once). The problem we are having is the database locks periodically as if I have the RecordLocks property set to 2- "Edited Record". All of the forms, queries and reports are set to 1-"No Locks". When it locks, no one can do anything in the database until the user locking moves to the next record. Any thoughts on why this might be happening.
 
We use Access 2000 the track call information in our call center (35-45
Click to expand...
users at once). The problem we are having is the database locks periodically
as if I have the RecordLocks property set to 2- "Edited Record". All of the
forms, queries and reports are set to 1-"No Locks".
When it locks, no one can do anything in the database until the user
Click to expand...
locking moves to the next record. Any thoughts on why this might be
happening.

Er, it is happening because multi-user databases *must* lock records at
certain times to guarntee data integrity!

For example, say user 'A' displays a record 'R'. Then user 'B' displays the
same record 'R'. Then user 'A' changes field 'X' from 11 to 22, and saves
the record. Now user 'B' changes field 'Y' from 33 to 44, and saves the
record. Oops! User 'A's change (of 'X' from 11 to 22) is now wiped out -
invisibly to both users!

Jet (the database engine for Access) will automatically lock record 'R' at
approriate times to prevent this & similar cases occurring. The lock will
remain until the appropriate user(s) save the record or discard their
changes & start again. That's the way it's gotta be!

Up to Access 97, whole "pages" or records were locked. So locking record 'R'
would also lock any other records on the same "page". (That might be no
records, or a few records, or many records, depending on the record sizes.)
As from Access 2000 (?), you can get true "record level" locking, but I'm
not sure how (& don't have Access here to check).

HTH,
TC
 
Rick Brandt said:
But, If it's Access 2000 I wouldn't be so sure.

I have just seen instances of an A2K app being used by 3 people to do *inserts*
one at a time with a form and having all of them suddenly get "Record cannot be
saved... Object is locked" errors. This is with "No Locks" set on the forms and
with Record-Level Locking enabled. This was with a standard split app (MDE on
client PC, MDB shared on server).

I'm not even sure it was *record locking* or something else that was occurring,
but a big PITA at any rate. Eliminating a Domain Aggregate function in the
BeforeUpdate event seemed to cure it. At least that was all I changed and the
problem went away.
Click to expand...

I've asked david dot com to clarify this! (as it is partly new to me).

TC
 
Could you clarify please?

TC


david epsom dot com dot au said:
Avoid agregate functions, Memo's, and OLE fields.

(david)


users at once). The problem we are having is the database locks periodically
as if I have the RecordLocks property set to 2- "Edited Record". All of the
forms, queries and reports are set to 1-"No Locks". When it locks, no one
can do anything in the database until the user locking moves to the next
record. Any thoughts on why this might be happening.
Click to expand...
 
TC said:
users at once). The problem we are having is the database locks periodically
as if I have the RecordLocks property set to 2- "Edited Record". All of the
forms, queries and reports are set to 1-"No Locks".

locking moves to the next record. Any thoughts on why this might be
happening.

Er, it is happening because multi-user databases *must* lock records at
certain times to guarntee data integrity!

For example, say user 'A' displays a record 'R'. Then user 'B' displays the
same record 'R'. Then user 'A' changes field 'X' from 11 to 22, and saves
the record. Now user 'B' changes field 'Y' from 33 to 44, and saves the
record. Oops! User 'A's change (of 'X' from 11 to 22) is now wiped out -
invisibly to both users!

Jet (the database engine for Access) will automatically lock record 'R' at
approriate times to prevent this & similar cases occurring. The lock will
remain until the appropriate user(s) save the record or discard their
changes & start again. That's the way it's gotta be!
Click to expand...

But, If it's Access 2000 I wouldn't be so sure.

I have just seen instances of an A2K app being used by 3 people to do *inserts*
one at a time with a form and having all of them suddenly get "Record cannot be
saved... Object is locked" errors. This is with "No Locks" set on the forms and
with Record-Level Locking enabled. This was with a standard split app (MDE on
client PC, MDB shared on server).

I'm not even sure it was *record locking* or something else that was occurring,
but a big PITA at any rate. Eliminating a Domain Aggregate function in the
BeforeUpdate event seemed to cure it. At least that was all I changed and the
problem went away.
 
Avoid agregate functions, Memo's, and OLE fields.

(david)

Sharon Madison said:
We use Access 2000 the track call information in our call center (35-45
Click to expand...
users at once). The problem we are having is the database locks periodically
as if I have the RecordLocks property set to 2- "Edited Record". All of the
forms, queries and reports are set to 1-"No Locks". When it locks, no one
can do anything in the database until the user locking moves to the next
record. Any thoughts on why this might be happening.
 
When you EDIT a record, Jet must lock all related records
in related tables, in case you do something that invalidates
the related tables.

Memo's and OLE fields are actually related records in
a separate table, the Memo/OLE records must be locked
when you EDIT a record.

Memo and OLE records use PAGE LOCKING, so if you lock
a Memo, you may lock other records.

This locking is IMPLICIT, and Jet has to work out the
locking strategy. In fact, to make it simpler, Jet will
sometimes lock Memo and OLE pages even when the primary
record is not locked, because if you DO edit the record,
Jet would have to deal with the cascading locking and
update testing on (all of) the related table(s). And Jet
does not/ did not even have the ability to TEST memo and
OLE fields to see if they had changed...

.... Jet may not be able to apply 'no locks' to a record
containing Memo or OLE fields, and may automatically
upgrade the locking, and that may affect other records
as well.

--
If you use aggregate functions, the aggregate would
be invalidated if anyone changed one of the records
while Jet was trying to do the grouping, sorting etc.
What if some user changed a record from "A" to "B"
after Jet had counted the A's, but before Jet had
counted the B's ?? Jet will lock the records so that
it can get a snapshot of the data.

It will also be locking the pages of any related
MEMO fields, and also locking some indexes.

When you lock records, Jet makes a judgement about
how much you want to lock, and will upgrade record
locks to table locks if it seems like a good idea.

.... when you use aggregate functions, Jet will lock
records, and Jet may lock the whole table.

--

also,
Locking uses the SMB network protocol, and when there
are faults in the network, client, server, or protocol,
the locking can get messed up.


(david)
 
Ok, good info.

TC


david epsom dot com dot au said:
When you EDIT a record, Jet must lock all related records
in related tables, in case you do something that invalidates
the related tables.

Memo's and OLE fields are actually related records in
a separate table, the Memo/OLE records must be locked
when you EDIT a record.

Memo and OLE records use PAGE LOCKING, so if you lock
a Memo, you may lock other records.

This locking is IMPLICIT, and Jet has to work out the
locking strategy. In fact, to make it simpler, Jet will
sometimes lock Memo and OLE pages even when the primary
record is not locked, because if you DO edit the record,
Jet would have to deal with the cascading locking and
update testing on (all of) the related table(s). And Jet
does not/ did not even have the ability to TEST memo and
OLE fields to see if they had changed...

... Jet may not be able to apply 'no locks' to a record
containing Memo or OLE fields, and may automatically
upgrade the locking, and that may affect other records
as well.

--
If you use aggregate functions, the aggregate would
be invalidated if anyone changed one of the records
while Jet was trying to do the grouping, sorting etc.
What if some user changed a record from "A" to "B"
after Jet had counted the A's, but before Jet had
counted the B's ?? Jet will lock the records so that
it can get a snapshot of the data.

It will also be locking the pages of any related
MEMO fields, and also locking some indexes.

When you lock records, Jet makes a judgement about
how much you want to lock, and will upgrade record
locks to table locks if it seems like a good idea.

... when you use aggregate functions, Jet will lock
records, and Jet may lock the whole table.

--

also,
Locking uses the SMB network protocol, and when there
are faults in the network, client, server, or protocol,
the locking can get messed up.


(david)
Click to expand...
 
Back
Top