Hello! I want to have in-memory db shared between multiple db connections.

There was one way before release 3.36.0 that is described in the in-memory db docs. That is shared cache:

file::memory:?cache=shared

or as i understand the same

file:memdbname?mode=memory&cache=shared

sqlite 3.36.0 release notes says:

The "memdb" VFS now allows the same in-memory database to be shared among multiple database connections in the same process as long as the database name begins with "/".

So now I can use

file:/memdbname?vfs=memdb

This way currently is not described in the docs (even nothing is found on request "memdb").

Which should I use? Can someone describe advantages and disadvantages of each variant?

And one question I had when I saw the 3.36 announcement regarding MemDB VFS,
was whether WAL mode was supported in the new mode, for MVCC.

All readers/writers are in the same process, so having shared-memory for WAL
downgrades to plain memory then, just with some synchronization, no?

If that alternative is really not documented, or is too difficult to find, the docs should be improved. (And, in that case, thanks for the report!)

To me, looking at the code, it appears that the memdb specification variations all bottom-out in the common sqlite_open*() implementation with SQLITE_OPEN_SHAREDCACHE passed in among the flags. (I would be shocked if those alternative methods of getting the shared effect for an in-memory DB did not bottom out to the same implementation. It would represent a much better optimization opportunity than many others I've seen exploited.)

I will see to getting this documented (made easier to find.) In the meanwhile, I no reason not to favor the documented approach beyond the possibility of saving a few bytes of data storage.

Then no WAL-mode and no MVCC then, I guess :(

Too bad. I thought going for the VFS approach was precisely to gain WAL-mode
for shared in-memory DBs, thus I don't quite understand why a separate approach
was added, if it ends up going through the same code in the end.

There must be something I'm missing. Looking forward to the new doc.

I'm confused...

This forum post and mentioned there commit says:

This provides a way for threads to share an in-memory database without the use of shared-cache mode

where is the truth?

Unfortunately no wal:

PRAGMA jorunal_mode=wal; PRAGMA journal_mode

returns memory for each in-memory variant.

[a] commit says

This provides a way for threads to share an in-memory database without the use of shared-cache mode

where is the truth?

Regarding my statement:

To me, looking at the code, it appears that the memdb specification variations all bottom-out in the common sqlite_open*() implementation with SQLITE_OPEN_SHAREDCACHE passed in among the flags.

I am going to document what these open options do, after a much more careful study of the code to see what combinations of flags reach the underlying open(). That study may induce a walk-back of the above. Or not.

In my opinion, a shared in-memory store and a shared-cache mode are largely redundant. If the performance advantages all favor one or the other, one should vanish as a needless pitfall. And if they trade against each other, that should also be documented.

I will wait for the documentation update. Thank you!

Well, the point is that shared-cache is frowned upon, and kinda deprecated,
except on very low resources devices, which is precisely not the case of wanting
to use it on large multi-core desktop machines. Except shared-cache was the only
known way to have separate SQLite connections sharing the same in-memory DB within
the bounds of a single process, allowing concurrent access to the same DB from several
threads. Until MemDB VFS. But then you say they end up being the same code... that's confusing.

Shared-cache implies normal Journal mode, with readers blocking writers, and the writer
blocking readers. WAL not supported. While nothing prevents in theory a VFS from supporting
WAL-mode, as long as that VFS supports SHM, which it can if that VFS is in-memory.
Thus my question whether MemDB VFS supports WAL-mode for true MVCC.

Hopefully everything will be clearer soon. --DD

Any updates on the documentation around memdb?

Thanks

The search is still coming up empty, https://sqlite.org/search?s=d&q=memdb. I have an extension that works in an otherwise isolated in-memory database, but wants to expose that database as part of its public interface. Using memdb in the extension and attaching to the "/"-globally named database seems to work fine, but it would be nice to have more documentation on this, in order to rely on it.

Using vfs=memdb does not use shared cache (although I am sure you could specify to use that if you wanted to do so). It is an in-memory database that can be accessed by multiple connections, each with their own private-cache.

This is quite simple to demonstrate:

SQLite version 3.42.0 2023-03-13 16:36:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open file::memory:?cache=shared
sqlite> .connection 1
sqlite> .open file::memory:?cache=shared
sqlite> begin immediate;
VM-steps: 5
Run Time: real 0.001 user 0.000000 sys 0.000000
sqlite> .connection 0
sqlite> begin immediate;
VM-steps: 3
Run Time: real 0.001 user 0.000000 sys 0.000000
Runtime error: database table is locked (6)
sqlite>

Note that the second connection to the same memory database with "shared cache" returns immediately with a SQLITE_LOCKED (6) error which indicates a shared-cache conflict.

Compare:

SQLite version 3.42.0 2023-03-13 16:36:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open file:/dbname?vfs=memdb
sqlite> .connection 1
sqlite> .open file:/dbname?vfs=memdb
sqlite> begin immediate;
VM-steps: 5
Run Time: real 0.001 user 0.000000 sys 0.000000
sqlite> .connection 0
sqlite> begin immediate;
VM-steps: 3
Run Time: real 32.596 user 0.000000 sys 0.000000
Runtime error: database is locked (5)
sqlite>

which returns error SQLITE_BUSY (5) after waiting for the busy timeout to expire, indicating that it is not using shared-cache but rather is using normal database multiple access arbitration.

I am interested in exactly the same use case - in memory db in WAL mode, so I looked at the implementation of memdb. You could do this as well see memdb_io_methods

1. in order to support WAL mode vfs must implement SHM functions
2. there exist one workaround (locking_mode = EXCLUSIVE) to run db in WAL without SHM but you can have exactly 1 connection to the db, which in this use case not an option

whether MemDB VFS supports WAL-mode for true MVCC

at the moment of writing this post, no, it doesn't. Someone needs to add SHM implementation into memdb_io_methods then it can work in WAL mode. I am not familiar with WAL internals to say how much effort is it. It likely requires some workarounds to not create WAL-index file on disk (because you know this is in memory db it should not use FS at all), maybe this is as trivial as memdbDlOpen and similar dll functions I don't know.