WAL databases and shared access discrepancy

[eirnym]

If database is in WAL jounral mode, it's reasonably safe to write in another process to the same database using sqlite3 and compatible libraries. When I open using limbo, it locks database for write, so it cannot be opened in another process.

I often use this to monitor status written in database, e.g. when I download a long queue.

process 1: create database and set WAL:

$ sqlite3 test.db
SQLite version 3.48.0 2025-01-14 11:05:00
Enter ".help" for usage hints.
sqlite> pragma journal_mode=wal;
sqlite> ^D
$ sqlite3 test.db
sqlite> create table test(id integer auto increment);
// pause for another process below
sqlite> select * from test;
1

process 2: opens same database:

$ sqlite3 test.db
SQLite version 3.48.0 2025-01-14 11:05:00
Enter ".help" for usage hints.
sqlite> insert into test (id) values (1);

when I quit both applications, files test.db-shm and test.db-wal are removed.

when I opened database in limbo and then open the same database using sqlite3, in sqlite3 I see following message on similar insert as shown above:

sqlite> insert into test (id) values (2);
Parse error: database is locked (5)

[krishvishal]

@eirnym I think you might not be quitting the CLI using the .quit command. If you do that you wont get this error.

I'm sorry, I have read your issue wrong.

limbo uses exclusive locking by default.

[LtdJorge]

What OS are you on?

[pereman2]

@eirnym Limbo does not plan to support multi process for now, or maybe ever. This is due to unnecessary performance burden that comes with synchronization between processes. If there is a clear use case where this is really needed, we may think about it. But for now, you will need to close limbo to open in another process.

[eirnym]

@pereman2 I don't ask to limbo have multiple processes, I'm asking to allow to open database in separate process while it's in WAL journal mode. I suggested a clear simplified case when it's may be needed.

Writing from multiple applications may be dangerous, but reading is not.

[eirnym]

@LtdJorge I use macOS

@krishvishal Limbo has no public option to open database in non-exclusive mode, or this option is well hidden

[LtdJorge]

I think what Pere means is that Limbo won't support having concurrent access to the same database file. So you could have multiple SQLite readers on one file, or one Limbo reader on that file. But not one Limbo reader and one SQLite reader, foe example, because of the performance impact of synchronization. That is why Limbo locks the file exclusively from the start (in supported platforms), while SQLite only locks exclusively (in memory) when writing.

[pereman2]

@eirnym I think you have a valid point but currently it is hard, because imagine if limbo is in the middle of writing some data to the database, then if SQLite3 tries to read from there it won't know there is another process touching it so it could read an invalid state of the database and that could be catastrophic.

[eirnym]

@LtdJorge this is managed by VFS setting in SQLite.

[LtdJorge]

@LtdJorge this is managed by VFS setting in SQLite.

I know what SQLite does. What we're saying is Limbo will not support as many configurations as SQLite does. One of them is multiprocess access to the same database file.