On Sun, Feb 13, 2011 at 8:53 PM, Jim Wilcoxson <pri...@gmail.com> wrote:
> > But I thought about how it would be possible to test this explanation . > I'm > > going to do some tests that works like this. The same id updating, but in > > the middle of 10,000 operation I will unplug the cord, the sqlite will > say > > that it can't commit particular insert and I can see what is the value of > > this insert. After replugging two variants possible: > > - I will find the previous value in the base. If for several dozens tests > > the result is the same, that would mean that the situation is still > needed > > to be explained. > > - I will find some value less than previous to the one sqlite could not > > commit at least in some of the tests (maybe -3, -4 to the failed one). In > > this case the explanation will be confirmed. > > > > How about this? Does it sound reasonable? > > > > > Sounds like a clever test to me! > > > Thanks for supporting :) now the results. I switched off the journal: PRAGMA journal_mode=OFF; As I described, the only record contained id=1 and the sql query was UPDATE TestTable SET Id=Id + 1 The cord was unplugged in the middle of the 10,000 operations when about 5000 records were updated. The hd was bus-powered external hard drive and I repeated the test several times. No test showed expected value that confirms the Jim's explanation about postponed writing. The actual values are below (the first one is the expected value to be found after reopening, the next one is actual value and the difference is self-explainable) 5094 -> 5086 = -8 5084 -> 5083 = -1 5070 -> 5049 = -21 5082 -> 5069 = -13 5095 -> 5086 = -9 5072 -> 5033 = -39 5118 -> 5053 = -65 5081 -> 5075 = -6 So the maximum of non-flushed commits was 65 I also made a couple of tests with journal on and see what is the difference between expected, non-restored value, and restored value. One of the results: 5078 (expeced) -> 5077 (non restored) -> 5021 (restored) It seems that for correctly operating os/hardware the numbers should be 5078->5077->5077 or no journal present depending on the moment of unplugging. So this postponing also made possible existing of some prior version of journal file. So, the next question was 'where?'. Is this software or hardware to blame. Richard always mentioned hardware in this context, I decided to check this with another device. This time it was a harddisk box with separated bus and power (the hd was Maxtor IDE 80Gb drive). The first variation was similar to the one with bus-powered, this time I used hard button on the box that is equivalent to unplugging both connection and power. The difference for a single test was really impressive 5355 -> 4445 = -910. And when I calculated numbers for non-interrupted test the drive showed about 300 commits per second. The second variation was just unplugging the cord but keeping the power intact, so if it's drive that caches, it would end its operations completely. This time the results were perfect, for example 4822 -> 4822, and even 5371 -> 5372 = +1 that actually would mean the process was interrupted after all data is written but before sqlite made winsync, os reported failure, but the data was already there. So the sad news about faulty hardware is probably true once again. Max Vlasov _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
