I?m also concerned about this.
I have used SQLite since around 2008 with great success. For the latest version of my application I decided to switch to using WAL mode and shared cache, to gain better performance. I use pragma synchronous=NORMAL and for a highly-critical (yet small, 2 MB) database even synchronous=FULL. My application is multi-threaded, but each thread uses a separate instance of SQLite. Typical database sizes are from 2 MB to 8 GB. Recently I get an alarming large number of reports about databases with the ?database disk image is malformed?. Pragma integrity_check() reports a variety of errors in these cases. The typical scenario is: Windows 7 or 8. One user. Database on a local hard disk or SSD. No power failure. No blue-screen or other issue from the ?How to damage your SQLite database? help topic. The error just happens. It may cause SQLite to refuse loading the database, or it is discovered when my users run the routine diagnosis, which includes a pragma integrity_check() and if it returns anything other than ?OK? it flags the database as defect. Users then need to restore the database from the backup. A database once marked as defect will always stay in that state. My application logs all error codes from SQLite but I have not seen anything unusual in the logs provided to me by customers. I have even added an error callback for SQLite (as per our recent discussion), to get more info about the error. In the log files I have seem, this handler was not called and there are no unusual errors. But the database corruption may be detected days after the actual error happened, and then the original logs are gone. I depend on my users to get logs and that does not work too well. Questions: Is there a higher risk in using WAL and/or shared_cache? In a mulit-threaded environment? Is it safe to switch existing databases back from WAL to non-wall mode? Is there a specific workflow? SQLite was always so reliable and I had maybe one report about a damaged database in 3 months (and usually it was a hard defect or a power failure). But now I get reports about damaged databases every week, sometimes even for new databases which have been created an hour ago. I fear that by using WAL/shared_cache with multiple SQLIte instances in multiple threads I somehow stress out SQLite, causing database damage under some conditions. I would go back to the tried-and-true non-WAL no shared-cache mode and let a few hundred users test that for a while to see if this causes a drop in damaged databases.
