Exclusive write-only lock? (Allow only one process read-write, and multiple read-only)
(1) By Galicarnax (galicarnax) on 2020-09-10 09:57:12 [link] [source]
I've read about sqlite3 locks, and from what I understood it seems impossible to achieve what I want... Nevertheless, I'll ask here, just in case.
Is it possible to make it such that an sqlite database (either with rollback journal or in WAL-mode) might be opened only by one process that can modify it, but still allow other processes open it in read-only mode? If I set exclusive lock, once the writer process makes a change, it will block all other processess even from reading. I want a behavior where all processes, except a single one (say, the first one which opens the database), are not allowed to write but allowed to read.
(2) By Gunter Hick (gunter_hick) on 2020-09-10 11:07:43 in reply to 1 [link] [source]
SQLite does not care which process/thread/connection is used for changing the contents of a database file. You will need to enforce the "designated writer process only" constraint yourself, by setting SQLITE_OPEN_READWRITE only in the designated writer process/thread for the designated writer connection and using SQLITE_OPEN_READONLY everywhere else. You will also have to handle remedial actions for the writer dieing (convert reader or start a new writer) yourself, taking care that you do not end up with two writers accidentally. And you still have to handle transactions properly in readers and the writer so that each has a chance to perform the required operations and access current data.
Is it possible ... opened only by one process that can modify it, but still allow other processes open it in read-only mode?
Not only is this possible, it's a fairly common use-case, and SQLite is excellent at doing this fast and efficiently on a local file-system and a pretty good solution for a Network file-system.
Simply open the DB in the "Writing Process" as normal, and then let every "reader Process" open it with the correct READONLY flag in SQLite API (as Gunther suggested) or if you access it via a Wrapper or Data-Object and connection-string, find and set the correct Read-Only attribute/string/property upon opening it.
To further emphasize what Gunther said - Note that SQLite has NO CONTROL WHATSOEVER over the file other than what you tell it to open it as. If you spawn two "Writer-Processes", SQLite will open both times correctly, and if you start writing from both, SQLite will do it's best to take both inputs (which is not what you want) so you have to manage/block any such possibilities from your application's side. Further to being "unwanted", on a Networked file system, this can even corrupt the file and lose you some data.
Lastly, in the quote above you say:
... an sqlite database (either with rollback journal or in WAL-mode) ...
Note that these are two very different things. In the normal journaling modes, SQLite will "wait" for writers to finish transactions before readers can read. Only in WAL mode can you have readers reading existing data WHILE a writer is writing new data.
This makes it possible to actually write-LOCK the DB (at least on a Local file-system) by starting a write-transaction implicitly and keeping it open, with occasional commits/checkpoints which can be read by readers, but still blocking any other process from writing. However, you still can't control (by just using SQLite) which process gets to the write-lock first. (Some special options exist here, like reading uncommitted data, but that's another discussion)
These above options are simply touching very lightly on the possibilities. you need to study the documentation quite well before making a decision, and/or specify a lot more accurately what your exact intended use/architecture is, then someone here will be able to give some advice on the best way that SQLite can help do it.
 Local File-System means a File-System (and as such: Files) which the same Operating System that is running your process, is directly controlling. If it has to ask another OS for the file data (such as Network files, NAS, etc.) then file-locking feedback is not guaranteed to be accurate and thus general concurrency is not workable, but single-writer DBs should work fine (in my experience, at least).
