Once I figured out how to reliably get the reader and writer connections open on a database — key point, when creating the database let the writer get through all of the creation work before opening any readers — I've been getting great concurrency behavior for simultaneous reads and writes in WAL mode.
What's less great is that if you have enough read activity, the checkpoint logic may never be able to actually reset the WAL and the WAL can get very large. Basically, if there is a read in process that uses the WAL, it can't get reset. This would obviously be a change to the WAL implementation, but I've been thinking that it ought to be possible to use essentially two WALs as essentially successive appendages to the main database and to swap their roles as the earlier WAL gets written into the main database. In other words something like the following: Logical view: DB ++ WAL_A ++ WAL_B where ++ is essentially the WAL overlay logic. Writes always go into the second WAL. Checkpoints copy from the first WAL into the database. When a checkpoint is done, if there are no reads with holds on WAL_A, we can swap the roles of the WALs, reset WAL_A which is now the second WAL and hence the target for writes and start copying WAL_B which is now the first WAL into the database. The trick comes in what we do when we start a read. If the first WAL has not been fully checkpointed, then we need to grab both WALs. (We could ignore the second WAL if it is empty, but that's immaterial to this discussion.) If, however, the first WAL has been fully checkpointed, then a read need not grab it which then leaves us free to do the WAL swap. Such a scheme won't help with long reads blocking WAL reset, but it seems like it would eliminate issues with having a steady stream of small read operations blocking WAL reset. Does this make sense? Does it seem useful? (It seems useful to me when I see multi-megabyte WAL files.) Mark _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users