SQLite and multiple process behavior

[Jeff Nokes <je...@yahoo.com>]

I sent this to the SQLite mailing list on June 4th, but never received any replies.
I figure either I'm making a totally rookie mistake somewhere, or folks really don't
know how to help me with this.  I'm hoping someone may have some insight to offer
on this forum.  As always, I appreciate any assistance one can offer.
Thanks,
- Jeff

PS:  For my SQLite instance:

   PRAGMA synchronous = 2, which means that SQLite is configured in the most
   conservative manner to force writes to disk whenever possible.  It uses the
   fsync() call in Linux.

   http://www.sqlite.org/pragma.html


I've tried the below examples in both autocommit mode, and as explicit transactions.
No change in behavior.


***********************************************************************************

Hi,
I'm a little new to SQLite, but have been using it successfully for about 8 months,
as a read only repository for application configuration parameters in my mod_perl
based web application.  I have recently added functionality to update the
configuration tables I have, via the web interface.  Here is my stack:

RedHat Linux 7.2
apache 1.3.x, prefork mode
mod_perl 1.29
DBI  1.51
DBD::SQLite  1.13

Single database file, with 3 simple configuration tables in name => value format.
Mulitple apache processes each with their own connection, sharing that single file.
No threading.

In my unit testing of this, I tried updating the the table values from multiple
approaches, and found that I was seeing strange behavior.

Approaches:

(1)  Updated a single row, in one table, in a copy of the DB file on a build host,
using the `sqlite3` command line tool.   I then uploaded that modified DB file to the
application host which was already running apache, and all of my reads from the table
still reflected the "old" row value as if it had never changed.

(2)  Updated a single row, in one table, in the existing DB file on the application
host, using the `sqlite3` command line tool, while apache still running, and still,
the web application kept reading the "old" row value.

In either of the two former scenarios, if I stopped/started apache again, the web
application would then see the correct value in the configuration table, from all
child processes.

(3)  Updated a single row, in one table, in the existing DB file on the application
host, using the web interface.  This time, all apache child processes saw the new
value immediately; each process has it's own DB connection that is not shared.

So, I thought that the issue with approaches 1 & 2 above could be due to memory
caching/paging by SQLite or Linux, especially since I was modifying the DB file while
apache had current handles connected to it from other processes (i.e. apache).  But
when you think about it, the prefork model of apache is the same thing, with each
child process being a separate process that has a handle with the DB file.

My question has multiple parts:

(Q1)  First, why would what seems to be identical multi-process interaction with the
DB file achieve different results when attempting to view the updated record?

(Q2)  I have a business need to perform both small, infrequent updates, and larger
more frequent updates to this DB file.  I am hoping to be able to not only perform
these updates through the web interface of the application, but also to be able to
just deploy a new DB file to all our production hosts, without bouncing apache, and
have the updates take effect either way.  How can I achieve this with the behavior I
have mentioned above?

Thanks in advance for any help you can offer,
- Jeff

Re: SQLite and multiple process behavior

[Nikolay Ananiev <an...@thegdb.com>]

What happens if you pass the AutoCommit => 1 option to DBI's connect method?


"Jeff Nokes" <je...@yahoo.com> wrote in message 
news:347164.45909.qm@web62408.mail.re1.yahoo.com...
>I sent this to the SQLite mailing list on June 4th, but never received any 
>replies.
> I figure either I'm making a totally rookie mistake somewhere, or folks 
> really don't
> know how to help me with this.  I'm hoping someone may have some insight 
> to offer
> on this forum.  As always, I appreciate any assistance one can offer.
> Thanks,
> - Jeff
>
> PS:  For my SQLite instance:
>
>   PRAGMA synchronous = 2, which means that SQLite is configured in the 
> most
>   conservative manner to force writes to disk whenever possible.  It uses 
> the
>   fsync() call in Linux.
>
>   http://www.sqlite.org/pragma.html
>
>
> I've tried the below examples in both autocommit mode, and as explicit 
> transactions.
> No change in behavior.
>
>
> ***********************************************************************************
>
> Hi,
> I'm a little new to SQLite, but have been using it successfully for about 
> 8 months,
> as a read only repository for application configuration parameters in my 
> mod_perl
> based web application.  I have recently added functionality to update the
> configuration tables I have, via the web interface.  Here is my stack:
>
> RedHat Linux 7.2
> apache 1.3.x, prefork mode
> mod_perl 1.29
> DBI  1.51
> DBD::SQLite  1.13
>
> Single database file, with 3 simple configuration tables in name => value 
> format.
> Mulitple apache processes each with their own connection, sharing that 
> single file.
> No threading.
>
> In my unit testing of this, I tried updating the the table values from 
> multiple
> approaches, and found that I was seeing strange behavior.
>
> Approaches:
>
> (1)  Updated a single row, in one table, in a copy of the DB file on a 
> build host,
> using the `sqlite3` command line tool.   I then uploaded that modified DB 
> file to the
> application host which was already running apache, and all of my reads 
> from the table
> still reflected the "old" row value as if it had never changed.
>
> (2)  Updated a single row, in one table, in the existing DB file on the 
> application
> host, using the `sqlite3` command line tool, while apache still running, 
> and still,
> the web application kept reading the "old" row value.
>
> In either of the two former scenarios, if I stopped/started apache again, 
> the web
> application would then see the correct value in the configuration table, 
> from all
> child processes.
>
> (3)  Updated a single row, in one table, in the existing DB file on the 
> application
> host, using the web interface.  This time, all apache child processes saw 
> the new
> value immediately; each process has it's own DB connection that is not 
> shared.
>
> So, I thought that the issue with approaches 1 & 2 above could be due to 
> memory
> caching/paging by SQLite or Linux, especially since I was modifying the DB 
> file while
> apache had current handles connected to it from other processes (i.e. 
> apache).  But
> when you think about it, the prefork model of apache is the same thing, 
> with each
> child process being a separate process that has a handle with the DB file.
>
> My question has multiple parts:
>
> (Q1)  First, why would what seems to be identical multi-process 
> interaction with the
> DB file achieve different results when attempting to view the updated 
> record?
>
> (Q2)  I have a business need to perform both small, infrequent updates, 
> and larger
> more frequent updates to this DB file.  I am hoping to be able to not only 
> perform
> these updates through the web interface of the application, but also to be 
> able to
> just deploy a new DB file to all our production hosts, without bouncing 
> apache, and
> have the updates take effect either way.  How can I achieve this with the 
> behavior I
> have mentioned above?
>
> Thanks in advance for any help you can offer,
> - Jeff
>
> 

