I apologise for the many posts, but I'm writing in hope that one of you may point out something that I'm either doing wrong, or a concept I haven't fully grasped.
I'm aware that prepared statements are tied to the database connection they were created for. In order to get more speed out of my recurring statements (almost every statement I sqlite3_prepare_v2, I'm then re-using it later), I store them in the thread local cache as well. Roughly, here's what I am doing: 1) Thread A: Create a new connection, if one already does not exist for the thread. Store it's 'reference count' in the thread storage (I close a connection when the count becomes zero). 2) Thread A: Get a previously stored statement for that connection against a name (using a dictionary for this) from the statement cache (again, from the thread local storage) - if one doesn't exist, call sqlite3_prepare_v2 on a new statement and save it in the statement cache. Since a single thread can always only have a single db connection, the statement is thread-safe. ... assume some nested asynchronous calls 3) Thread A: Grab a cached connection, and then grab an existing statement. If found, re-use it by first calling sqlite3_clear_bindings(pStmt). 4) Thread A: Close connection (i.e. decrement the reference count, if it's zero first clear the statement cache by calling sqlite3_reset and sqlite3_finalize on all the cached statements). In case the reference count is't 0, the connection is kept alive. Since a thread could be doing various things and a method call may result in another nested method call that needs to query the db again, the connection is kept alive and only closed when necessary. This keeps the overall active 'readers' in check. If there are 7 threads, there are 7 readers in theory (although each thread will almost always run a 'task' and aim at closing the connection if it can). The above scenario can be imagined for multiple asynchronous threads. As I explained, I do however use a persistent 'writer' sqlite connection (along with a reference count for that too) and open / close only if there are no more tasks requiring a writer. The writer was previously using a mutex in case multiple threads need to access the same writer, but given I keep prepared statements in a thread local cache, the statements were always valid against the connection these were created. For the writer I use a separate prepared statement cache by the way. As you can see, the architecture is very elaborate, but all this ensures utmost performance whilst ensuring thread-safety along with concurrency. The app performs very well against multiple asynchronous tasks, but it's being bogged with database corruption every now and then. Could it be that I need to prepare and finalise the statements and not re-use them like I do? Not sure how accurate this is but it seems to suggest that we need to do this: https://stackoverflow.com/questions/36364162/accessing-sqlite-database-from-multiple-processes-and-sqlite-busy Reading up on look-aside memory (https://sqlite.org/malloc.html#lookaside) I'm now even more confused as I think the memory is being stomped over presumably and causing issues since I'm re-using my statements like I do? -- Sent from: http://sqlite.1065341.n5.nabble.com/ _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
