Network file access considerations
Visual FoxPro is an ISAM Style database. In 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. This process is clean and very efficient, producing better speed and efficiency than other client-server database and DBMS systems, however due to the nature of the technology behind it, it is also less protected from issues stemming from connection problems and/or windows cache write failures.
Although Windows NT technology has employed locking and caching techniques for many years, beginning with Windows Vista and moving all the way up to Windows Server 2012, Microsoft has defaulted to employing a process called optimistic locking under the SMB 2.0 and SMB 3.0 protocols.
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. (seeQ129202)
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.
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)
The only thing that developers utilizing ISAM database systems can do to help mitigate the problem posed by network communications issues and optimistic locking, is to perform more regular flushes (writes of buffered information) to disk. This process will both lessen performance, but may also still not resolve the issues that can arise from optimistic locking and network communications failures.
Our recommended solution is to place the data access inside a safe environment where file access and requests are not handled over the network at all. We recommend accessing our software via Remote Desktop Services and to have the data stored directly on the server managing the remote access. If file access is handled locally, and no network access is handled, the cache / locking / race conditions that can occur during read/write over the network are veritably eliminated.
If network file access is required, following Microsoft suggestion to downgrade to SMB 1.0 and disabling optimistic locking is the recommended solution. There are known side effects with performance and file access is going to generally be slower after doing this. This solution may still not eliminate all potential network file access issues, and has no effect on mitigating communications problems or failures leading to data corruption.
PTM Inc and Pluss Corporation believe that our first and only real recommendation to ensure both speed and data integrity is to implement a remote environment with local data access and ZERO network data access for utilizing our software.
One of our partners Alaska Software provides a utility for disabling / downgrading oplocks on newer Windows clients.