Hello,

I can't quite figure out, reading https://www.sqlite.org/wal.html, if the sentence below implies that readers must have write permission on some database file or directory:

A long-running read transaction can prevent a checkpointer from making progress

Surely the only communication channel between readers and writers is the file system, isn't it?

To ask the same question in another way, can a unix user with only read permission on a database and its directory open a read-only connection on a WAL database and still expect to be able to be granted with snapshot isolation even if there are concurrent writers?

Thank you very much for your answer,

Gwendal Roué

You might need to set the SQLITE_OPEN_READONLY flag on the sqlite3_open_v2() call to prevent SQLite from trying to create the -wal and -shm files, which will fail if the reader can't write to the directory holding the DB file.

A database with a journal mode of WAL must always have a WAL and SHM file when open and in use. The only way to not have them is to change the journal mode to something other than WAL.

Thank you, I know about SQLITE_OPEN_READONLY. But even though, doesn't a reader have to write something so that it communicates to the writers that checkpointing must not remove from the WAL the data accessed by the reader?

Suppose your read-only connection to the database executed a SELECT which returned a thousand rows. You would need to know that another connection to the database didn't change the database part way through the rows being read. So even a read-only connection to the database needs to be able to tell other connections that it is in use.

A WAL database requires two other files to maintain a record of locking and changes. Even if you open the file telling SQLite you need read-only access, SQLite still has to create the -shm and -wal files in the same folder as the database file. So it still needs write permission to that folder.

You might be able to get a result by having another connection create the -shm and -wal files first, then establishing your read-only connection. But that's a hack and I don't know if it will really work.

You might be able to get a result by having another connection create the -shm and -wal files first, then establishing your read-only connection. But that's a hack and I don't know if it will really work.

Thank you, we're getting closer to an answer to my question.

According to your answer, a reader needs write access to the database, even if it opens a read-only connection, so that it can create the -shm and -wal files.

What I still don't know is: if a read-only connection is opened after -shm and -wal files are created, can it prevent checkpointing if the reader process has no write permission?

I can't find anywhere in the documentation if the WAL journal mode requires the write UNIX permissions or not, even for SQLITE_OPEN_READONLY connecttions.

(NON-WAL) Updating the database file requires EXCLUSIVE access to the database. That means that there must be no readers in order to COMMIT a transaction "write the changes to the database". This is necessarily so because the "database file" must be "static" while it is being read, just as all hell may break loose if you update a memory structure while another thread is writing (mutating) that structure. "mutexes" are used to arbitrate access to "memory structures" and "file locks" are used to arbitrate access to files.

When using WAL, this is relaxed because COMMIT does not write to the database, it writes to a "write-aside" log. That means that a transaction can COMMIT (write its changes to the write-aside file) while a reader is reading the database.

From time to time, however, the changes written to the "write-aside" log must be written (called a checkpoint) into the database file.

However, you cannot write changes that "ought not be seen" by a current reader to the database file. Therefore, a long lasting reader can prevent the "write-aside" updates from being copied to the database.

This is well explained in the referenced documentation of "write-aside logging".

Therefore, a long lasting reader can prevent the "write-aside" updates from being copied to the database.

Yes, this is abundantly documented.

My question is whether a reader needs write access in order to make this effective.

I can ask the question in a third way, because I really hope that we can get to the point:

Can a process without write UNIX permission to a WAL database file in and directory prevent concurrent writers from checkpointing?

This is a very precise question!

Thank you very much in advance.

Yes. Read locks can be taken on files to which you do not have write permission.

Thank you very much for your answer!

That's amazing!

Now this is just for my personal culture: how is it possible? What is the communication channel?

Many thanks to you all. The summary, as far as I understand, is:

