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); Win 7 and Server/SBS 2008 (July 2010); Win 8 and Server 2012 (January 2014) - 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.
- Fast, standalone executable requiring no other support files or DLL's.
- Can be used to disable or enable oplocks on any PC running Windows or to simply check the oplocks status of any supported Windows system; 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/2003/2008 Server), you still have a problem if your database/accounting systems are not running on client-server engines and in the case of 2008 Server, the extent to which your clients are all Vista/Win 7 or higher.
- 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 SMB2/SMB3: Vista, Win 7 and Server 2008; Win 8 and Server 2012
- Starting with Vista in 2006, the server message block (SMB) protocol that had been in use since the 1990's was updated to SMB2. Initially it was thought therefore that the need to disable oplocks no longer applied to this operating system.
- In March of 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. Then the reference to "Ultimate" was removed and by December of 2007, the article read:
The opportunistic locking registry keys are valid only for traditional SMB (SMB1). You cannot turn off opportunistic locking for SMB2. SMB2 was introduced in Windows Vista to enable faster communication between computer that are running Windows Vista and Windows Server 2008 or Windows Server 2008 R2.
- Vista, Windows 7 and Server 2008 use SMB2 by default and when communicating with each other. See MS 2028965. But, despite SMB2's promised various enhancements, initial releases created the all too familiar problem of data caching related corruption (see the 2028965 knowledgebase article). Further when communicating in a mixed environment and with say XP Pro PC's, SMB1 still comes into play which seems to indicate that oplocks related problems could still persist in mixed Vista-Windows7-XP Pro etc. environments.
- Windows 8 and Server 2012 use SMB3 by default and when communicating with each other. See for example MS 2696547. Disabling SMB2 or SMB3 is not recommended by Microsoft. We have not yet heard of reports relating to data caching problems with these operating systems. It would be expected that when communicating in a mixed environment and with XP Pro PC's, SMB1 still comes into play which could indicate that oplocks related problems could still persist in mixed Win 8-Win 2012-XP Pro etc. environments. As with SMB2, as far as we are aware one can also not turn off SMB3 opportunistic locking.
- Special note for Advanced Accounting 6.x, 7.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.
- Visual FoxPro applications - File access is denied article by Dan Macleod (published in January, 2012)
- Configuring opportunistic locking in Windows (Microsoft article ID 296264)
- A data corruption issue occurs when multiple users perform read and write operations to a shared file in the SMB2 environment
- 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