My apologies about the attachment; should have known better. This should be better: https://dl.dropbox.com/u/50838941/SQLite3_Test.zip
I only get the problem with DELETE and TRUNCATE journal_mode (synchronous set to NORMAL or FULL), but not with PERSIST (synchronous set to NORMAL or FULL) or WAL (synchronous set to FULL). The reason I think there has to be 50 rows is because on FULL mode for example, the I/O buffers are flushed, and it's consistently missing 1 record as opposed to any other number of records. I insert 50 records, the sqlite3 command line utility executes the script, and I get to a stage where I can write commands to the console. What I mean is that as far as sqlite is concerned, it has written the data to disk and also instructed the OS to flush the buffers. Perhaps it finalises something from the last insert only when the next insert comes in? It could be a coincidence of some sort, by sheer difference of implementation of different journals that the "problem" doesn't show itself with PERSIST or WAL journals. I turned off host I/O cache in VirtualBox, so any writes by the guest OS have to be physically written to the virtual disk on the host. The guest has "standard" I/O caching on disk, but FlushFileBuffers() should have done its job. There is no reason why any link in the chain should report data written to disk without actually doing it, unless there is a problem. I suppose I should just go ahead and test it on the physical PC. If there is a problem with that also, then I suppose I could blame the OS for not flushing stuff to disk properly. Now I gotta find me a machine............ Regards, Dan On Sun, Sep 9, 2012 at 8:14 PM, Pavel Ivanov <paiva...@gmail.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