1. YES a read-only process can access a WAL database, without any ill side effects (I still don't know how it works).
2. BUT such a connection requires that the -wal and -shm files are present before it starts reading.

I can't help but feeling that something is odd with the second sentence, because sqlite3_close_v2 is documented to delete the -wal and -shm files:

[...] so the closing connection runs a checkpoint (while holding SQLITE_LOCK_EXCLUSIVE) and then deletes both the WAL and WAL-index files.

To me, this means that a process without write access HAS NO GUARANTEE it can access a WAL database.

I don't see any answers to your last question.

I suspect the answer you're looking for there is "the lock contention mechanisms, the facilities that prevent simultaneous read and write locks from being established on the same file".

For some kinds of locking (I'm not sure whether any of the VFSes SQLite ships with use such locking), this involves write access somewhere, typically to the directory containing the database. For other kinds of locking, it doesn't. For flock() locking (the only one I know well enough to speak to with any authority), on at least some OSes, a process does not need write access to a file in order to set a lock on it, even an exclusive ("write") lock. Thus, a non-cooperating process can block such locks by other processes indefinitely.

Yes.

The same stability requirements apply when reading the database whether you are confined to only reading, or only reading at the moment in the same way that a "thread" traversing an in-memory structure requires that the structure be unchanging while it is traversing it -- it makes no matter that the "reader" cannot "write".

It is like reading a book. While reading the book, you cannot have something else making changes (adding deleting and renumbering pages). This requirement does not change just because you can or cannot add, remove, or renumber pages. It is inherent in the activity of "coherent reading".

I see what you mean, but I still wonder how a reader without write permission can communicate writers that their eventual checkpointing operations (particularly SQLITE_CHECKPOINT_TRUNCATE) must fail ???

That depends on what you mean by "reader without write permissions".

If by that you mean that CREATE/DROP/INSERT/ALTER/DELETE/UPDATE statements are "intercepted with a 'you cannot do that you silly twit` error message" then that is merely an artificial limitation (conceptually similar to having a guard at the paint-store-door that will refuse to sell you pink paint) -- we call that LOGICAL access control because it is not real, it is merely a convention, sort of like DRM or a flag saying "NotEvil". It does not mean that the process does not have PHYSICAL ACTUAL REAL write access, merely that it is LOGICALLY preventing you from having it -- as long as you play along.

A LOGICAL (ie, application level) read-only connection is still required to have PHYSICAL (ie, Operating System level) write-access to the SHM file. YOU are prevented from writing to the database files, however SQLITE itself has no such limitation.

If the Operating System cannot grant write access to the SHM file, then the database must be immutable (no one can change it). If the database is immutable and nonetheless is "mutated", all hell will break loose.

SQLite requires READ/WRITE access to the SHM (wal index) by all processes connecting to a "mutable" database even if the connection is LOGICALLY read-only.

https://sqlite.org/walformat.html particularly the descriptions of the READ_LOCK[N] fields in the SHM (index) file and how they work might be helpful.

That depends on what you mean by "reader without write permissions".

UNIX permission

A LOGICAL (ie, application level) read-only connection is still required to have PHYSICAL (ie, Operating System level) write-access to the SHM file. YOU are prevented from writing to the database files, however SQLITE itself has no such limitation.

This contradicts this message by D. Richard Hipp:

Yes. Read locks can be taken on files to which you do not have write permission.

What am I supposed to conclude?

Look, I'm really looking for the answer for a simple question: is WAL suitable when some readers don't have write permission?

It's a very simple, and very important question.

So far, my answer is NO, the WAL mode is not suitable when some readers don't have write permission, because a WAL database can exist without any -shm and -wal file, making them unusable by such readers.

I think this should be explicitly stated in the "disadvantages" section of https://www.sqlite.org/wal.html.

Absolutely agree with the importance of stating this as a "disadvantage"

Write permission is also required for read-only connections on a non-WAL database to provide atomicity/consistency. In the case of a hot-journal (eg. application crash/lost power partway through a transaction) the DB file is not guaranteed to be consistent until the journal is rolled back, which requires writing to the DB file.

So I don't think it makes sense for this requirement to be listed as a disadvantage of WAL mode.

Perhaps it could be fixed, so that if the read version is set to 1 and the write version is set to 2, then reading a WAL database is allowed even if the -wal and -shm files are not exist and without creating them, but committing and checkpointing are not allowed unless you first change the read version to 2, and you cannot change the read version from 2 to 1 (while leaving the write version at 2) unless first the database is fully checkpointed and no transactions are active.