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
