Siebe Warners <[EMAIL PROTECTED]> wrote: > Hi all, > > I'm using sqlite on an embedded system with flash EEPROM as the medium. > Performance of the flash is not too good, so i'm trying to avoid file > system access where possible without running risk of database corruption. > So the database file and the journal files need to be written to the > flash, but i also noticed temp files being written to disk. I'm using > sqlite 3.2.7. > > When i perform a sequence of commands like: > BEGIN; > UPDATE t SET col="val1" WHERE key="key1"; > UPDATE t SET col="val2" WHERE key="key2"; > UPDATE t SET col="val3" WHERE key="key3"; > ..... > UPDATE t SET col="valx" WHERE key="keyx"; > COMMIT; > > Using strace i observe: > - the journal file is created at the start of the transaction > - a temp file is created at the start of the transaction > - the journal file is written at the start of the sequence and some more > data is appended somewhere halfway > - the temp file is written at every UPDATE > - at the commit the journal file is written, the database file is > updated, and journal file and temp file are removed. >
You must have a UNIQUE or CHECK constraint on your "t" table. The extra file being opened is a rollback journal for each particular UPDATE statement. This extra rollback journal is needed in case you hit a UNIQUE or CHECK constraint half way through the update and have to go back and undo those parts of the UPDATE that are already done. You can avoid this extra journal file by using UPDATE OR FAIL instead of UPDATE. -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
