4 ms·

The whole "Writes are atomic" still breaks down if they assume that disks are local. Having worked with one of these zip document format for 20 years now, I hav

The whole "Writes are atomic" still breaks down if they assume that disks are local. Having worked with one of these zip document format for 20 years now, I have found that local disk is the edge case, and SMB is the norm in corporate environments. Sure you can download the sqlite file at the start of a session and work on it locally in a temp directory with atomic writes - but in the end the user doesn't think they have "saved" until version N+1 of the file is actually on their network share.

galgalesh 6y ago
SQLite tries to detect when the database is on a network share and changes it's behavior accordingly.
- alkonaut 6y ago
  This doesn't really work as well as one would like. It's not really related to sqlite it's just that expectations of file system behavior (locking, operations having completed when the system says they have, etc) are just flimsy fo many network file system implementations. All SQLite can do when it can't map the file as it would normally is basically upload the new version of the file, and not write it in place anyway. That behavior is just as easy to create by the application developer.
- niea_11 6y ago
  The sqlite documentation advises against using it in that scenario: If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it. A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network. text from here : https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
wmertens 6y ago
Isn't that the same problem with zip files? So if we're assuming that the compressed SQLite format will take up the same or less space as the zip file, wouldn't that mean that exactly the same mitigations work?
- alkonaut 6y ago
  Yes the problem is ”fundamental” here as there is no filesystem guarantees. You basically upload the whole file after modifying it just like you would a zip. My point was that this is just as good to leave to the application and gain some better control over failure behavior/recovery.

galgalesh 6y ago

SQLite tries to detect when the database is on a network share and changes it's behavior accordingly.

alkonaut 6y ago
This doesn't really work as well as one would like. It's not really related to sqlite it's just that expectations of file system behavior (locking, operations having completed when the system says they have, etc) are just flimsy fo many network file system implementations. All SQLite can do when it can't map the file as it would normally is basically upload the new version of the file, and not write it in place anyway. That behavior is just as easy to create by the application developer.
niea_11 6y ago
The sqlite documentation advises against using it in that scenario: If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it. A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network. text from here : https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html

wmertens 6y ago

Isn't that the same problem with zip files? So if we're assuming that the compressed SQLite format will take up the same or less space as the zip file, wouldn't that mean that exactly the same mitigations work?

alkonaut 6y ago
Yes the problem is ”fundamental” here as there is no filesystem guarantees. You basically upload the whole file after modifying it just like you would a zip. My point was that this is just as good to leave to the application and gain some better control over failure behavior/recovery.