On Thu, Dec 16, 2010 at 09:41:27AM +0200, Yoni Londner wrote: > > > The underlying error here is that you are attempting to use threads in the > > first place. You should never do that. Threads are evil and should be > > avoided wherever possible. Use separate processes for concurrency. Threads > > in application programs always result in subtle bugs (such as this one) that > > are hard to reproduce and waste countless hours of developer time. Just say > > "no" to threads. > Could not agree with you more. Threads are evil. This is exactly the > reason I cannot EVER do long operations > in the main thread (and by long I mean more than 20-30 ms). If I will do > long operations, my system wont be > responsive enough. > When is it allowed to use threads? Only to do long, standalone > operations, that would block the main thread (such > as wal checkpoints). > That is exactly what I did in my example program. > > What I really wanted to do, is to use long transactions in the main > thread and checkpoint in a background thread. > long transaction increase the performance (and responsiveness) > dramatically, since most of the work is in memory, > and when committing, much less pages needs to be written to the DB > (since many sql statements update the same pages), > so I/O is reduced by order of magnitude. In addition there is no > overhead of begin/end transaction for every statement.
In WAL mode, any data manipulated will not be visible outside the transaction until the final commit. Therefore, why not collect data into an internal buffer, and write it out periodically to SQLite in a background thread. That background thread can do all the inserts and checkpointing in a big transaction, without worrying about blocking the foreground data collection thread. Once the forground thread has collected enough data for a big transaction, it passes if off to the background SQLite thread, then continues on with a new list of data. Using the above scheme, you'll have all the durability guarantees as your existing code, and with no restrictions on the checkpoint time length you currently have. I understand that this may change the structure of your code somewhat, but that is preferable to large scale messing with the WAL code that noone else needs. Christian _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
