Sqlite over a network share

Question

Does anyone have real world experience running a Sqlite database on an SMB share on a LAN (Windows or Linux)?

Its clear from the documentation that this is not really the fastest way to share a Sqlite database.

The obvious caveats are that it may be slow, and Sqlite only supports a single thread writing to the DB at a time. So you become a lot less concurrent cause your DB updates now will block the DB for longer (DB will be locked while data is in transit over the network).

For my application the amount of data that I would like to share is fairly small and writes are not too frequent (a few writes every few seconds at most).

What should I watch out for? Can this work?

I know this is not what Sqlite was designed for, I am less interested in a Postgres/MySql/Sql Server based solution as I am trying to keep my app a light as possible with a minimal amount of dependencies.

Related Links:

From the sqlite mailing list, so I guess one big question is how unreliable are the filelock apis over SMB (windows or linux)

Answer 1

Well I am not great sqlite operation but I believe the Locking of records/tables may not work correctly and may make database corrupt. Because since there is no single server which maintains central locking, two sqlite dll instances on different machines sharing same file over network may not work correctly at all. If database is opened on same machine, sqlite may use file level locking offered by OS to maintain integrity but I doubt if it works correctly over network share.

Answer 2

My experience of file based databases (i.e. those without a database server process), which goes back over twenty years, is that if you try to share them, they will inevitably eventually get corrupted. I'd strongly suggest you look at MySQL again.

And please note, I am not picking on SQLite - I use it myself, just not as a shared database.