The default threading mode for SQLite can be Serialized, which means only one thread at a time.
See http://www.sqlite.org/threadsafe.html However, you can change it to Multithreaded either at compile time or via a call to sqlite3_config() (that's in the C API -- I don't know about Python, but I presume it has a version of the call) which allows you to use to select a different threading mode. You can also use flags on the open call as well. Multithreaded mode allows SQLite to be accessed via multiple threads as long as threads don't shared connection handles. This is the what's sometimes called the apartment model of multithreading. Best, Peter > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Bo Peng > Sent: Saturday, October 22, 2011 12:53 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Concurrent readonly access to a large database. > > Dear all, > > I have a database with about 5000 tables each with more than 1 million > records. I needed to get some summary statistics of each table but > find that it will take days to run 'SELECT count(*) FROM table_XX' > (XX=1,...,5000) sequentially. I therefore created 10 threads, each > having its own database connection (sqlite3.connect() from python). To > my dismay, it appears that only one thread was allowed to access the > database at any time so the overall speed was not improved. I further > created 10 processes, each running one query (e.g. 'sqlite3 > file:mydatabase?mode=ro "SELECT count(*) FROM table_XX"'), but I still > could see any improvement in performance... > > Because each thread/process took only 10M RAM and about 1% CPU, > staring 10 threads/processes should not be a problem at all. Before I > investigate further (e.g. programming error, bottleneck of disk I/O), > can anyone tell me if I am going the right direction? I mean, is it > possible, in theory, to read a sqlite database from multiple > threads/processes each with performance comparable to a single > thread/process? I understand writing to a database from multiple > threads can be troublesome but I only need to read from it. > > Many thanks in advance, > Bo > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
