On 10 Nov 2016, at 10:03am, Dan Kennedy <danielk1...@gmail.com> wrote:
> It seems to be that if your drive has write-caching enabled, you need > barrier=1 to avoid risking corruption. Or you can get away with barrier=0 if > write-caching is disabled. To supplement Dan's answer, having write-caching enabled is always a problem with ACID storage, unless it's defeated by other settings. Because the thing which looks at the cache and writes it to disk is not limited to writing writes in order. So you cannot guarantee that your changes are written to disk in the order they were made. Illustration: Suppose your program modifies the contents of these sectors in order: A B C D B E When it gets to the second 'B' the mechanism which caches writes notices that it already has data for B and just replaces that data in the cache. The first write to B never happens. Instead, even if it happens to write all the other sectors in order it actually writes one of these two sequences, depending on how it's programmed: A B C D E A C D B E In both sequences the first the change to sector C gets written before the first change to sector B. And power could fail, or the disk subsystem could glitch, between them. If SQLite recovers a file after a crash it goes some way towards detecting this but it's impossible to completely compensate for it. You can ask why write-caching does this thing. Well, it has two advantages. One is that it uses less memory (store just one copy of sector B in memory even if it's part of a frequently-changed log file). The other is that it saves time (write all the changes to sector B in one operation). And those two things are the main reasons people would turn write-caching on in the first place. Some storage drivers have an EIOW (Enforce In Order Writes) directive/switch/patch. Or an equivalent, like the 'barrier=1' switch when you mount the drive, that Dan mentioned, but one of the things that does is ensure that write-caching is not fully used. > the possibility of losing data due to power loss A useful way to proceed if you care about that, is to look at each setting which looks like it speeds things up and ask yourself "If it was okay to have this on all the time, why would they have provided the other option ?". It's not nice, but it does help you consider the aspects. And it can slow down operation considerably. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
