Opportunistic locking breaking database?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dennis - 25 Feb 2005 21:09 GMT

We have a complex application with a multi-database backend, and allowing
multiple users on simultaneously. The application is in Access 2002 and
resides on a WinServ2003 server. Since upgrading to the WinServ2003, this
application has been consistenly becoming frozen/corrupted. The error message
generated is:

The database '\\path here\filename.mdb' needs to be repaired or isn't a
database file.

Some users will get this message, while other users continue to work without
incident. The only solution has been to boot everyone out of the database.
Then they can go back in and all is working properly (until the next time).
No actual repair has been required, but has been performed anyway, just to be
sure.

I was reading that Opportunistic locking might be a contributing factor, and
that it should be disabled on the server. This database application is used
simultaneously by dozens of users. The server also hosts other massive
database applications for other departments, so is VERY heavily used. My
questions are:

1) What are the ramifications/performance issues in disabling Opportunistic
locking on the server? (Does that mean that only one user at a time can be in
a database or table?)

2) Might this not even be relevant to the problem?

3) If not, what might be relevant to resolving this issue?

Any assistance on this would be greatly appreciated.

Thank you.

Reply to this Message

david epsom dot com dot au - 28 Feb 2005 06:19 GMT

> What are the ramifications/performance issues in disabling Opportunistic

People who request shared access to a file will only ever get shared
access. The system won't automatically try to upgrade them to exclusive
access. This will affect only file users who use shared files on the
network. Typically, only Access users, but potentially, users sharing
other very large files on the network.

Because the system won't 'opportunistically' try to open shared
files in exclusive mode, it won't be able to 'opportunistically'
cache a copy of the file on a users local workstation, even if
it thinks that user is the only user using the file. This can make
file access using 'shared file' applications slower, but can possibly
also make them faster (because the system might not waste time trying
to create or rollback exclusive locks and local copies).

> (Does that mean that only one user at a time can be
> a database or table?)

No. It just blocks a possible network optimisation when there
is only a single user of a file opened in 'shared' mode.

> 2) Might this not even be relevant to the problem?

We kept hoping that opportunistic locking would be fixed correctly,
and we kept on being disappointed, so then we became suspicious.
But file corruption is caused by network errors, and network
errors are caused by things like bad network cards, cables, drivers,
hubs, and routers.

(david)

> We have a complex application with a multi-database backend, and allowing
> multiple users on simultaneously. The application is in Access 2002 and
[quoted text clipped - 36 lines]
>
> Thank you.

Reply to this Message

Terry - 28 Feb 2005 10:03 GMT

We had file corruptions on various db applications, Sage, Act and Access
when we installed a new Server 2003. Disabling opp locks on the clients AND
server fixed the problems for us, they just evaporated away. Opp locks was
even an issue for us on the old, now defunct, Novell server.
Regards

> We have a complex application with a multi-database backend, and allowing
> multiple users on simultaneously. The application is in Access 2002 and
[quoted text clipped - 36 lines]
>
> Thank you.

Reply to this Message