On 14 Feb 2011, at 3:56pm, Pavel Ivanov wrote: >> So my question is, does it maintain the other 3 parts of ACID, so that >> the database will never be in a corrupted state after a power loss, >> even though it may be missing some writes that were confirmed? > > Jim, I think the answer to your question is already in Max's tests: > the USB drive is completely unreliable and can easily lead to database > corruption. I'll explain. Max's tests showed that there were > situations when database and journal were different not by one > transaction but by several ones. So if one makes changes to several > database pages (located in different disk sectors) and/or makes > transactions touching several database pages (which makes multi-page > journal files) then these pages can be way out of sync with each other > (including pages inside journal). And this will easily lead to > database corruption.
It's worth noting that SQLite's journaling mechanism is designed to make up for some ACID frailties of the storage medium. Even if your drive doesn't support ACID correctly, SQLite (seen as a whole and used as intended) may still be able to manage ACID on it. You can see a discussion of this in section 2 of http://www.sqlite.org/atomiccommit.html Technology has now moved on from when that page was written. For example, it's no longer possible for a drive to write part of a sector: the entire sector would be marked as corrupt now. However, it might be worth changing the page (or splitting section 2 off to another page) to list precisely the requirements of the storage medium SQLite needs to accomplish this. For instance, it may be that if the drive doesn't support write-in-order then even SQLite can't guarantee ACID. I don't see a proper cure for this: software has to trust hardware to some extent, but unlike the old days hardware is now a set of independent components, each of which can lie to the others. For SQLite to support ACID it may be that you need to set up your computer as a server, using drives bought as server drives which are set up with write-through-caching and other slower but safer settings. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
