Replacing SQLite database while accessing it

Question

I am completely new to SQLite and I intend to use it in a M2M / client-server environment where a database is generated on the server, sent to the client as a file and used on the client for data lookup.

The question is: can I replace the whole database file while the client is using it at the same time?

The question may sound silly but the client is a Linux thin client and to replace the database file a temporary file would be renamed to the final file name. In Linux, a program which has still open the older version of the file will still access the older data since the old file is preserved by the OS until all file handles have been closed. Only new open()s will access the new version of the file.

So, in short:

• client randomly accesses the SQLite database
• a new version of the database is received from the server and written to a temporary file
• the temporary file is renamed to the SQLite database file

I know it is a very specific question, but maybe someone can tell me if this would be a problem for SQLite or if there are similar methods to replace a database while the client is running. I do not want to send a bunch of SQL statements from the server to the client to update the database.

Biogeography

Any words of wisdom after another 3 years? I'm also specifically interested in the case where the client has read-only access. – Attend

I monitor the database file and when I notice an inode change, I close and reopen the database. Works fine. Please note that the database is not being heavily (a few queries here and there based on user interaction). – Biogeography

Answer 1

No, you cannot just replace an open SQLite3 DB file. SQLite will keep using the same file descriptor (or handle in Windows-speak), unless you close and re-open your database. More specifically:

• Deleting and replacing an open file is either useless (Linux) or impossible (Windows). SQLite will never get to see the contents of the new file at all.

• Overwriting an SQLite3 DB file is a recipe for data corruption. From the SQLite3 documentation:

  Likewise, if a rogue process opens a database file or journal and writes malformed data into the middle of it, then the database will become corrupt.

  Arbitrarily overwriting the contents of the DB file can cause a whole pile of issues:

  • If you are very lucky it will just cause DB errors, forcing you to reopen the database anyway.
  • Depending on how you use the data, your application might just crash and burn.
  • Your application may try to apply an existing journal on the new file. Sounds painful? It is!
  • If you are really unlucky, the user will just get back invalid results from any queries.

The best way to deal with this would be a proper client-server implementation where the client DB file is updated from data coming from the server. In the long run that would allow for far more flexibility, while also reducing the bandwidth requirements by sending updates, rather than the whole file.

If that is not possible, you should update the client DB file in three discrete steps:

• Send a message to the client application to close the DB. This allows the application to commit any changes, remove any journal files and clean-up its internal state.
• Replace/Overwrite the file.
• Send a message to the client application to re-open the DB. You would have to setup all prepared statements again, though.

If you do not want to close the DB file for some reason, then you should have your application - or even a separate process - update the original DB file using the new file as input. The SQLite3 backup API might be of interest to you in that case.

Derma

Thanks for your response. You say it's useless under Linux. So when I replace the database file while the client is still accessing it does not harm (ie. the client uses just a single file descriptor for a database file). My client does not write to the database (it's a daily updated lookup data table) and frequently restarts itself or reopens the database. It is not a problem when the client still uses old data until he reopens the database as long as the database is still usable. From what I understand this should work. Do you agree? – Biogeography

@Udo G.: If your client does no updates there should be no danger of journal mix-ups. I tried deleting a DB file and my reader process continued with no issues. SQLite appears to use a single file descriptor - I am not sure that it always does so. That said, we are talking about implementation/OS-specific behaviour that is not by any means documented and that may change in the future. Do you really want to base your code on a pile of assumptions like this? – Derma

interestingly, this is wrong. if you delete the file while accessing it using the sqlite3 client, you get an i/o error. try it. – Fugazy