Our Visual FoxPro applications had been in wide use since the early 2000’s, and before that our Clipper based applications had been in use since the late 80’s. Visual FoxPro and Clipper are both are xBase-based prodcuts, and use an ISAM-based database system. Other products that use an ISAM type database include Btrieve and MySQL.
A key point is that in many ISAM database systems the client manages it own database table access. The client has access to the data tables and indexes so it can read and write them directly. The client relies on server based locking to maintain file integrity.
With thousands of installations worldwide, we’ve always had occasional reports of corruption in data tables. Many of our customers use a peer-to-peer network with a workstation OS as a file server, like Windows 7 Pro, Vista, etc. They then use this file server as another workstation. This is inexpensive and easy to set up, and doesn’t require an IT department (or even IT person) to maintain. However, it’s also easy to accidentally reboot the “server” since it’s just a workstation. This sometimes would cause corruption of a data table if a client had a file operation in progress.
In late 2010 we started to see an uptick in the number of corrupted data files. We analyzed our support requests and there was definitely an increase in the number of cases where the data files had become corrupted. It turned out that the problem was connected to a change made in Windows Vista that was carried through Windows 7, Server 2008, Windows 8, and Server 2012.
Opportunistic locking or “oplocks”
When a client needs to read a table, it can request an oplock on a file. This means that the server will give that client exclusive access to the file, if no other processes need the file. This allows a client to perform read-ahead, write-behind, and lock caching, which will increase performance for the client.
However, if a second process needs the file, the server will require the client to break the oplock. At this point, the client has to flush all cached data and release the oplock. (see Q129202)
Opportunistic locking is a feature of SMB. It’s an optimization that can increase performance for a client program making changes to a file on a server. Under SMB, it can be disabled. For SMB2, it’s not optional, it’s enabled all the time.
So, what’s wrong with opportunistic locking?
According to Microsoft:
“If a multiuser or single user database application accesses a common data store on a Windows NT file server using opportunistic locks (or OPLOCKS), it is possible for a given user to cache partial transactions on the client systems hard drive. This is a performance enhancement to the Windows client redirector to reduce network file I/O between the client and server. The data being cached on the client redirector is later written back to the server. However, in some cases, a client system may stop responding (hang), do a hard reboot, lose its network connection to the server, or experience any number of other technical difficulties. In such cases, the content of the local cache that has not yet been written to the server can be lost. As a result, the transaction integrity of the database structures on the server is compromised and the data on the file server can become corrupted.” (see Q224992)
It turns out that ISAM based databases are vulnerable to this kind of corruption because the client has to maintain it’s own access to the shared database on the server. In late 2010, Windows Vista and 7 adoption had reached a point where enough people were using them as their workstation-based file server to bring the problem on to our radar.
Once we had identified the problem, we researched a solution. Microsoft had two registry entries that would disable oplocks on the client and server for SMB. For SMB2, oplocks could not be disabled. The resolution in this case was to disable SMB2, which caused Windows to fall back on SMB. Then the registry entries could be employed to disable oplocks.
We updated our installer to make these registry entries automatically when needed. For existing customers, we created a standalone program that would make the registry entries. It’s a fairly simple Winforms application in C# that can be run with a user interface or in a silent mode.
The program is available on GitHub, and is called Oplocks.
This is certainly not a common problem, as most modern database system are not vulnerable to this type of corruption. However, I decided to document this just in case someone else runs into this problem.