3 ms·
> NFS and SMB are options that can convey access to remote systems, but performance will not likely be good. And the safety of your data depends on the quality
by teraflop 5y ago
> NFS and SMB are options that can convey access to remote systems, but performance will not likely be good.
And the safety of your data depends on the quality of your network filesystem's locking implementation. It's not too difficult to design a locking method that works most of the time, but it's a lot harder to build something that guarantees mutual exclusion over an imperfect network.
On a single machine, file locking is generally reliable (mediated by the kernel). Multiple clients can access the same database simultaneously, with readers and writers taking turns, and all that happens is you pay the performance penalty of serialized access.
But if your client thinks it has a lock on a remotely-stored database file, but the server-side lock has actually expired and another client is writing to the database, your data is likely to get hosed.
https://www.sqlite.org/howtocorrupt.html https://www.sqlite.org/howtocorrupt.html
- chasil 5y agoDatabases on remote filesystems should be limited to SQLITE3_OPEN_READONLY access, agreed.
- ridiculous_fish 5y agoI'm experimenting with using SQLite to store users' history in fish shell, but the remote filesystems problem seems likely to be a showstopper. What can be done about it?
- chasil 5y agoI'm just reading this, and learning a few new things: https://www.sqlite.org/howtocorrupt.html https://www.sqlite.org/howtocorrupt.html SQLite has an alternate lock mode with dotfiles that seems to prevent database corruption over NFS. It is important that all SQLite accesses from all connected processes use the same lock mode. "2.3. Two processes using different locking protocols "The default locking mechanism used by SQLite on unix platforms is POSIX advisory locking, but there are other options. By selecting an alternative sqlite3_vfs using the sqlite3_open_v2() interface, an application can make use of other locking protocols that might be more appropriate to certain filesystems. For example, dot-file locking might be select for use in an application that has to run on an NFS filesystem that does not support POSIX advisory locking. "It is important that all connections to the same database file use the same locking protocol. If one application is using POSIX advisory locks and another application is using dot-file locking, then the two applications will not see each other's locks and will not be able to coordinate database access, possibly leading to database corruption."
- ridiculous_fish 5y agoThe problems with dotfile locking are: 1. A crash risks leaving the lock file in place; it must be manually removed or else you hang forever. 2. The same home directory may be local and remote on different machines, meaning different locking protocols. I am considering a mode where the database is loaded fully into memory, and writes are atomically saved via the classic adjacent-file-renamed mechanism. You risk losing data due to races, but it can't corrupt the database.
- stjohnswarts 5y agotalk to a server on the remote "storage" where the history is rather than directly to the sqlite server? Queue up changes and make them serial in the "service"