OpLockSet
Info
Help
Order
Easily set Windows opportunistic file locking and lock violation retry $15 - individual/site license $50 - unlimited/multi-location license Updated for Vista (April 2007) - Your database/accounting system is potentially at risk
- Microsoft Windows operating systems starting with NT were designed with client-server database support in mind. They use a strategy referred to as opportunistic file locking or "oplocks" to optimize client-server performance. Simpler file sharing database engine arrangements such as workgroup engines, which are not centrally managed and involve higher levels of concurrency, have not been anticipated under this philosophy, even though the most commonly used networked database and accounting software systems in use throughout the world use the simpler approach to avoid higher costs and overkill. With oplocks enabled by default, systems running on underlying database systems that are not client-server suffer from a number of problems, including database corruption. IT professionals are more often than not unaware of the issue and install systems without consideration of this now long historical problem and without regard to the database needs of programs that they install.
- What databases are at risk?
- A vast number of products running the full gamet of database systems including the Jet engine, Borland Paradox, dBase/Clipper/FoxPro/CodeBase, Btrieve 5.10a, Btrieve 6.15 workgroup engine and possibly Pervasive workgroup engines, TopSpeed and many others.
- What products use these databases?
- Microsoft Access and any product using the Jet engine (Visual Basic for example), Peachtree (all versions used in network situations), most likely most networked versions of QuickBooks, Advanced Accounting, TAS (all versions), Clarion, Delphi, ACT! and many, many others.
- What is the solution?
- You must add certain registry keys to disable opportunistic locking to all of your NT and higher PC's, including all XP Pro and 2000 PC's, if you are using any of the database types mentioned above.
- Why haven't I heard about this before from my database/accounting software vendor?
- Frankly we continue to be stunned by the lack of information about this topic provided by major database and accounting software vendors (particularly in the lower end, small network market which far and away comprises the greatest number of total users out there who are afflicted by this problem), who seem to ignore it and do not adequately inform their users through their support staff nor through their on-line nor other technical documentation. We have been routinely advising our database system VARs and accounting software users since at least late 1999 about this problem.
- Why you may need the Addsum OpLockSet utility
- Not only is this issue still obscure after all of these years, but setting the registry values correctly is tricky because of the very odd and inconsistent way Microsoft devised to deal with a problem that should not exist. We have found it awkward ourselves despite our long familiarity with the problem; it is very easy to make a mistake. Further, many sources report that the installation of some Microsoft updates or service packs cause the oplock setting to revert back to the default (which is something to keep in mind if you have enabled automatic updates; this is one reason why your computers might work fine one day but not the next). So this means that the oplocks setting must be re-checked periodically. This needs to be done not just on the server or gateway PC, but rather on all PC's on your network. And when you add a new PC/client to your network, you again need to make this esoteric change to ensure the ongoing health of any non client-server database systems you run. Our utility lessens the time it takes to make this critical change and ensures that the correct keys are implemented every time.
- Additional features
- The utility also allows you to check and set the lock request retry (lock violation delay) value, which is set by default at too high of a value. For legacy/DOS systems, the utility also allows you to quickly and easily set the WOW LPT timeout value so that LPT print jobs are not inordinately delayed.
- Very small (300K), fast, standalone executable requiring no other support files or DLL's.
- Can be used to disable or enable oplocks on any PC running Windows; can be run as many times as desired.
- If you are running client-server that does not mean you are safe
- Even though you may be running a client-server network (such as 2000 Server or 2003 Server), you still have a problem if your database/accounting systems are not running on client-server engines.
- Consequences of disabling opportunistic locking
- If you are running a client-server network and disable oplocks, adverse side affects are that your overall network performance will suffer and true client-server database engine system performance (but not stability) will possibly suffer the most. If, however, your networked database/accounting systems handled by your primary server are not running on client-server database engines, then the alternative is likely database corruption in your non client-server database systems. Possible strategies: upgrade/change your software to client-server capable systems; run non client-server systems on a single user basis only; move systems that are not client-server capable to a completely different network (the trouble is that the oplocks setting is all or nothing: you cannot enable it for some applications and disable it for others).
- Developer note
- Opportunistic file locking (or "caching") is an operating system issue, and has nothing to do with opportunistic (more correctly "optimistic") record locking. The concept of opportunistic file locking is not unique to Windows. Novell appropriately disables oplocks as the default. Linux also has an opportunistic locking concept.
- Oplocks and Vista
- As of March 20, 2007, Microsoft updated knowledgebase article 296264 (see link below) to include Vista Ultimate. It is not yet fully clear whether all of the six (five of which are sold in the U.S.) versions of Vista will need to be addressed in the same way. In view of the obvious similarities between Vista and XP Home/Pro and the inclusion of Vista Ultimate by Microsoft in the 296264 article, it would logically appear that the same issue as described above continues with all of the various releases of Vista. As of April 2007 we have accordingly updated this utility to be Vista compliant and have tested it primarily on Vista Home Basic. The updated utility now checks for administrative rights (all applicable versions of Windows). Where applicable the utility will tell the user that administrator access is needed and to right click the icon and "run as administrator." If not running as an admin, the user will be told. And it also now checks for compatibility mode (Windows 2000 and above). If the user is running in compatibility mode, the user will be told to disable that mode and try again.
- Special note for Advanced Accounting 6.x and higher users
- The Addsum utilities now includes the OpLockSet utility, so a separate purchase is not required.
- More information
- These are simply a few references on this topic. There are many others.
- Configuring opportunistic locking in Windows (Microsoft article ID 296264)
- Improving Performance of MS-DOS Database Applications (Microsoft article ID 219022)
- (In this article which appeared at least as early as March of 1999 under Q219022, Microsoft attempted to blame DOS applications that were incompatible with NT's file locking default, when in fact the issue relates to all multi-user databases running under Windows that were not client-server, as they later documented in connection with their own Jet Engine used by a non-DOS application called Microsoft Access 2000 - see below.)
- How to keep a Jet 4.0 database in top working condition in Access 2000 (see Opportunistic Locking (oplocks) on the Network File Server, Microsoft article ID 300216)
- Pervasive Knowledge Base (search on solution ID: psql6157 - Pervasive confirms, however, that oplocks is still an issue with respect to their workgroup engines)
- Opportunistic Locking and Read Caching on Microsoft Windows Networks (Data Access Worldwide white paper)
- Superbase Networking - Oplocks Explained (Superbase Developers article)
- Paradox file corruption (Delphi Magazine)
- CodeBase file corruption (Sequiter)
- Access (Jet) Database Corruptions Intersoft technical bulletin 1019