On 14 Feb 2010, at 6:49am, Michael Tharp wrote: > On 02/14/2010 01:09 AM, Simon Slavin wrote: >> read the distinctions between the different types of 'BEGIN'. > > I don't see any mention of flushing behaviour, just earlier locks. I > guess what I'm asking for is a lightweight two-phase commit, which seems > easy enough to do given the current implementation.
Hmm. I believe that 'a lightweight two-phase commit' is a good description of how SQLite works anyway, without you taking any special measures. SQLite does maintain is own journaling system. >> But second, you should know that even the default behaviour of SQLite >> is extremely quick at this. Even just ignoring transactions and >> issuing a bare 'INSERT' will be extremely fast from begin to end. In >> fact the extra time it takes to issue the BEGIN and COMMIT commands >> may actually increase the time between updating your journal and >> SQLite committing the change. Run some time tests yourself. > This is a long-running transaction with hundreds to tens of thousands of > modifications made. The problem is that this exists only in the buffers > and when commit time rolls around it takes long enough to flush them to > disk -- 0.1s to 5s or more as the OS is also flushing lots of non-sqlite > pages -- that the user has an opportunity to get bored and interrupt > with a well-placed Ctrl-C. Python defers the signal until the commit > completes by which time it is too late to roll back the filesystem > journal. The bug resulting from this condition has been hit several > times in real-world situations and is easily reproduced. If the flushing > could be done in a separate step then the commit process is reduced to > two sequential unlink() syscalls for the FS journal and for sqlite which > would make the risk window acceptably small. Well, the COMMIT command does do the flushing. But it does seem that you need advice from someone who knows more about SQLite than I do. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
