Should i disable OpLocks on a shared multiuser database? (1 Viewer)

Saphirah

Member
Local time
Today, 18:51
Joined
Apr 5, 2020
Messages
132
Hey everyone,

currently i am running a Frontend/Backend Split, networked Access database.
The backend lays on a seperate computer and we are using Shared Windows folder to connect the other PC's to the backend.
(If it helps, the command we use to connect to the server is simply "net use T: \\ServerPCName\Folder")
There are around 5 active users on the database. While the database runs quite fast on a local hosted environment, it is significantly slower on the network.
To improve the speed i did various research.

So recently i did read online that you should disable OpLocks on your Server/FileHost PC when using Access Databases in a multi user environment.
This can dramatically improve the performance using the database over a network.
But most of these pages/articles are more than 5 years old, an i can not seem to find any new information on this issue.

Will disabling OpLocks improve my Database Network speed? Is it recommended?
Thank you for your help.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:51
Joined
Feb 28, 2001
Messages
20,293
Recommend first that you use the "persistent connection" method mentioned by ArnelGP. Second, if you have speed issues, always look to assuring that you have an index on any field used in searching.

There is a thing in Access called OPTIMISTIC locking. OpLocks is a Windows thing - opportunistic locking. I highly recommend that you use Access optimistic locking or NO LOCKS. I am uneasy of OpLocks because they seem similar to something that used to cause database corruption by delaying data write-back to the back end file. But I can't find the articles in question. (Still searching.)
 

Saphirah

Member
Local time
Today, 18:51
Joined
Apr 5, 2020
Messages
132
try it and see for yourself.
there is also a method of creating a persistent connection to the linked database.
by "persistent connection" means opening the db (as opposed to others saying to just open a linked table).
Significantly Improve the Performance of Microsoft Access Databases with Linked Tables (fmsinc.com)
Thank you, this helped me a lot.
Recommend first that you use the "persistent connection" method mentioned by ArnelGP. Second, if you have speed issues, always look to assuring that you have an index on any field used in searching.

There is a thing in Access called OPTIMISTIC locking. OpLocks is a Windows thing - opportunistic locking. I highly recommend that you use Access optimistic locking or NO LOCKS. I am uneasy of OpLocks because they seem similar to something that used to cause database corruption by delaying data write-back to the back end file. But I can't find the articles in question. (Still searching.)
Enabling OpLocks used to cause database corruption 4-5 years ago. But to my knowledge they fixed that (Not 100% sure though).
In Access i am using no locks, because it is not necessary in my case.
But OpLocks makes windows lock the file until the user finished writing in it. When i do a lot of lookup and writing windows will continously lock and unlock the file which has some performance issue. By default OpLocks are enabled on windows.
My question is, does disabling OpLocks on Windows improve the performance of access databases without compromising the database or creating further issues? Does anyone have experience in that?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:51
Joined
Feb 28, 2001
Messages
20,293
The best answer to that is to consider older versions of Access on older versions of Windows where OpLocks were not available. Old versions of Access running with No Locks worked pretty well and performance was not generally very bad that I recall. Anything that reduced overall network traffic will improve performance, and I don't recall that many cases being reported where using No Locks was a big problem.

However, if you are worried about compromises, using Access Optimistic Locks is sort of a middle-of-the-road approach. More open than Pessimistic Locks but not as wide open as No Locks. Honestly, I used "No Locks" successfully pretty often so I can't speak against them.

The other thing you could do if you were REALLY worried about data integrity would be to look into transactions.


Normally, you would do this for complex sequences of queries, but if you are paranoid, this would at least allow for a roll-back if something DID go wrong.

I am still skeptical of the OpLocks feature simply because that lock isn't in Access, it is in Windows, and if something happens to your Access session, you might end up with a piece of a file structure that is locked long-term and needs special help to unlock it. Color me nervous in that regard.
 

Users who are viewing this thread

Top Bottom