Before we dig into specifics, it is important to understand that this is a general concept and we can actually distill this to “why can’t client/server or shared database file applications be stored on synced storage (e.g. Google Drive, DropBox, NextCloud, etc.) when access is not controlled to a single user?” QuickBooks uses a shared database file mechanism common to 1980s style applications where a single file or set of files is shared via a file sharing mechanism and individual application copies each access this file to modify it. Google Drive is a synced storage mechanism: meaning it makes copies of data from one location to another. People working on the same file at the same time can, and often do, overwrite each other’s changes and the expectation is that these changes will be manually reconciled later, ignored, or that users will be controlled to keep them from working at the same time.

For some types of multiple user applications, synced storage can be leveraged but only in situations where the application is capable of being locked by the storage system to only allow changes when no others exist. This requires a level of integration not practical with general purpose file syncing. Most systems that do this use a syncing mechanism built into either the database or application layer, not a general purpose one that has to work blindly. In order for data integrity to be maintained with synced storage, it is necessary that only one person edit a file at a time, wait for all potential users to receive updates made after the “save” of that file is made, then a different user can edit that file and repeat the process. But only one user at a time can work on the file and must receive the other user’s updates before opening the file for editing themselves. Or else the system has to ask the users which changes to keep and which to discard in every case.

This integrity process cannot be applied to a database file in any realistic way. The file is designed to be open and accessed all the time, not just to quickly open, edit and save. Saving is also not manual, and not always predictable. We generally assume that saving happens continuously during use, but caching can make even those save operations impossible to manually control. But this is necessary for performance reasons.

Confusion often arises because a single user, without the fear of another user accessing the system at the same time, can use synced storage, like Google Drive or Apple iCloud, to act as a backup mechanism (it simply makes a distant copy automatically) and/or as a means to replicate the file so that the single user can use it first from one location, then from another without needing to manually move the file from location to location. As long as that single user takes enough time moving between locations to ensure that any cache has flushed and that syncs and locks have completed, or ensures that they have not left the first instance of the application open they can reasonably assume a safe system (but cannot completely guarantee it – the mechanism carries minute risk of race conditions even then.) Because there is “a way” to safely use synced storage with the application in a single user mode, many non-technical accounting or financial workers will incorrectly assume that multi-user simultaneous access, which is wholly different, will also work. This, however, is not possible.

What happens is that you have a race condition between multiple users and you can never be entirely sure that it has not happened. Sometimes data will simply be bad and there is no question that a race condition has happened as numbers will be wildly inaccurate. But more often, some transactions will simply be lost even after they have been reviewed.

Let’s give an example. User 1 is at home and enters a new receipt into QuickBooks. This change begins to save to the local computer and after that has completed, it starts to forward the new file to Google Drive in the cloud (online). User 2 is in the office and starts to enter a customer payment on an invoice during this time. User 2’s copy of the QuickBooks datafile is open and cannot be overwritten while in use, so the copy being sent to Google Drive cannot get to User 2. Once User 2 saves his change, his copy also wants to send to Google Drive. Google Drive now has to do something with two documents that started off the same but now have two radically different changes to them, but neither copy has both. It has no possible means of merging them, so it can either accept User 1 as the master and discard the changes from User 2 (e.g. first priority). Or it can accept User 2’s changes and discard User 1’s changes (e.g. latest priority). Or, of course, it can discard all changes and accept none. In no case do all users’ financial transactions get retained even after they have saved them locally. Either User 1 or User 2 (or possibly both) are going to have data that they believed to have been saved suddenly vanish on them. Add in more users, and the problem just gets bigger.

Part of the problem is that when working at a file access level, and syncing and sharing data at a full file level, there’s no way to lock only one record or row, or to keep transactions separate or to merge changes. The file is a single entity and it has changed. It’s all or nothing. The individual QuickBook applications cannot talk to each other directly, nor through the database file, to coordinate writes, saves, reads, etc. to work around this problem. They are blind and cannot know that other applications are trying to work with the file at the same time because each has its own unique copy of the file, there is nothing “shared” between them to allow for communications. The copies are not tied to one another, there’s no quantum state involved here. And then we can add in the potential problems with one or more application instances being used when there is a slow or buggy Internet connection or worse, when an instance is offline. There can be hours or days of changes that have to overwrite, or be overwritten, when synchronization finally happens. We rarely are talking about milliseconds, but often days of data.

How this problem is handled, when it is handled, by locally shared files is multi-faceted. First, there is only one file, not copies of that file. So all changes are available to all copies of the application simultaneously and instantaneously. When one instance of the application is going to write data to the file it uses a locking and alerting mechanism similar to how Clustered File Systems allow SANs to work, where it can signal other application instances that a change is being made and that they have to wait for it to complete, and then refresh their copy of the data before continuing. Only one instance can write and all others have to wait. There are no race conditions because the lock is obtained before beginning, and released when done. And all instances only function as long as the data is currently accessible, if connection is lost then they are unable to proceed. A critical data integrity protection mechanism. Some applications will take this mechanism to an even greater level and add direct (rather than through the shared file) communications channels to make this process more rapid for better performance. But few will go this far as once you go to that level it is generally far easier to simply move to a modern application rather than attempt to shoehorn modern multi-user systems onto decades old designs.

Hopefully this has cleared up way accountants may commonly think that sync’d files will work and why they will often claim that it “worked for me” when they should be saying “I got lucky” or “I used it in a totally different scenario that doesn’t apply to multi-user environment” and why you can absolutely use Google Drive, NextCloud, iCloud, DropBox and more with QuickBooks and other legacy style applications for backups and data transfers but cannot consider attempting to use it as a means of obtaining multi-user access as it simply cannot keep the data intact.