Simon, Roger,
Thanks for the replies.
Simon, I took some of your advice and redesigned my script to use smaller
database files at least for my testing. However, I still run into this
message after starting the second process and my two processes get stuck so
no inserts/deletes occur after that moment. I'll also google "wal" as you
suggest.
PHP Warning: SQLite3::exec(): database is locked in
...populate-traceroute2.php on line 53. Line 53 below contains
"$smallDb->exec($query2)".
Roger, I read the page you provided, but I think I need to read some more
before fully understanding what I've done wrong. I didn't mention it until
now, but with my sqlite 2 database and an older version of php I didn't see
this issue last week. I was working with smaller files then but now even
with small files I have the locking issue.
I would appreciate if anyone can guide me a little more on how to correct
this. Here are the relevant code snippets. There are probably more
efficient ways to handle this but I am fairly new to php programming and
only sql, so excuse the mess :)
function getTraceroute($smallDb) {
while($Results = $smallDb->query('SELECT * FROM tworklist limit 1'))
{
$entry = $Results->fetchArray(SQLITE3_ASSOC);
if (!($entry)) { break; }
$query2 = "delete from tworklist where rowid=$rowid";
echo "result of delete = " . $smallDb->exec($query2) . "\n";
$query2 = "insert into tresults (rowid,ip,traceroute) values
('$rowid', '$ip','$new')";
echo "result of update = " . $smallDb->exec($query2) . "\n";
}
}
On Mon, Aug 23, 2010 at 4:47 PM, Roger Binns <rog...@rogerbinns.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 08/23/2010 08:21 PM, Chad Rebuck wrote:
> > I am new to databases so perhaps there is something very basic I am
> > overlooking here.
>
> You may inadvertently be causing the busy errors yourself. Have you
> read this:
>
> http://www.sqlite.org/lockingv3.html
>
> A simple example is if you have a select query in process 1 from which
> you read one result at a time, process and continue with the query:
>
> for row in ("select * from db"):
> ... do work with row ...
>
> This will block writes in the second process since the select is always
> live. Workarounds include using WAL mode, gathering all results from a
> select before processing them, and using one thread/process to do the
> database work dispatching work items to child threads/processes.
>
> Roger
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkxy3lIACgkQmOOfHg372QTTyACgx4qhZSQ5bMjREOYuZBlOG6fM
> UbsAnRZDEYZMRcrKUrDiHpPRKs4mhx10
> =Af1h
> -----END PGP SIGNATURE-----
> _______________________________________________
> 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
