Shared File Access

[Heinz Salomon]

I have programmed a multi-user finance application in VB 6 that has been running fine for the last five years. The app is started from a file service. As I couldn’t use a database, I put my data in a bunch of differently organized files on the fileservice.

One issue I could never solve completely was doing reliable multi-user read/write access on these files. My first approach was letting the clients open the data files using the "LOCK READ WRITE" clause and handling potential "permission denied" errors following the OPEN command by trapping the error and displaying a message "File in use - try again later".

Unfortunately, this approach failed as - from time to time - two users mysteriously got "green light" to access a file at the same time - no error was triggered! - but later in the program - inevitably - some file operations led to obscure untrappable errors.

So, lastly, I designed a PB/CC app that acted as a "server". Clients had to "ask" the server for exclusive access by placing a file in a certain folder on the fileservice that was monitored by the "server" app which placed an "access ticket" with the same name, one after the other, in another folder that was in turn monitored by the waiting clients (monitoring here means regular polling).

This strategy has been working fine for five years with only a few malfunctions. My customers were satisfied and are satisfied.

Nevertheless, this solution is far from being perfect, in fact, I regard it as bloated, clumsy, far from being efficient – its only asset is that it has been working nearly flawless for years.

If I will ever have to do some change on this app I also want to solve that shared access issue in a perfect and professional manner. Unfortunately, introducing a database app which would do the low level work - safely locking and unlocking shared data - is not an option for various reasons. I must manage without additional resources in this case – that means, I have to resort to a pure PowerBasic (PB/WIN or PB/CC) solution.

Could anyone please point me in the right direction?

Heinz Salomon

[Erich Schulman]

You can't use SQLitening?

If not, then see http://www.codeproject.com/KB/threads/opbmutex.aspx

[Heinz Salomon]

SQLitening seems to be a very attractive free software product but replacing my data files with a database would mean I'd have to apply greater changes to my app. I simply can't justify that as everything runs fine.

I have downloaded the opbmutex project and have gone through the source code. I compiled the source code to a dll but could not find out how to use/call the dll. I have to admit that the complexity of the subject (c language, mutex, critical sections) exceeds my programming skills. Maybe sometime in the future I will return to this project and try to understand it...

Thank you very much for your suggestions!

Heinz Salomon

PS: I know this thread should have been opened in the programming section. But once I had created it I could not find out how to move it there.

[Fred Harris]

Hi Heinz!

