Mailing-List: contact modperl-help@perl.apache.org; run by ezmlm
Precedence: bulk
Received-SPF: pass (herse.apache.org: domain of gcam-modperl@m.gmane.org
 designates 80.91.229.2 as permitted sender)
To: modperl@perl.apache.org
From: "Nikolay Ananiev" <ananiev@thegdb.com>
Subject: Re: SQLite and multiple process behavior
Date: Mon, 18 Jun 2007 10:27:56 +0300
Lines: 121
Message-ID: <f55c69$t6m$1@sea.gmane.org>
References: <347164.45909.qm@web62408.mail.re1.yahoo.com>
Sender: news <news@sea.gmane.org>

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


"Jeff Nokes" <jeff_nokes@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
>
>