The "normal" connection mode would be to have a separate connection for each thread, with no shared cache. Each connection is opened with the FULLMUTEX (serialized) flags. This is the default. Each connection is fully isolated from every other connection. Assuming that each "thread" has its own connection, each "thread" is isolated (database wise) from each other thread (by its different connection context).
You may "share" one connection amongst multiple threads provided that you "follow the entrance requirements". Since database isolation is provided by the connection, multiple threads sharing the same connection share the same database context. You may, if you must, optimize (ie, reduce) CPU usage by recovering the time spent executing the FULLMUTEX protections used in the default SERIALIZED mode by taking responsibility for this yourself and tell the SQLite3 library not to cover your ass but rather use YOUR OWN CODE or design to enforce the single-serial-entrance requirement of the underlying SQLite3 engine. You will "recover" the time taken within the library to enforce this protection and instead you will spend that CPU time doing it yourself in your application, or will ensure the application design is such that violation of the rules is not possible (and choosing to perhaps forgo some operating system provided features that could "break" your design, such as DPC's or system managed thread pools in Winders). However, the amount of CPU time saved is small and the added complication is large. In other words you would do this only if it is cost effective and each nanosecond matters (though it would probably be more cost effective to just buy better hardware). On the other hand, you may need to optimize memory usage at the expense of more CPU usage. You do this by enabling "shared cache" so that the multiple connections "share" the same cache thus reducing memory used by multiple connections (since they now have one cache shared amongst them rather than one cache per connection). This might permit an otherwise well designed application that uses multiple connections and threads to run on a device which is memory constrained by trading memory for CPU (reduce memory increase CPU), after determining that you cannot fix the defect by simply adding more memory (which is likely far more cost efficient). Since you are optimizing memory usage (ie, reducing it) by deliberately trading it for CPU (which will be increased to make up for the lack of memory) you have already decided that you do not want/need to optimize CPU usage (so there are pretty much no options here to optimize CPU usage). Using a single connection in shared cache mode (vs that same one connection in non-shared cache mode) does nothing to optimize memory (you still have one cache being used for your one connection) but does still use all the extra CPU processing associated with managing the shared cache, because that shared cache still has to be managed even though you are only using one "shared cache" connection at the moment. Therefore, one probably ought to use the default (one connection per thread, non-shared cache, in serialized mode) and optimize IF AND ONLY IF it is found that optimization is required. Otherwise, one might find that they have prematurely optimized for the wrong thing and then spend inordinate amounts of time correcting the error (or griping about it on the mailing list). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Jesse Rittner >Sent: Sunday, 30 December, 2018 08:53 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Shared Cache vs. Serialized > >I've been reading about the shared cache feature and I have a >question. It >seems to me that there are two ways we can have multiple threads all >accessing a database via a "single" connection. One, establish a >single >serialized private cache connection up front, and give it to all the >threads. Two, have each thread independently establish a multi-thread >shared >cache connection. What are the trade-offs between these two >approaches? (I >am using "serialized" and "multi-thread" as they are defined here: >https://www.sqlite.org/threadsafe.html) > >Also, if my application only establishes a single connection to a >particular >database, does it matter at all if that connection uses a shared or >private >cache? In particular, I am establishing a single multi-thread >connection >that I protect with my own mutex. My assumption is "no", but I just >want to >confirm. > > > >-- >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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
