Thanks for replying Perrin, see my replies inline below. - Jeff ----- Original Message ---- From: Perrin Harkins <[EMAIL PROTECTED]> To: Jeff Nokes <[EMAIL PROTECTED]> Cc: modperl@perl.apache.org 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: >http://www.sqlite.org/lockingv3.html [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 happening. >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 shells 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.
