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
