Product Alert 

Problem sharing the PubAssist Database in a Windows Network

Submitted by Ron Lawrence, March 11, 2013

It has come to our attention through the plight of one long-time PubAssist user that there is a problem running Publishers’ Assistant with a shared database in a networked environment.  This problem can result in data corruption! 

This is a problem ONLY for users of the Epic edition, where the database is hosted in a network and multiple users are accessing the data at the same time from different computers.

This is NOT a problem for Epic users who are accessing their data locally, i.e., from the computer on which the database is located.

It turns out that this is not a new problem—though it seems that Microsoft hasn’t gone out of its way to let registered users know about it.  It is caused by features of the Windows file system called, "Opportunistic Locking" and "Read Caching".  The problem was made worse by the introduction of a new file system protocol, SMB2.  Microsoft introduced this protocol with Windows Vista and Windows server 2008, and has maintained it with subsequent versions.

This situation can be remedied, but it requires setting specific parameters in the Windows registry.  If you are not comfortable with this process, you should seek assistance from a knowledgeable person. If you have an active subscription to the Publishers’ Assistant maintenance agreement, feel free to contact me (Ron Lawrence) for telephone assistance. If you do not have a subscription, I can help but will have to charge my regular hourly fee. Or you can consult with an IT professional.

A word about editing the Windows registry

Before discussing the procedure further, I am compelled to highlight the following disclaimer from Microsoft:

WARNING : You can edit the registry by using Registry Editor (Regedit.exe or Regedt32.exe). If you use Registry Editor incorrectly, you can cause serious problems that may require you to reinstall your operating system. Microsoft does not guarantee that problems that you cause by using Registry Editor incorrectly can be resolved. Use Registry Editor at your own risk.

In other words, be careful.  It’s a good idea to create a “restore point” for your registry (i.e. back it up) before making these changes.  You’ll have to reboot your system before the changes will take effect.  If things don’t work when you reboot, you’ll want to be able to get back to where you were before your registry changes were made.  Here’s a sample Microsoft article for how to do this in XP.

http://support.microsoft.com/kb/322756#method3

There are many other articles on backing up and restoring your registry that are more specific to each operating system and some articles that deal with specific problem situations (like you can’t reboot).

I’m not planning to go into the details of using RegEdit here. If you are not comfortable with editing your registry, you should talk to a local IT specialist, or give us a call to talk through how best to make these changes.

 

Let’s cut to the chase

For those who simply want to know what they have to do, here it is in short.  All changes are made in the registry.  There are different registry settings for client machines vs. your server (presumably where your data is stored).

The relevant parameters may not currently exist in your Windows Registry.  If they do not, you will need to create them.  All of these parameters are type REG_DWORD.

Registry settings on your server:

You will need to tell your server to NOT grant Opportunistic Locking to your clients.  This is true for older servers as well as new.  Here’s the registry key and parameter setting:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters EnableOplocks = 0

HOWEVER, the latest versions of Windows running SMB2 don’t have this option because SMB2 doesn’t allow you to turn off opportunistic locking.  So, you have to disable SMB2.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters SMB2 = 0

Once SMB2 is disabled, SMB1 will be used again, and the parameter setting described above should be applied to disable oplocks for SMB1.

 

Registry settings on your client machines:

Now, you will need to tell your client machines NOT to cache information about the files you are sharing on the server (i.e. your database).  If Windows caches part of your database, a user could be operating on data that is no longer current.  Here are the registry parameters and settings:

HKEY_LOCAL_MACHINE\system\CurrentControlSet\Services\LanmanWorkstation\Parameters

 

You can also disable Opportunistic Locking on your client machines.  As of this writing, it is not clear whether this is necessary, if you deny it at the server; but here is the registry key and parameter setting to do it:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters EnableOplocks = 0

 

You will need to reboot your machine for the new registry settings to take effect.

 

More background for the curious

The two features in question are “Opportunistic Locking” and “Read Caching.”  Personally, I find these names a little intimidating and their meanings are not necessarily what you might surmise from the names. This is all too typical in the computer world.  At the end of this paper I will list several articles that describe these features and how they affect this problem; but first I’ll take a stab at making a general description. 

