Database file locking


I have a REALSQLDatabase file that should get backed up every five minutes. This is done by using a timer object that is creating a copy of the file to a temporary folder.

But… I found no information about what happens if the user writes a record into the database file exactly at the same time the backup is created… would this lead to a destroyed backup, an exception error (which ?) or can I rely upon the operating system (OS X, Windows) that the file is blocked until write access has been completed.


I Think you should be on the safe side with your backup strategy if your app is not using threads and your database is used by single user on local computer.

(And If you have already switched to Xojo, REALSQLDatabase has been replaced by SQLiteDatabase where there is a new interface for backing up the database.)

Well the backup routine is planned as a timer/thread combination to make creating large backups invisible to the user.

Yes, it’s a single user database.

I’m aware that the risk is quite low that both actions are processed at the same time… but data is precious, especially if it gets lost. :wink:

I already thought about using an app-wide flag (boolean) that is checked before writing something to the database. But this would be difficult to realize (vast parts of the db functions have been already completed).

What do you mean with Xojo is providing a new interface for backing up the database ? I’m on RS not switching to Xojo till it runs stable (in about one year or so ;-). Isn’t the “new” DB in Xojo just the renamed REALSQLDatabase aka SQLite ?

Backing up every 5 minutes seems a bit excessive. At such a short interval you might start thinking about just writing the data out twice to begin with.

Yes, I also thought about doing so… currently evaluating the best method… :slight_smile: I think that this would work best. Not sure, will need some testing.

I’d recommend a log file approach. Store the raw SQL commands themselves in a different database. If the first one is ever lost you can rebuild it in essence. Then do actual backups with less frequency.

The downside to your current approach is you are duplicating data a lot.

An interesting solution… I put it on my list. :slight_smile:

Duplicating data is not an issue, the database is relatively small, about 1 MB in size.

But perhaps someone is able to answer my original question: What happens if the database file is accessed (writing a record to it) while FolderItem.CopyFileTo is used ? The current DB is just about 1 MB in size, but let’s consider the DB is 50 MB and saving may take a few seconds (for whatever reason).

The SQLite API handles it without locking up for reads. It even works incrementally which the Xojo class supports (by virtue of pausing every so often).

Thank you. :slight_smile:

Most likely you end up with corrupted backup document. Use CriticalSection or Semaphore to delay backup thread until all writes have completed and transactions are commited. Personally, I’ve had problems with CriticalSection so in my apps I use Semaphore.