What happens if you pass the AutoCommit => 1 option to DBI's connect method?
"Jeff Nokes" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] >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 > >
