multithreaded app with in-memory database and transactions
Wim Hoekman <[email protected]>
| Newsgroups | gmane.comp.db.sqlite.general |
|---|---|
| Message-ID | <[email protected]> |
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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users