On Sunday 08 August 2004 6:52 pm, Scott Leighton wrote:
> On Sunday 08 August 2004 6:26 pm, D. Richard Hipp wrote:
> > Scott Leighton wrote:
> >
> > Whenever you start a SELECT statement (by calling sqlite3_step()) but
> > have not finalized that statement (using sqlite3_finalize() or
> > sqlite3_reset()) the statement is probably holding a lock on the table(s)
> > that are being queried. Other SELECT statements can read those tables,
> > but no other statements can INSERT, UPDATE or DELETE those tables until
> > after all the SELECTs have been finalized.
>
> Once again, thank-you! That makes complete sense and explains
> exactly what I am seeing. You are a lifesaver, I was really struggling with
> this, now I have a lead to solve it!
>
Solved! Just to close the loop on this for the benefit of any other
DBD::SQLite users who may be trying to upgrade from v 0.31 to v 1.x.x.
Watch out for cases where your former working code left unfinished SELECT
statements since under SQLite v 3.x.x they will hold locks on the database
causing subsequent SQL calls to fail.
In my case, the code had several instances where we knew there could only
be one result to the query so we basically grabbed that result and moved on,
e.g.,
$sth = $dbh->prepare($statement);
$sth->execute;
return $sth->fetchrow_arrayref;
This left the SELECT statement in a not finalized state and the dataset
locked. Worked fine under SQLite 2.8.xx but it fails under 3.x.x. We had to
change the code to,
$sth = $dbh->prepare($statement);
$sth->execute;
my $result= $sth->fetchrow_arrayref;
$sth->finish();
return $result;
to solve the problem by finalizing the SELECT so the lock came off the
database.
Hope that helps someone else from banging their head against the wall
searching for the problem <g>. Again, a big thanks to D. Richard Hipp for
pointing me in the right direction!
Scott
--
POPFile, the OpenSource EMail Classifier
http://popfile.sourceforge.net/
Linux 2.6.5-7.104-default x86_64
