> Le 14 sept. 2016 à 18:14, Alex Ward <cov...@yahoo.com> a écrit : > > I don't think we can afford to have a connection per thread. We have an > arbitrarily large number of threads coming and going all the time and a > combination of the amount of memory each connection is taking up and how long > it takes to connect to the db it looks like limiting the number of > connections would be the direction we go if we can get it to work. Maybe a > pool of connections.
If I'm permitted: you're wrong. This assertion is plain wrong, even when I don't have the least idea of your code architecture and the number of threads we're talking here. You, generally, won't get anything, except problems or at least 'complications', by sharing a SQLite connection between multiple threads. For one thing, please, do _not_ think you will gain anything like better performance from this approach. To get it to work you will basically have to get to do so much mutual exclusion between threads competing for the shared connection that the workload done by these threads could as well be serialised in a single thread. And this is not what your threading design is intended to do, I guess. Unless you have a very complex schema (I would say at the very least more than on hundred tables and other create statement), opening a SQLite connection is lightweight, especially compared to whatever experience you might have had with quite any other SQL system. Also please keep in mind that in SQLite, the 'connection' object is the 'transactional-control' object, which quite often is a distinct object in other SQL systems. My only recommendation would be to take the time to test your software with one new connection opened by any thread when it needs it or at start of thread, then properly closed when done with it or right before thread end. The one case where I agree this wouldn't be appropriate is if the database file is NON local (network filesystem). But then a heavily multi-threaded piece of software dealing with such a remote file is probably wrong in the first place. On Windows systems what I do is to pool threads. Creating a thread is far from being costless (in resources and time). There I get performance benefits. And from using WAL mode along with a nice distinct connection per each thread, I get a good level of read concurrency among threads. I win on two levels where you intend to loose on both of them (pooling connections and sharing them across threads at the cost of mutual exclusion contention to get it working). :) -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
