> Le 24 mars 2016 ? 14:46, Domingo Alvarez Duarte <sqlite-mail at > dev.dadbiz.es> a ?crit : > > But in this example there is only one application accessing the database and > only one table with one small record been updated all the time and no active > readers, why it's writing to wal ?
Because it is engineered to do it that way, which is needed to achieve its goal(s). https://sqlite.org/wal.html: ? WAL is significantly faster in most scenarios. ? WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently. ? Disk I/O operations tends to be more sequential using WAL. ? WAL uses many fewer fsync() operations and is thus less vulnerable to problems on systems where the fsync() system call is broken. > And why not update the same record instead of adding a new one in such > situation ? I suppose you wanted to say: why add a new transaction slot to WAL, instead of directly updating the record within the main database file? If that is so, then the answer is: for the same reason as above. One big WAL advantage is to allow you to have multiple readers, each of them having a stable view on the database content (NOT seeing committed transactions), for the duration of their (explicit) transaction, even though at the same time, any number of write transactions might occur (one writer at a time). By default, once the WAL file stores about 1000 pages (or more), a 'checkpointing' operation will be attempted on each new commit, trying to merge as much transactions as possible from the WAL to the main database file. As there may be active readers still needing some of these transactions (to maintain their stability view on the data), not necessarily all transactions can be merged back to the db at once. So the wal file might not be reset completely at that time, and it will continue to grow above 1000 pages. But eventually they will, unless you have a read transaction active for ever. And once all and every connections to the database will be closed, the very last one to close will take care of finalizing the checkpointing (if needed). The WAL additional files will disappear. Maybe WAL is not suited for your use-case as there are disadvantages too: https://sqlite.org/wal.html. -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om
