Re: [sqlite] multithreaded app with in-memory database and transactions
On 13-Feb-20 23:38, Keith Medcalf 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! My application should run on Linux and Windows. Using tmpfs on Linux could be doable, but having to setup a ramdisk on Windows would only complicate matters... Are there any plans on supporting multiple connections to in-memory databases with separate caches? If not, how much effort would be involved in getting this to be supported? Regards, Wim. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multithreaded app with in-memory database and transactions
On Thursday, 13 February, 2020 17:58, Jim Dodgen wrote: >I have often wondered what the performance difference is between /dev/shm >and :memory: databases Theoretically a :memory: database is faster than a /dev/shm stored database. A :memory: database is purely in memory and has no extra connection cache (and no transactions to speak of). A database in /dev/shm is just like an on-disk database, and the connection has its own memory cache, the difference being that there will never be a wait to flush the OS cache to disk (the file exists only in the OS file cache). Using a /dev/shm file will still require file read/write because it is a file as far as the application is concerned. A file on /dev/shm will release all its space back to the OS free pool when the file is deleted but will persist until reboot when closed by the application (and not deleted). A :memory: database will release its memory back to the OS when it is detached/closed. A database opened on a RAMDISK will behave like a database on /dev/shm except that deleting the file will not release the space allocated for the RAMDISK back to the OS since the RAMDISK usually permanently allocates virtual storage for its contents when created. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multithreaded app with in-memory database and transactions
I have often wondered what the performance difference is between /dev/shm and :memory: databases Jim "Jed" Dodgen j...@dodgen.us On Thu, Feb 13, 2020 at 4:48 PM Keith Medcalf wrote: > > On Thursday, 13 February, 2020 17:06, Jim Dodgen > wrote: > > >I have placed databases on/in /dev/shm and shared them across both > >threads and processes. > > Yeah, /dev/shm is a pre-existing tmpfs filesystem, separate from the one > mounted on /tmp. I keep forgetting about that one ... > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > > ___ > 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
Re: [sqlite] multithreaded app with in-memory database and transactions
On Thursday, 13 February, 2020 17:06, Jim Dodgen wrote: >I have placed databases on/in /dev/shm and shared them across both >threads and processes. Yeah, /dev/shm is a pre-existing tmpfs filesystem, separate from the one mounted on /tmp. I keep forgetting about that one ... -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multithreaded app with in-memory database and transactions
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 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 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=memory", > >SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI > > does not work, 'database table is locked' error > > > >2) "file:memdb?cache=private=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
Re: [sqlite] multithreaded app with in-memory database and transactions
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 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=memory", >SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI > does not work, 'database table is locked' error > >2) "file:memdb?cache=private=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] 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=memory", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI does not work, 'database table is locked' error 2) "file:memdb?cache=private=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
