Note: attachments are stripped out of this list. So if you want for anybody else to see your zip file you need to put it on some website and post link here.
About the problem you have: I wonder how are you sure that there should be 50 rows in the database and not 49? If you are resetting the OS before it has a chance to properly commit everything then it's okay for last transaction to be missing. But if you are sure that you are resetting the OS after everything is settled then maybe you are not committing your last transaction properly? Or maybe there's some bug in your virtualization layer and you need to try the same thing on a real hardware? Pavel On Sun, Sep 9, 2012 at 8:50 AM, Daniel Frimerman <danielfrimer...@gmail.com> wrote: > I am fairly new to sqlite and as a result of not reading the manual and not > doing some performance testing, I got punished somewhat. I did not > anticipate that on journal_mode=DELETE and synchronous=FULL, I would get no > more than 5 inserts (in auto-commit mode) per second. It crippled a > certain batch operation on a live system. That's water under the bridge; > it's the testing afterwards and a potential minor problem that I found is > what I am now interested in. > > I tested all journal mode settings for sqlite, as well as the synchronous > setting. Some things that I discovered were not so obvious from reading > the docs, such as the WAL journal mode combined with NORMAL synchronous > setting, which is nowhere near as "durable" as NORMAL setting for other > journal modes. I.e. NORMAL mode for DELETE journal in 99% of cases saves > all inserted data - reproducing the slightest of chances that consistency > is compromised was rather hard. This is reflected in performance testing: > NORMAL is only slightly faster than FULL mode for non-WAL journal settings > (btw, journal_mode=OFF was never tested in any of my tests). But, I > understood, that in WAL+NORMAL mode is equivalent to FULL+non-WAL mode > where consistency/corruption is concerned. That is, the database cannot > get corrupted in WAL+NORMAL. The gain in speed for WAL+NORMAL trades off > durability and in my tests I easily reproduced that. > > Okay, that was not really related to the possible bug I found. I've > attached a ZIP file containing some batch files that create a table, insert > some rows, at which point you hard-reset the OS, log back in and check if > the number of rows in the DB matches what you inserted. Although the > non-WAL journal modes are somewhat similar, the little problem that I've > come to find only happens on DELETE/TRUNCATE, but not on PERSIST or WAL. > The problem is basically as follows: in DELETE and TRUNCATE journal mode > combined with NORMAL/FULL synchronous mode, there is always 1 row missing > during my simulated power-cut. > > I used VirtualBox 4.1.22 and Windows XP Pro (SP3) and sqlite3 3.7.14 > (command line as well as through my testing application). In VirtualBox, > under storage settings for the VM, I used IDE Controller (afaik it's single > threaded), turned off host I/O cache. Inside the guest, write-cache should > be enabled in device manager under policies for the default disk controller. > > To test this, set your VM as above, copy the files from the attached ZIP > file, also download the latest sqlite3 command line shell. Restart the > guest once to ensure your files are flushed out before you start resetting > the guest :) > Execute the following batch file: EXEC_DATA__DELETE_FULL.cmd, wait 2-3 > seconds (or less) then hit HOST+R to hard reset the OS. When you reboot, > run READ_DATA.cmd, you'll see 49 rows, but there should be 50. > You can try the same with EXEC_DATA__DELETE_NORMAL.cmd, > EXEC_DATA__TRUNCATE_FULL.cmd, EXEC_DATA__TRUNCATE_NORMAL.cmd > 50 rows if you try with EXEC_DATA__PERSIST_FULL.cmd and > EXEC_DATA__PERSIST_NORMAL.cmd and EXEC_DATA__WAL_FULL.cmd > > What's with that? > > Kind regards, Dan > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
