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
