> Le 27 mars 2018 à 22:55, Deon Brewis <de...@outlook.com> a écrit : > > The model we use for SQLITE is to use a connection pool (connections opened > via sqlite3_open_v2). > > We have many threads, but only one thread can use a connection at a time - a > thread takes it out of the pool, and when done, returns it to the pool.
Been there, changed plans. In our case we profiled no net benefit from such a pooling compared to have each thread which need a connection to actually open it, use it and then close it. It looks like you really need a text heavy / complex schema (taking some noticeable time to parse upon each new attachment) and a very good connection pool mechanism (not adding too much contention when obtaining a connection from the pool or returning it) to really see benefit from this more complicated scheme. Our model which is conceptually equivalent to running N processes instead of N threads, guarantees us to be able to use sqlite3_config(SQLITE_CONFIG_MULTITHREAD) at application startup, or compile sqlite with SQLITE_THREADSAFE=2, or yet use SQLITE_OPEN_NOMUTEX on attachment themselves, all three being equivalent means to get SQLITE_THREADSAFE=2 mode) to lower (a very small yet) unneeded contention inside sqlite, since we take care of never ever share any attachment (and child objects) between threads, *not even reuse one later in another thread*. > The only exception to this is calls to: sqlite3_interrupt(), which can be > called from any thread. > > Under that model, do we need to pass SQLITE_CONFIG_MULTITHREAD ? If you ask wether it is required, no it isn't. If you ask wether you may consider using that mode, I'd say maybe, but there are two risks I wouldn't take. 1) It all depends on wether SQLite might have, directly or indirectly, any kind of thread-local storage on behalf of an opened attachment. If yes (and I don't know it for sure) you would end up involuntary breaking the contract between you and SQLite and would risk all sorts of horrible things. 2) The slightest occasional mistake with the connection pool could get two of your threads share a same attachment. With your connection pooling model, I would strongly suggest that you keep SQLITE_THREADSAFE=1 which should be the default unless you use a specifically compiled version of SQLite. Assuming your schema is complex (which would justify the pooling mechanism to gain on schema parsing at each connection), most of your queries are probably too, and the minimalist contention that the normal threading mode will imply is probably very hard to measure in real life. If you're not sure of the default behaviour of your SQLite instance you link with (if not compiled by you), you may want to pass SQLITE_OPEN_FULLMUTEX on each sqlite3_open_v2 calls to ensure serialized mode is in effect. https://www.sqlite.org/threadsafe.html -- Best Regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
