I have placed databases on/in /dev/shm and shared them across both threads and processes.
Jim "Jed" Dodgen j...@dodgen.us On Thu, Feb 13, 2020 at 2:38 PM Keith Medcalf <kmedc...@dessus.com> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users