On Thu, Mar 17, 2005 at 08:33:03PM -0700, Ara.T.Howard wrote: > On Sat, 12 Mar 2005, Andrew Piskorski wrote: > > >On Sat, Mar 12, 2005 at 10:03:25AM -0700, Ara.T.Howard wrote: > > > >>does anyone have a strategy for doing massive updates to a db and atomicly > >>replacing it in a multi-process situation? > > > >Why would you want to do that? SQLite properly supports transactions, so > >aren't you better of just using those? What scenario are you working with > >here? Do you have a large number of readers that MUST not block while a > >massive/slow update is done tot he database, or something like that? > > precisely - active web site doing both reads and writes: long running update > to perform.
If besides that one slow update you had ONLY READS (and those reads do not care which version of the data they get, old or new), I'd say sure, fine, an atomic "swap in a totally new db" operation might be worth trying. But for a database with active reads AND sometimes also writes, all while that big slow update is going on? No, even if your special procedural scheme below works, the whole idea seems misguided. If you really must have the possiblity of other writes while that huge slow update is happening, use an RDBMS which is actually designed to support that scenario, like PostgreSQL. SQLite is the wrong tool. You proposed procedure below is an ugly hack. I do not know the particulars of your situation, so maybe for you it really is justified over the correct "use PostgreSQL" alternative. But I would think long and hard about that. (And no, I don't know what the best/simplest way to do an "atomically replace whole db" step is with SQLite. That would be useful to know, regardless.) > obviously not too much (but maybe something) will be happening at night. my > plan was > > lock db > take copy > note mtime > release lock > perform updates > lock db > if mtime has been updated > retry > else > perform atomic replacement > end > > perhaps this is simply impossible with sqlite? -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
