Correct.  "memory" databases can only be shared between connections in the same 
process, and then only by the sharedcache method.  In effect, a "memory" 
database is nothing more than a cache, and sharing it between connections means 
sharing the cache.  cache=private uses a separate cache for the connection and 
therefore (by definition) a different "memory" database.

You could, of course, use an "on disk" database where the database resides on a 
memory resident filesystem.  On Linux you would simply mount another filesystem 
using tmpfs and put your "on disk" database there.  Delete the "file" off the 
tmpfs filesystem when you are done with it.  (Or just use /tmp if it is mounted 
using tmpfs which it usually is.  Just beware that everyone has access to /tmp 
and if you mount your own tmpfs at a different location you can control the 
access permissions).

On Windows the closest thing I know of that can create an autosizing ramdisk is 
http://winramtech.atwebpages.com/RAMDriv/ramdriv.htm though I have not tried it.

For other OS's, Google is your friend!

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Wim Hoekman
>Sent: Thursday, 13 February, 2020 11:44
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] multithreaded app with in-memory database and
>transactions
>
>I have an app which is multithreaded. Sometimes during lengty inserts a
>different thread (which only reads the database) sees part of the
>updated data.
>
>This would be solved by using transactions. However, during the
>transaction the "reading" thread gets a 'database table is locked' error.
>
>In my case, transactions work fine when using on-disk database file, but
>not when using an in-memory database.
>
>In my code, I open the database twice, so I have two separate database
>connections: one for updating, one for reading.
>
>I've tried several ways of opening the in-memory database:
>
>1) "file:memdb?cache=shared&mode=memory",
>SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
>               does not work, 'database table is locked' error
>
>2) "file:memdb?cache=private&mode=memory",
>SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
>       does not work, "different" databases
>       data added via one db connection is not visible when querying via
>other
>db conncection
>
>3) "file:filedb?cache=shared", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
>       does not work, 'database table is locked' error
>
>4) "file:filedb?cache=private", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
>       works, but is not in memory
>
>5) "file:filedb", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
>does not work, 'database table is locked' error
>
>       (because I called sqlite3_enable_shared_cache( 1 )! )
>
>6) "file::memory:?cache=shared", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
>       same behaviour as 1)
>
>7) "file::memory:?cache=private", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI
>       same behaviour as 2)
>
>It seems I can not get 2 database connections to the same in-memory
>database, without using a shared cache.
>And having a shared cache causes 'database table is locked' errors.
>
>Did I miss something, or is multithreaded transactions with an in-memory
>database not supported?
>Any hints or tips to get this working are appreciated.
>
>Regards,
>
>Wim.
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to