perl-modperl mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeff Nokes <>
Subject Re: SQLite and multiple process behavior
Date Mon, 18 Jun 2007 18:54:01 GMT
Thanks for replying Perrin, see my replies inline below.
- Jeff

----- Original Message ----
From: Perrin Harkins <>
To: Jeff Nokes <>
Sent: Friday, June 15, 2007 7:26:09 PM
Subject: Re: SQLite and multiple process behavior

>I see two possibilities.  One is that you have a SELECT running in one
>process which blocks the UPDATE from the other process, or some
>similar kind of isolation level issue.  There's some information on
>that here:

[Jeff]  When I'm testing against my dev server, I'm the only one playing in that sandbox,

so I'm just doing single requests, there is definitely no locking or concurrency issues


>Another is that your code has a scoping problem and the values you're
>looking at are not actually being read from the database each time.
>There are a number of ways you can analyze this.  You can try these
>same experiments with no web application at all, using multiple shells
>running the sqlite3 tool.  If you still see the problem, then it most
>likely involves isolation levels or locks.

[Jeff]  So I tried this again for an extended period of time, and found the following result:
When I had multiple shells open, and my web app all connected to the same SQLite db file,
I saw the same behavior as before, where the shells would show something different than the
web app.  But, after some amount of time, and some number of writes to the db, all of the
and the web app were in-sync.  So, it looks as though if I wait long enough, all processes
on the
same dev box would be in-sync, with the web app, and all is well.

>You can turn on DBI_TRACE to see if your web application is really
>running the queries you think it is.

[Jeff]  I enabled DBI->trace(2), and can definitely see everything executing every time.

>You can whittle down your code to a small example that demonstrates
>the problem, and post it here for us.  If it has a scoping issue,
>someone would probably spot it.

[Jeff]  I'm doing very vanilla stuff; I doubt example code would show anything.

>I suspect you'd need to reconnect your DBI handles for this to work,
>but I don't really know.

[Jeff]  Yes, I agree, this is probably just a bad ideal all around, I'm no longer going to
try to support his approach.
I tried this experiment again, and updated the db file, copied over to the dev host, and then
tried to look for my
updates in both my open shells and the web app.  The shells and the web app still saw the
"old" version, yet
when I opened a new shell and instance of sqlite3, it saw the "new" db.  I then continued
to do updates and selects
through all interfaces, and still, the processes that were open with the "old" db file originally,
still only saw what
was in the old table.  And the new shell only saw the "new" table.  It's almost like the OS
is keeping two distinct
versions of this file, when there is only one on the drive.

I think going forward here, I'm going to stick with just doing all updates to the DB file
on all hosts, via the web interface,
so I don't have to bounce apache.  At least I know when I do everything from the web interface,
it seems to work
great across all apache children.  I'll just have to keep my source-controlled db file in
sync with the same updates as
the production files.

View raw message