(4) By David Raymond (dvdraymond) on 2020-09-10 15:18:18 in reply to 1 [link] [source]
Well, if each process knows if it's A reader or THE writer, then they can just play nicely with each other. So I'm assuming that at the time of connection, each process doesn't know whether it's going to become THE writer or not.
One thing you could do is to have the writer greedily hold a reserved lock the whole time. When you connect, attempt a BEGIN IMMEDIATE. If it succeeds then hey, you're the new writing process and are blocking anyone else from writing while still allowing them to both connect and read. If it fails then well, guess you're a reader. The reserved lock held by the BEGIN IMMEDIATE won't block new connections until it's upgraded to a pending lock, which won't be until you first have to write something to the file. In the meantime new connections can be made, and any other connection can read with a shared lock just fine.
Whenever the writer does a COMMIT/ROLLBACK it should then immediately try another BEGIN IMMEDIATE to maintain its hold on the lock. That would have a race condition though, where between the COMMIT/ROLLBACK and the BEGIN another process could swoop in and grab it. I'm pretty sure there's no way to say "commit, but keep a reserved lock when done instead of unlocking completely."
(5) By Richard Damon (RichardDamon) on 2020-09-10 16:39:24 in reply to 1 [link] [source]
As people have said, WAL mode will mostly do what you need. It will allow you to have a writer and most of the time as many readers as you want to access the database at the same time.
It will NOT lock a give accessor as the writer, as was mentioned when the writer commits, it gives up its lock, so someone else can take it, and that someone can even start the request before the writer commits and busy wait for it to be available, so that level of locking would need to be done elsewhere.
Also, there will be occasional periods when the writer will need exclusive control to fold the WAL file back into the database. If a reader holds is transaction forever, it can block this from happening, which will cause the WAL file to keep growing lowering efficiency in space and access time.
(6) By Galicarnax (galicarnax) on 2020-09-10 17:41:19 in reply to 1 [link] [source]
Thanks, all, for the answers. My fault, I didn't formulate the question clearly. Certainly, I know that I can write and read with the same database and use corresponding flags. What I meant is if it was possible to force any process that I may not be aware of (3rd party) to access the DB only in readonly mode when my app uses it. As I can see now, that's not possible. (I had a suspicion it could be possible when I saw there are distinct WAL locks used in the WAL-index file, e.g.,
[...] blocking anyone else from writing while still allowing them to both connect and read.
Could that become an option in SQLite? A "blocking writer (+read) connection",so you won't have to do the IMMEDIATE dance? This as the question seems to come up every now and then. No idea how useful it actually would be.
(8) By David Raymond (dvdraymond) on 2020-09-10 18:26:53 in reply to 7 [link] [source]
Perhaps an extension of the locking_mode pragma to include RESERVED in addition to the current NORMAL and EXCLUSIVE options.
In the scenario you mentioned, you might ponder what should happen if two apps both tried to be the boss, both forcing everything else to stop writing.
You'll come up with one of the many modes and flags which SQLite already makes available: WAL mode,
BEGIN IMMEDIATE, opening with read-only privileges, etc.. There are lots of ways to manipulate SQLite this way and you'll probably find one that suits you.
(10) By Richard Damon (RichardDamon) on 2020-09-12 00:33:15 in reply to 9 [link] [source]
I could see adding a mode where a connection says that basically it ALWAYS wants to hold a write lock on the database, and anyone else attempting to get one should get a "busy" return, and when that connection does a commit, it atomically gets back the write lock.
I don't know enough details about how SQLite does this to know if it is actually possible or feasible. It would have to be done in a way that if that app crashes (or doesn't close properly) that the lock goes away.
It also is definitely an 'edge' case, and might not be really worth adding.
You could get that result by doing a
BEGIN IMMEDIATE and never closing the transaction. Just use savepoints for what should be transactions.
Of course, other connections won't see your changes. But in this situation I'm not sure you'd want them to.
What I meant is if it was possible to force any process that I may not be aware of (3rd party) to access the DB only in readonly mode when my app uses it.
Could user access rights to the db file be of help here?
An authorizer has to be installed per DB conn. The code won't even be available to a 3rd party app, as the OP wants. You don't get a pre-built authorizer simply by opening the DB. If the tech existed to do that in SQLite, it would amount to C code injection into foreign apps, and so probably disabled by a great many apps on security grounds.
Making the DB read-only to "other" through the filesystem may help, but that then means he cannot run any 3rd party apps under his current user, because that would give write access to the DB.
This is the sort of thing that demands an API barrier, where an interposing app exposes a query interface and enforces permissions.