After changing my script to make use of querysingle instead of the
query/fetchArray loop I was still seeing database locked notices. My
scripts would end unexpectedly if the database lock occured at the right
moment. Finally found the root cause of the trouble was due to not defining
a busyTimeout().
$smallDb->busyTimeout(60000);
function getTraceroute($smallDb) {
while($entry = $smallDb->querysingle('SELECT * FROM tworklist limit
1', true)) {
$query3 = "delete from tworklist where rowid=$rowid";
echo "result of delete = " . $smallDb->exec($query3) . "\n";
$query2 = "insert into tresults (rowid,ip,traceroute) values
('$rowid', '$ip','$new')";
if ($smallDb->exec($query2)) {
echo "result of update = success. \n";
}
else echo "results of update = fail. \n";
}
}
I have run 10 processes at once for many hours and there was occurrence of
the database locked message. There may be better ways to code this, but it
seems robust enough for my needs now. It took a bit too much searching to
realize this was what I needed. It is not clearly documented in the sqlite3
documents either - http://www.php.net/manual/en/book.sqlite3.php.
On Mon, Aug 23, 2010 at 8:04 PM, Chad Rebuck <chadreb...@gmail.com> wrote:
> 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