Opportunistic Locking

When you are sharing files across your network, and particularly when those files are your Foxpro database files, you would expect that Windows and Foxpro would appropriately manage multiple requests for the same files.  They do, but it looks like Windows is second-guessing your request.  Even though you’ve asked for shared access to your data, Windows is saying to itself, “You know, nobody else is looking.  It’s a lot less trouble to lock the entire file and allow this one user to work with a local copy rather than constantly sending data packets across the network.”  The problem is, with a shared database, someone else is likely to want access—but that first user has a lock on the entire file.  So now, even though that first user requested shared access, Windows has allowed itself to create a mess that it has to untangle.

This is the default behavior for newer versions of Windows!  The reasoning is that for most types of files, this works most of the time.  The overall effect is to reduce network traffic.  In the case of a shared database, however, it doesn’t work at all.  Couple this with the local copy (or “cache”) that the first user is merrily editing—not knowing whether a second user may be editing the same data—and you have the potential for a real mess.

Read Caching

When I see this term, I tend to think about a local copy of data that was requested for read-only purposes.  That would probably work just fine; but that’s not what this term refers to.  It’s short for "Read-Ahead Caching". 

The business of caching data is tricky.  It is used because the local cache provides faster access than if you have to go back to the source of the data every time.  When the data sits on another machine across the network, you can easily imagine that getting your data from a local disk or memory (your cache) would be a lot quicker than making another request from the server in your network. 

One strategy that is typically employed is called “read-ahead.”  The idea is that if you asked for one segment of data in one request, you are likely to ask for the next segment of data in your next request.  So, the thinking goes, “Why not just get them both now?”  So you (or the program you are running) then ask for that next segment, and voila!  It’s already here.

Like Opportunistic Locking, in a lot of cases, this works pretty well.  Even if that next request for data isn’t for the anticipated segment, you can just toss it away and there is nothing lost for the effort.

If, however, the data being requested is from a shared database, then there’s a pretty high likelihood that more than one user will attempt to edit the same segment of data somewhere along the line.  If you are working on a local cache, and Windows has told your program that you have a lock on the file… well, you can see how things might not go the way you expect.

So, the remedy here is to turn off Opportunistic Locking and Read Caching.  In other words, tell Windows to keep looking back the server for each new request for data from the database.  The overall effect will likely be slower access, but you will also greatly reduce the risk of data corruption.

SMB2

SMB stands for “Server Message Block.”  This is the protocol (a standard set of rules) that Windows machines use to transmit requests and responses across your local network.  Apparently, SMB2 is a streamlined version of SMB1.  One aspect of this streamlining is NOT to carry along all the information that SMB1 transmitted about the source of the data requested.  Consequently, it seems that SMB2, while more efficient, is NOT as well equipped to understand whether the local copy of data is—or is not--current.  That’s why this problem got worse with the advent of Windows Vista and Windows Server 2008 and subsequent releases that implement SMB2.

 

Some final commentary

I can’t write this article without venting just a little about this whole situation.  Microsoft acquired an excellent product from Fox Software.  To their credit, they made that product even better over the years.  But Foxpro was designed from the beginning to be a multi-user database product.  It is designed to manage multiple simultaneous requests for data.  I submit that in a personal computing world, there is no reasonable expectation for multi-user access to a database--except in a network environment.

The design decisions that Microsoft has made to improve network performance are understandable, but these decisions were undoubtedly made with the knowledge that they would break Microsoft’s own database products.  As a long-time registered user of Foxpro, I would have expected some notification about these changes and how to deal with them.  There have obviously been knowledge-base articles written concerning these issues, but no proactive notification to known users. 

I have to apologize to our users for not being more on top of this technical issue sooner.  Thinking back on some support calls over the years, I know that this has been an issue for some of you.  But, since the symptoms were varied and intermittent, it has been very difficult to nail down a specific cause.  I hope that this article helps our users to eliminate this problem in their operations.  Please know as well that work is well underway to change the architecture of PubAssist to avoid this problem altogether.

 

Relevant Articles:

The following articles were helpful in understanding and addressing this problem: