On 24 Sep 2019, at 10:14pm, Randall Smith <rsm...@qti.qualcomm.com> wrote:
> I have an application where remote users will be connecting to a SQLite DB > over a network connection that seems to be somewhat sketchy (I can't > characterize it well; I'm hearing this second-hand). > > My question is: Do the commit-or-rollback semantics of SQLite transactions > work if the connection to the file system is weird? For example, do I get a > rollback if the connection is lost mid-transaction or something? Or, is the > underlying assumption with transactions that the connection between SQLite > code and file system is 100% reliable? I can't answer your question as stated, but here are a few points which may get you further to a conclusion. SQLite does not support network connections. It has no networking code. You can't talk to a SQLite database over a network connection. What you can do is write your own frontend and backend software. In which case, the network connection is in your software, not SQLite. SQLite is written to talk to a database stored in local storage to the processor it's running on. In other words, it talks local file system, not network file system. If your SQLite code is running on computer X, then the database should be stored in a filesystem mounted /locally/ on computer X, not across a network. So USB connections are okay, a RAID array mounted over FireWire/Thunderbolt is okay, but anything involving IP addresses is not okay. It might actually work. You might luck into a network file system that correctly supports locking. But the SQLite developers cannot recommend any. This falls under section 2.1 of <https://www.sqlite.org/howtocorrupt.html> which you might find interesting. In terms of transactions and rollback, SQLite is bulletproof in recovery from hardware disconnection (unplugging a local drive) or powerloss of the computer it's running on. When SQLite reopens the database it figures out that a transaction was not completed and rolls it all back [1]. However, as with normal operation described above, this can be relied on only if the connection correctly supports locking. [1] Of course this works only if the database drive has not suffered file system corruption. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