A year or two ago I faced a problem that I think might be somewhat similiar to yours. The context for the problem was that I had re-written a DOS data collector program to Windows CE. Windows CE is a multi-tasking platform just like desktop Windows and unlike DOS. Anyway, when I wrote the Windows CE program I used a random access binary file setup where the user would open the data file through an open file dialog box. The only thing is I performed all my file openings/closings - reads/writes within keypress event procedures; in other words, the file being used wasn't maintained in an open state from the time the user started entering data. It didn't occur to me immediately that this situation would allow a demented user (aren't they all mostly demented?) to minimize the app, then open another instance of the app and use the open file dialog to open that very same data file!

I had no intention of creating a multi-user database for a handheld data collector program!!!! The solution I found was quite easy to implement and it was through help from someone here that I solved it (Peter Lameijn). The solution was to create a mutex around the file name. It only takes a few lines of code to do it. When someone attempts to open a file one just checks to see if a mutex is associated with it. If so, you give the user a nasty message. If you are interested I'll dig up the code.

[Heinz Salomon]

Hi Fred!

I'd appreciate any hint - and of course, I am interested in a tested-and-tried solution too
Of course, in my case, the application is running on different machines at the same time - should your lock work anyway?

Thank you for taking the time!

Heinz Salomon

[Brian Chirgwin]

My experience file share locking is buggy; there are too many pieces involved including OSes client/server, patches, network delays, etc...
The following is from SQLite documentation. I have read this statement elsewhere and experienced failures, I assume to the following, myself.

From SQLite documentation
(5) Can multiple applications or multiple instances of the same application access a single database file at the same time?

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

SQLite uses reader/writer locks to control access to the database. (Under Win95/98/ME which lacks support for reader/writer locks, a probabilistic simulation is used instead.) But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time. On Windows, Microsoft's documentation says that locking may not work under FAT filesystems if you are not running the Share.exe daemon. People who have a lot of experience with Windows tell me that file locking of network files is very buggy and is not dependable. If what they say is true, sharing an SQLite database between two or more Windows machines might cause unexpected problems.

We are aware of no other embedded SQL database engine that supports as much concurrency as SQLite. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business. Other embedded SQL database engines typically only allow a single process to connect to the database at once.

However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine.

When SQLite tries to access a file that is locked by another process, the default behavior is to return SQLITE_BUSY. You can adjust this behavior from C code using the sqlite3_busy_handler() or sqlite3_busy_timeout() API functions.


Solution?

I think you are on the right track using your server application monitor. It has worked for 5 years and has been quite stable. I'd make some changes to the server:

1. Instead of using files and monitoring, use TCP/IP protocol. This would get rid of the file creation/deletion in a special folder and greatly increase the current server monitor. This requires minimal changes. You can make the application operate in either mode current File flags and TCP/IP. Instead of files, just keep the flags in memory in the server application.

2. Change the current monitor a step farther. Using TCP/IP have the server process the file request on the clients behalf and return the results via TCP/IP. By doing this the locking issue is completely removed. The server would be the only application to need/have access to the files. The server could even keep the files open over multiple requests. Optimizations could be made for specific data as well. A side benefit is the possibly using the server would allow the application to be used remotely over an Internet connection.

Of course, this is sort of reinventing the wheel. You would basically creating your own proprietary database server; but you already have done that with your current server monitor.

I have programmed a multi-user finance application in VB 6 that has been running fine for the last five years. The app is started from a file service. As I couldn’t use a database, I put my data in a bunch of differently organized files on the fileservice.

One issue I could never solve completely was doing reliable multi-user read/write access on these files. My first approach was letting the clients open the data files using the "LOCK READ WRITE" clause and handling potential "permission denied" errors following the OPEN command by trapping the error and displaying a message "File in use - try again later".

Unfortunately, this approach failed as - from time to time - two users mysteriously got "green light" to access a file at the same time - no error was triggered! - but later in the program - inevitably - some file operations led to obscure untrappable errors.

So, lastly, I designed a PB/CC app that acted as a "server". Clients had to "ask" the server for exclusive access by placing a file in a certain folder on the fileservice that was monitored by the "server" app which placed an "access ticket" with the same name, one after the other, in another folder that was in turn monitored by the waiting clients (monitoring here means regular polling).

This strategy has been working fine for five years with only a few malfunctions. My customers were satisfied and are satisfied.

Nevertheless, this solution is far from being perfect, in fact, I regard it as bloated, clumsy, far from being efficient – its only asset is that it has been working nearly flawless for years.

If I will ever have to do some change on this app I also want to solve that shared access issue in a perfect and professional manner. Unfortunately, introducing a database app which would do the low level work - safely locking and unlocking shared data - is not an option for various reasons. I must manage without additional resources in this case – that means, I have to resort to a pure PowerBasic (PB/WIN or PB/CC) solution.

Could anyone please point me in the right direction?

Heinz Salomon

[Michael Mattias]

Here's something at the right price (free) regarding multi-user file access:

"Fundamentals Of Multi-User Programming." Article published in December 1995 issue of "BASICally Speaking" magazine discussing the principles of writing multi-user programs; code samples in BASIC. Rich Text format; placed in the Public Domain June 2005

http://www.talsystems.com/tsihome_html/downloads/multiuser_programming.rtf

Maybe it will give you some ideas.

MCM

[Heinz Salomon]

Thanks @ all for your responses!

Heinz Salomon