[EMAIL PROTECTED] wrote: > Markus Kolb <[EMAIL PROTECTED]> wrote: >> Hello, >> >> I have some questions to autocommit mode of SQLite 3.3.4 and its >> transactions. >> >> Did I understand it right that new or changed row data is only written >> to disk when the db connection is closed with sqlite3_close? > > No. Data is guaranteed to be written to disk when you COMMIT, > and a COMMIT happens automatically after every statement in > autocommit mode. > > SQLite transactions are (among other things) Durable. That means > that all the data is safely on the disk surface before COMMIT > returns (autocommit or explicit commit). You can take an power > failure or OS crash at any point after a COMMIT and the data will > survive (assuming your disk drive doesn't get wasted - nothing > really we can do about that.) > > Where did you get the idea that data is only written to disk > on close? Is there some point in the documentation that needs > to be clarified?
I got the idea from this page: http://www.sqlite.org/lockingv3.html 7.0: "By default, SQLite version 3 operates in autocommit mode. In autocommit mode, all changes to the database are committed as soon as all operations associated with the current database connection complete." "The SQL command "COMMIT" does not actually commit the changes to disk. It just turns autocommit back on. Then, at the conclusion of the command, the regular autocommit logic takes over and causes the actual commit to disk to occur." And I got the idea because my INSERTS with sqlite3_exec are not written to disk but the memory usage of my program grows with each INSERT. I am using autocommit and I call sqlite3_exec with a SQL-INSERT inside a sqlite_callback()-function which gets called for each row of a SQL-SELECT (sqlite3_exec, too). It is working but the inserts seem to stay in memory till the connection is closed and with more inserts each insert needs more time. I call my program which does many row inserts. I use the sqlite3 tool to have a look at the DB while my program is running. I do a "SELECT * FROM table;" and before my program has finished there is no new row result. Starting with an empty table it is empty until my prog has finished. If I kill my program after e.g. 5 minutes (in this time it has done many many INSERTS) there is no new data in the DB file afterwards. So again my question what can I do to get the data written to DB file or why there is this behavior? Thanks Markus
