On Sun, 13 Jul 2014 18:00:59 +0100, Simon Slavin <slav...@bigfraud.org> wrote:
> On 12 Jul 2014, at 9:37am, Charles Parnot <charles.par...@gmail.com> wrote: > > - the journal file is actually not “hot” and I misunderstood > the conditions that make it hot > > That one. The files on disk aren't 'hot' (as I think you mean > it) while you're in a transaction. > > Your file system is not pushing journal changes at the file > level. It doesn't need to do that while a transaction is open. > since while the transaction is open, the database is locked so > nothing else can use it anyway, and if your app crashes the > whole transaction will be ignored. > > SQLite could be written to push transactions to the journal file > on each change, but that would involve lots of writing to disk, > so it would make SQLite slower, and for no gain. > > > [snip] The test case I am generating is just for a simple edge > case of our Dropbox-based syncing > > > Yes, DropBox can be a problem for open SQLite databases. As a > file level duplication system which does not understand locks, > there's no good way to make DropBox work with open SQLite > databases, or as a mediator for concurrent multi-user changes to > a database. > I had to explain to some users that a database > change is not 'safe' until the database is closed. As far as I know, a database change is safe after a successfull COMMIT. Commit also releases locks. > One thing that's worth testing is to make sure that recovery > after crashes always yields a database with either pre- or post- > transaction data rather than something corrupt which can't be > opened. I don't know much about how DropBox works. Could it > perhaps end up with a database file from one computer but > journal file from another ? Indeed, or a journal file and a database file of different points in time. One could expect dropbox to respect locks, but it doesn't seem to do that. It also soesn't seem to synchronize a directory in an atomic fashion, which would be necessary to maintain consistency for sqlite or any other software that works on time-coordinated sets of files. In my opinion dropbox should not be used on directories with SQLite databases at all. It would be better to only allow dropbox access to directories with backups, and an application level synchronisation/recovery mechanism to reconstruct the main database from the backup when needed. -- Regards, Kees Nuyt _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
