Hello Olivier, thank you for the hints. I think I do it correctly, I compiled with SQLITE_CONFIG_MULTITHREAD and attach the database with the URI option cache=shared. Also, each connection is used by one thread at a time. But another thread may reuse a connection - I hope that is allowed.
Are you also opening/closing database connections and prepare statements while another SELECT is running? Thanks, Detlef. -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Olivier Mascia Sent: Monday, April 18, 2016 2:47 PM To: SQLite mailing list Subject: Re: [sqlite] UPDATE/open/close blocked while executing SELECT > Le 18 avr. 2016 ? 14:17, Detlef Golze <Detlef.Golze at point.de> a ?crit : > > Hi, > > I am using SQLite C amalgamation version 3.08.11.01 compiled for Multi > Threading Mode and using WAL Journal Mode. > > Sometimes I need to execute a SELECT statement which takes a very long time > to complete. I realized that during that time other functions are blocked for > a significant amount of time, eventually they get through after few minutes, > but then, another statement/function hangs. I have seen UPDATE statements > blocking and also functions like sqlite3_prepare16_v2(), open/close calls for > the same database. > > It is usually blocking at btreeLockCarefully(). > > Is there something I can do to avoid such long starvations or is that by > design? > > Thank you very much, > Detlef. I'm not an old-timer using SQLite and participating here, but I'm using it in WAL journal mode only, especially for its feature of 'multiple readers do not block writer and see a stable view of the db based on the instant they started their transaction'. Just to clarify: you mean compiled using SQLITE_CONFIG_MULTITHREAD (and not SQLITE_CONFIG_SERIALIZED), is that it? Then, if using threads in the application, each thread has its own connection (or multiple connections) but no thread share any connection with another one? (This is required for SQLITE_CONFIG_MULTITHREAD mode, else you would need SQLITE_CONFIG_SERIALIZED, and that would add a lot of contention). Also, are your connections using private (SQLITE_OPEN_PRIVATECACHE) or shared cache (SQLITE_OPEN_SHAREDCACHE)? When using SQLITE_CONFIG_MULTITHREAD, taking care no application thread share a connection with another, and using SQLITE_OPEN_PRIVATECACHE, along with WAL journal mode, I see no issue similar to what you report in our own programming. -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
