WAL mode for in-memory databases?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

WAL mode for in-memory databases?

Dominique Devienne
According to [1] WAL mode does not apply to in-memory databases.
But that's an old post, and not quite authoritative when not from the
official SQLite docs.

I'd like to benefit from the MVCC of WAL mode, but for an in-memory
database,
with different threads, each with its own connection, accessing a single
in-memory DB.

Can this do done? If not, why is WAL mode deemed not useful for in-memory?
i.e. is there a work-around that makes WAL-mode in-memory superfluous?
Or it's not superfluous and not supported, but could technically be
supported?

Thanks for any insights. --DD

[1]
https://stackoverflow.com/questions/28358153/sqlite-wal-mode-in-memory-database-with-private-cache
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WAL mode for in-memory databases?

wmertens
AFAIK, your best bet is to put a file db on a ramdisk (tmpfs). The
":memory:" DB is per connection only.

Wout.


On Mon, Jan 14, 2019 at 11:37 AM Dominique Devienne <[hidden email]>
wrote:

> According to [1] WAL mode does not apply to in-memory databases.
> But that's an old post, and not quite authoritative when not from the
> official SQLite docs.
>
> I'd like to benefit from the MVCC of WAL mode, but for an in-memory
> database,
> with different threads, each with its own connection, accessing a single
> in-memory DB.
>
> Can this do done? If not, why is WAL mode deemed not useful for in-memory?
> i.e. is there a work-around that makes WAL-mode in-memory superfluous?
> Or it's not superfluous and not supported, but could technically be
> supported?
>
> Thanks for any insights. --DD
>
> [1]
>
> https://stackoverflow.com/questions/28358153/sqlite-wal-mode-in-memory-database-with-private-cache
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WAL mode for in-memory databases?

Dominique Devienne
On Mon, Jan 14, 2019 at 2:23 PM Wout Mertens <[hidden email]> wrote:

> AFAIK, your best bet is to put a file db on a ramdisk (tmpfs).


That's not a very portable solution, and a work-around at best.

I don't see anything technical that would prevent WAL to work for
":memory:".
"Shared-memory" "in-process" is just memory about all...
And mutexes already exists in SQLite to protected access when necessary.

In-memory DBs are just so useful, and WAL is just so useful,
I just don't see why we can't have both at the same time.
Richard?


> The ":memory:" DB is per connection only.
>

Not really. You can open the same :memory: DB
from different connections in the same process, via URIs. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WAL mode for in-memory databases?

Brian Macy
I’m very interested in the answer to this as I was planning on do the exact same thing.  Not sure my app would even be able to function without WAL mode.

Brian Macy

On Jan 14, 2019, 8:28 AM -0500, Dominique Devienne <[hidden email]>, wrote:

> On Mon, Jan 14, 2019 at 2:23 PM Wout Mertens <[hidden email]> wrote:
>
> > AFAIK, your best bet is to put a file db on a ramdisk (tmpfs).
>
>
> That's not a very portable solution, and a work-around at best.
>
> I don't see anything technical that would prevent WAL to work for
> ":memory:".
> "Shared-memory" "in-process" is just memory about all...
> And mutexes already exists in SQLite to protected access when necessary.
>
> In-memory DBs are just so useful, and WAL is just so useful,
> I just don't see why we can't have both at the same time.
> Richard?
>
>
> > The ":memory:" DB is per connection only.
> >
>
> Not really. You can open the same :memory: DB
> from different connections in the same process, via URIs. --DD
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WAL mode for in-memory databases?

Thomas Kurz
It would also be very helpful if more control about in-memory-databases was available. As far as I have understood, an in-memory database is deleted when the last connection closes. This requires me to always hold a connection to an in-memory database even if don't need it right now.

Maybe one could introduce a pragma "persistent_inmemory" that allows to keep an in-memory-database until explicitely deleted. (DROP DATABASE IF EXISTS ...)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WAL mode for in-memory databases?

Stephen Chrzanowski
That doesn't sound healthy at all.  If your application dies, what happens
to the database?  What if something rogue starts hitting it and just chews
up your memory?

IMO, Mem databases should be short lived and treated simply as an
intentional cache.  I get they're fast, but, long term life for a finite
resource isn't something I want kicking around when an application dies.

On Mon, Jan 14, 2019 at 1:43 PM Thomas Kurz <[hidden email]> wrote:

> It would also be very helpful if more control about in-memory-databases
> was available. As far as I have understood, an in-memory database is
> deleted when the last connection closes. This requires me to always hold a
> connection to an in-memory database even if don't need it right now.
>
> Maybe one could introduce a pragma "persistent_inmemory" that allows to
> keep an in-memory-database until explicitely deleted. (DROP DATABASE IF
> EXISTS ...)
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users