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 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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
