I am using SQLite 3 in WAL mode compiled with these flags:
#define SQLITE_ENABLE_FTS3 1
#define SQLITE_THREADSAFE 2
#define SQLITE_DEFAULT_MEMSTATUS 0
#define SQLITE_ENABLE_STAT4 1
#define SQLITE_MAX_MMAP_SIZE 0
#define SQLITE_OMIT_DEPRECATED 1
#define SQLITE_OMIT_SHARED_CACHE 1
#define SQLITE_OMIT_AUTOMATIC_INDEX 1
I've got a Mac app with a bunch of plugins (each in their own process) accessing the database at random times and making modifications. All processes are linking against the same shared library built with the custom SQLite code. The documentation says that multiple processes may read at any time, however only one process may actually make modifications. Does this mean I need to somehow choreograph and coordinate the writes between the processes?
I ask because ever since I've adopted WAL, I frequently am seeing reports of malformed databases. This seems to mostly occur if a process crashed after having opened a connection (doesn't matter if it's opened with a SQLITE_OPEN_READONLY flag or not) and another process had already opened a connection or later opened one. I can't always reproduce this reliably but it seems it has something to do with the -shm and -wal index files having been created by one process and the other process either had a copy of its own in memory or there was some mismatch produced somehow. Not that this should be the case, but could it be that the -shm file used by the new process somehow modifies it (or the .db file) without the first process finding out, and thus leading to a corrupt database (as already noted under 2.4 of How to Corrupt SQLite)?
My only guess is that having two processes write to the same db is the root cause of these corrupt instances. If this is true, how are we to coordinate between two separate processes without complex inter-process communication in place? Any ideas? I don't want to resort to using journal_mode=DELETE given I've got a highly multi-threaded application which otherwise benefits from concurrent readers and a single writer.
By the way, this is how I open a reader (multiple readers are opened by multiple threads concurrently):
NSString *path = ...
sqlite3 *readOnlyDB = NULL;
BOOL dbOpened = (sqlite3_open_v2(path.UTF8String, &readOnlyDB, SQLITE_OPEN_READONLY | SQLITE_OPEN_WAL, NULL) == SQLITE_OK);
sqlite3_exec(readOnlyDB, "PRAGMA read_uncommitted=1; PRAGMA query_only=1; PRAGMA synchronous=normal;", NULL, NULL, NULL);
sqlite3_unicode_init(readOnlyDB);
sqlite3_busy_timeout(readOnlyDB, 2000)
// I register custom functions here for the connection
And this is how I am opening a writer (a single writer exists for a single process):
BOOL dbOpened = (sqlite3_open_v2(path.UTF8String, &dbConnection, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL) == SQLITE_OK);
sqlite3_exec(dbConnection, "PRAGMA main.journal_mode=WAL; PRAGMA synchronous=normal;", NULL, NULL, NULL);
sqlite3_unicode_init(dbConnection);
sqlite3_busy_timeout(dbConnection, 2000);
// I register custom functions here for the connection
Before closing a writer, I always run the following checkpoint:
....
sqlite3_exec(dbConnection, "PRAGMA wal_checkpoint(PASSIVE)", NULL, NULL, NULL);
EDIT: I've found another strange behavior, not sure if this is related. If I open my readers with SQLITE_OPEN_READWRITE instead of SQLITE_OPEN_READONLY, the -shm and -wal files are properly deleted after calling sqlite3_close_v2(...) on the connection. If I switch back to SQLITE_OPEN_READONLY, I see that the -shm and -wal files are never deleted even when the process closes cleanly. I was using read-only mode in one of my plugins (which seemed to be causing the corruption mostly, especially in case it quit or the host application using the plug-in crashed etc), I'm seeing this change in behaviour. I wonder if this is an indication of a bug in SQLite or just some behaviour I wasn't aware of. I could gladly switch to readwrite mode and use PRAGMA query_only if that won't break anything else in SQLite in terms of locking, given effectively all my readers are actually writers but just never perform any modifications.
I have had a similar issue with SQLite3 using Python client on Linux: DB file getting malformed when client crashes while inside an open transaction, while another concurrent process performs read/write query.
That issue resolved after I added exception handler to make sure transaction closes before crashing process terminates.