PHP SQLite PRAGMA journal_mode = wal和readonly用户

Question

I read that WAL mode allows a user users to read while during a write session.
Therefore I should be able to have 3 sessions reading data and one writing data at the same time, This sounds like a good thing for my use, so I figured I should define the session type so the system knows if this session is a reader or a writer using the connection flag.
http://php.net/manual/en/sqlite3.open.php#refsect1-sqlite3.open-parameters
It states here that if the session is not closed cleanly that the -shm and -wal files are not removed
https://www.sqlite.org/tempfiles.html#write_ahead_log_wal_files
After a read session the temporary files are not removed, thus meaning the session was not cleanly closed despite calling the close function and it returning true, so why are the files not being deleted when using the SQLITE3_OPEN_READONLY flag? Should I even use a flag at all?

Answer 1

You've conflated SQLite's concept of 'readers' and 'writers' with the ability of the PHP SQLite3 driver to open a file as read-only.

Using the SQLITE3_OPEN_READONLY flag will cause PHP to block all write operations with a warning:

Warning: SQLite3::exec(): attempt to write a readonly database in ...

This can be useful if you want to ensure no writes can happen from your application (perhaps as a security measure). But it's not related to SQLite's readers and writers.

As pointed out by CL all processes accessing the WAL mode database need write access. This is explicitly documented:

Also, if multiple processes are to access a WAL mode database, then all processes should run under user or group IDs that give them write access to the database files, the WAL file, the shared memory -shm file, and the containing directory.

And mentioned as a disadvantage at the top of that page:

It is not possible to open read-only WAL databases. The opening process must have write privileges for "-shm" wal-index shared memory file associated with the database, if that file exists, or else write access on the directory containing the database file if the "-shm" file does not exist.

SQLite itself decides if a process is a reader or writer when it receives a command to execute. For example, if a single process executes a SELECT, then a INSERT, then a SELECT again it changes from reader to writer to reader. SQLite will handle locking (potentially blocking other processes) automatically. (Note that this is a simplified explanation that can be quite different with different modes of database operation.)

For your purposes you shouldn't use the SQLITE3_OPEN_READONLY flag.