On 05/06/2011 05:53 PM, Ole Reinhardt wrote: > Hi Dan, > >>> I have to correct myself a little... the hanging sqlite3_open_v2() does >>> not happe while the prepare of the first query but during the first >>> sqlite3_step() of the first query... >> >> Each shared-cache has a mutex associated with it. When >> sqlite3_step is called it grabs the mutexes for all shared-caches >> it might use. The mutexes are not released until the call >> to sqlite3_step() returns. >> >> So if you have one query that spends a lot of time in sqlite3_step() >> you are going to block your other threads. Unfortunately, that is >> the nature of shared-cache mode. > > Thanks for the info. But is this even true when enabling read > uncommitted isolation mode using: > > sqlite3_exec(log_database_local, "PRAGMA read_uncommitted=True;", NULL, > 0,&err) > > ?? > > I suspect it's the master table lock that is hold there, right?
You are correct that your query will hold a shared-lock on the master table at the shared cache level, but it's the mutex that is causing you problems. Shared-cache locks are held for the duration of a transaction to ensure transactions are correctly isolated. In this case the master table is locked to make sure that the table your query is accessing is not dropped by another thread while you are using it. Mutexes are held for the duration of an sqlite3_step() call to make sure SQLite doesn't segfault when two threads try to access the same shared-cache object. > Any other chance to speed this up (apart from the obvious "optimize the > query, do not use distinct on large tables....)= Without seeing the query or database schema? Not really... Depending on the exact query an index on "xyz" might help. Dan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
