> On Feb 14, 2017, at 3:51 PM, Bob Friesenhahn <bfrie...@simple.dallas.tx.us> > wrote: > > Due to timing constraints, it performs all read queries in one thread and > creates a temporary POSIX thread for each update query (this is the > developer's reasoning).
To me that seems kind of backwards, since SQLite supports multiple readers but only one writer. In other words, reads can be parallelized [if you use multiple connections], but it’s not possible to perform more than one write at a time. For example, I’m told the .NET SQLite library keeps a pool of connections for reads, but uses a single connection for writes. > Due to memory constraints (at least 1MB is consumed per connection!), only > one database connection is used. Any thread may acquire and use this one > database connection at any time. With only one connection I don’t think you get any real parallelism. The docs aren’t explicit, but my understanding is that in serialized mode every SQLite API call goes through a mutex belonging to the connection, so only one thread can be acting on that connection at a time. > If we have two threads executing sqlite3_step() on the same connection and > using their own prepared statement, is there any magic in sqlite3 which would > keep sqlite3_step() and sqlite3_column_foo() from consuming (or disrupting) > the results from the other thread? Not if they’re using the same statement. A statement is a stateful object, so using it on multiple threads is probably going to cause problems. Other ways to get in to trouble include * Iterating over a statement’s result set in one thread while mutating the database on another thread, which results in “undefined results” from the iteration (I just got burned by this a few weeks ago) * Beginning and ending transactions on multiple threads, since the transaction is shared state of the connection. Again, my understanding is that SQLite’s thread-safety just says that it won’t crash or corrupt memory or databases, if called on multiple threads. The semantics of the API still mean that you’re not going to get the results you want if you’re not careful about which thread calls what when. (FYI, this is why I think making APIs thread-safe is a waste of time. Even if you do so, the higher level calling patterns still need to be synchronized correctly by the client code, in which case the lower level thread safety is largely unnecessary. And the overhead of all those mutex locks can be pretty high.) —Jens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