Re: SQLite and multiple process behavior

[Perrin Harkins <pe...@elem.com>]

On 6/15/07, Jeff Nokes <je...@yahoo.com> wrote:
> (Q1)  First, why would what seems to be identical multi-process interaction with the
> DB file achieve different results when attempting to view the updated record?

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:
http://www.sqlite.org/lockingv3.html

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.

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

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.

> I am hoping to be able to not only perform
> these updates through the web interface of the application, but also to be able to
> just deploy a new DB file to all our production hosts, without bouncing apache, and
> have the updates take effect either way.

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

- Perrin

Re: Access apache log variables, eg %h in PerlLogHandler

[Geoffrey Young <ge...@modperlcookbook.org>]

Brad wrote:
> Hi, thanks for the reply Geoff.  I'm actually trying to access the %O
> bytes sent that logio.c places amongst the other variables within the
> logging module.  Thats something that I can't find elsewhere.  Sorry
> about the %h %i example, i thought %O would over complicate the
> question :)

mod_perl generally uses $r->bytes_sent() for this kind of thing

http://perl.apache.org/docs/2.0/api/Apache2/RequestRec.html#C_bytes_sent_

but note the caveat in the docs - it's unlikely bytes_sent() will match
mod_logio's results.  it's a shame mod_logio doesn't set its result in
subprocess_env, which would make accessing it's results a simple matter.

the cool thing, though, is that mod_logio itself has an api, registering
optional functions that you can call.  is, if you _really_ wanted
mod_logio's results you could use some simple xs to create a wrapper and
get at them :)

HTH

--Geoff

Re: Access apache log variables, eg %h in PerlLogHandler

[Brad <br...@intradonline.com>]

Hi, thanks for the reply Geoff.  I'm actually trying to access the %O
bytes sent that logio.c places amongst the other variables within the
logging module.  Thats something that I can't find elsewhere.  Sorry
about the %h %i example, i thought %O would over complicate the
question :)

Thanks,
Brad.

On Mon, 2007-06-18 at 12:43 -0400, Geoffrey Young wrote:
> 
> Brad wrote:
> > Is there any way I can read variables from apache's log system back into
> > a PerlLogHandler?  %h %l etc
> 
> you might as well forget about %l - nobody runs with identd.  as for %h
> you can use $r->c->get_remote_host()
> 
> http://perl.apache.org/docs/2.0/api/Apache2/Connection.html#C_get_remote_host_
> 
> mod_log_config merely uses the apache api to spit those bits out to the
> log file.  so browse the Apache2::RequestRec, ::ServerRec, ::Conection,
> etc and you'll have whatever info you need.
> 
>   http://perl.apache.org/docs/2.0/api/
> 
> HTH
> 
> --Geoff

Re: Access apache log variables, eg %h in PerlLogHandler

[Geoffrey Young <ge...@modperlcookbook.org>]

Brad wrote:
> Is there any way I can read variables from apache's log system back into
> a PerlLogHandler?  %h %l etc

you might as well forget about %l - nobody runs with identd.  as for %h
you can use $r->c->get_remote_host()

http://perl.apache.org/docs/2.0/api/Apache2/Connection.html#C_get_remote_host_

mod_log_config merely uses the apache api to spit those bits out to the
log file.  so browse the Apache2::RequestRec, ::ServerRec, ::Conection,
etc and you'll have whatever info you need.

  http://perl.apache.org/docs/2.0/api/

HTH

--Geoff

Access apache log variables, eg %h in PerlLogHandler

[Brad <br...@intradonline.com>]

Is there any way I can read variables from apache's log system back into
a PerlLogHandler?  %h %l etc