Re: [sqlite] multithreaded app with in-memory database and transactions

[Wim Hoekman]

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

[sqlite] multithreaded app with in-memory database and transactions

[Wim Hoekman]

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