[sqlite] database is locked on WAL with large number of reader connections
I've got a WAL database with only readers right now. I'm opening and close the database at pretty rapid rate, and usually have about 100 connections open at a time. I'm using prepared statements for SELECTS and I'm pretty sure I'm finalizing the statements. After a while I start getting back database is locked. How can this happen with a WAL database? This is OS X. --erik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database is locked on WAL with large number of reader connections
On Wed, Jan 25, 2012 at 8:01 PM, Erik Fears str...@strtok.net wrote: I've got a WAL database with only readers right now. I'm opening and close the database at pretty rapid rate, and usually have about 100 connections open at a time. I'm using prepared statements for SELECTS and I'm pretty sure I'm finalizing the statements. After a while I start getting back database is locked. How can this happen with a WAL database? This is OS X. I didn't think it could, under the circumstances you describe. Are you sure your description of the scenerio is correct? Are you building SQLite yourself, our using the one that comes built-in to OSX? Is this all happening in a single process? Or lots of separately processes? If you have only readers, why both with WAL mode, single DELETE mode works just as well in that case? --erik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database is locked on WAL with large number of reader connections
I have a writer, but the thread is not writing during this test. This is built by me. This is single process, one connection per thread, 100 threads. The threads are being created/destroyed often, though, and each time one is created it creates a new DB connection. I understand this isn't ideal, and plan to move to having less connections or using a pool, but I still don't understand why this would be occurring with WAL. --erik On Wed, Jan 25, 2012 at 5:39 PM, Richard Hipp d...@sqlite.org wrote: On Wed, Jan 25, 2012 at 8:01 PM, Erik Fears str...@strtok.net wrote: I've got a WAL database with only readers right now. I'm opening and close the database at pretty rapid rate, and usually have about 100 connections open at a time. I'm using prepared statements for SELECTS and I'm pretty sure I'm finalizing the statements. After a while I start getting back database is locked. How can this happen with a WAL database? This is OS X. I didn't think it could, under the circumstances you describe. Are you sure your description of the scenerio is correct? Are you building SQLite yourself, our using the one that comes built-in to OSX? Is this all happening in a single process? Or lots of separately processes? If you have only readers, why both with WAL mode, single DELETE mode works just as well in that case? --erik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ 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
Re: [sqlite] database is locked on WAL with large number of reader connections
On 01/26/2012 09:05 AM, Erik Fears wrote: I have a writer, but the thread is not writing during this test. This is built by me. This is single process, one connection per thread, 100 threads. The threads are being created/destroyed often, though, and each time one is created it creates a new DB connection. I understand this isn't ideal, and plan to move to having less connections or using a pool, but I still don't understand why this would be occurring with WAL. When a new connection is made to a WAL database, SQLite checks to see if there already exist any other connections. If there do not, then it needs to initialize the shared-memory region based on the current contents of the *-wal file. This is referred to as recovering the wal file in some places. While recovering the WAL file, SQLite takes an exclusive lock on the database file. So if two processes try to connect simultaneously, there is a race to recover the wal file. One process does the work of reading the wal file from disk and initializing shared-memory, and the other will get the SQLITE_BUSY error. If the wal file does not exist or is zero bytes in size, SQLite still takes this lock and runs the recovery code to initialize the shared-memory. Of course, it is very fast in this case. And since, assuming there are no IO errors and all processes call sqlite3_close() to close connections before exiting, SQLite always deletes the WAL file when the last connection disconnects, this is the usual case. Anyhow, that could be what is happening here. Two processes trying to run recovery simultaneously. To test, try opening the WAL database with the command line tool. Read some data (e.g. by querying the sqlite_master table) to make sure the db shared-memory region is initialized. Then run your experiment (leaving the command line client connected to the db). Since none of your threads should ever need to run recovery, the SQLITE_BUSY errors might disappear. Dan. --erik On Wed, Jan 25, 2012 at 5:39 PM, Richard Hippd...@sqlite.org wrote: On Wed, Jan 25, 2012 at 8:01 PM, Erik Fearsstr...@strtok.net wrote: I've got a WAL database with only readers right now. I'm opening and close the database at pretty rapid rate, and usually have about 100 connections open at a time. I'm using prepared statements for SELECTS and I'm pretty sure I'm finalizing the statements. After a while I start getting back database is locked. How can this happen with a WAL database? This is OS X. I didn't think it could, under the circumstances you describe. Are you sure your description of the scenerio is correct? Are you building SQLite yourself, our using the one that comes built-in to OSX? Is this all happening in a single process? Or lots of separately processes? If you have only readers, why both with WAL mode, single DELETE mode works just as well in that case? --erik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
