> On Dec 30, 2018, at 7:52 AM, Jesse Rittner <rittneje+sql...@gmail.com> wrote: > > 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?
Be careful — both of these approaches lack isolation (the I in ACID) between the threads. If you share a connection, then if one thread begins a transaction, all the other threads of course share in that transaction. In other words, thread B can see intermediate uncommitted writes from an ongoing transaction in thread A. If you don’t want this (and it tends to be not what you want in a typical GUI application) you’ll have to set up your own mutex to lock out all other threads during a transaction. But this reduces availability. If you use shared-cache mode, it _seems_ as though your threads should be isolated since they have separate connections. But since the connections are sharing a cache, they still end up seeing uncommitted writes. (Caveat: This is from memory and may be wrong. I briefly turned on shared-cache mode a few years ago, and had to back it out a month later after a series of weird application bugs. IIRC, this is the behavior that led to the bugs. There is a web page explaining this somewhere on the site.) That’s not to say you can’t use these approaches, but you have to design your code such that a transaction is global, not per-thread. —Jens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
