Use of sqlite on network share

Question

We are using SQLite (Xerial JDBC driver) on a windows desktop based Java application. Now we are moving on to a client-server version of the same application where multiple Java based Swing clients will be connecting to the same SQLite db file on the designated server Windows PC. Please correct me if I'm wrong:

1. Is keeping the SQLite database file over network share the only option to use SQLite in this mode? or is there some other solution that I am missing ?
2. Will using SQLite increase the chances of DB corruption ?

I don't see lot of concurrent update operations. There will be 5-10 clients trying to read & update the same DB. In that case, is it better to use an entperise grade DB (MySQL, Postgres)?

Answer 1

From the FAQ paragraph before the one quoted:

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.

I would not network share a SQLite database file as it appears you will be buying yourself nasty synchronization problems yielding hard to reproduce data corruption.

Put another way, you are using a general file sharing mechanism to substitute for the server capabilities of another DBMS. These other DBMS are tested specifically and field-hardened for multiple client access, though SQLite has great merits, this isn't one of them.

Answer 2

This is a FAQ:

[...] 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. [...]

Also read SQLite is serverless.

Whether SQLite is sufficient for your needs is impossible to tell. If you have long-running update transactions, locking the whole database might be a serious issue. Since you're using JDBC to access it, there shouldn't be many problems switching to another database engine if necessary.