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
