shared in memory db: :memory:+shared cache OR memdb VFS?
(1.1) By andse-t610 on 2021-07-06 14:20:17 edited from 1.0 [link]
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](https://www.sqlite.org/inmemorydb.html). 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?
(2) By ddevienne on 2021-07-06 14:16:04 in reply to 1.0
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?
(3) By Larry Brasfield (larrybr) on 2021-07-06 14:37:09 in reply to 1.0 [link]
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.
(5) By ddevienne on 2021-07-06 14:48:32 in reply to 3 [link]
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.
(7) By andse-t610 on 2021-07-06 15:10:04 in reply to 5 [link]
Unfortunately no wal: > PRAGMA jorunal_mode=wal; > PRAGMA journal_mode returns `memory` for each in-memory variant.
(6) By andse-t610 on 2021-07-06 15:06:59 in reply to 3 [link]
I'm confused... This [forum post](https://www.sqlite.org/forum/forumpost?udc=1&name=9eb30885ebc71e46) and mentioned there [commit](https://www.sqlite.org/src/info/533fffc4a39b01c3) says: > This provides a way for threads to share an in-memory database **without the use** of shared-cache mode where is the truth?
(8) By Larry Brasfield (larrybr) on 2021-07-06 15:40:54 in reply to 6 [link]
> \[a\] commit says >> This provides a way for threads to share an in-memory database <b>without the use</b> 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.
(9) By andse-t610 on 2021-07-06 15:47:25 in reply to 8 [link]
I will wait for the documentation update. Thank you!
(10) By ddevienne on 2021-07-06 19:02:49 in reply to 8 [link]
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
(13) By Keith Medcalf (kmedcalf) on 2023-03-13 22:11:37 in reply to 8 [link]
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.
(4) By Richard Hipp (drh) on 2021-07-06 14:39:09 in reply to 1.1 [link]
I think you should use memdb. Yes, I need to work on the documentation.
(11) By anonymous on 2022-04-15 03:13:43 in reply to 4 [link]
Any updates on the documentation around memdb? Thanks
(12) By Bjoern Hoehrmann (bjoern) on 2023-03-13 21:39:31 in reply to 11 [link]
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.