A valid backup needs to be a snapshot, It is of no value if other processes were able to modify the file as it was being backed up. An exclusive (write) lock of some type is required or else the file needs to be organized differently.
Neville Franks wrote: > A proper backup program that opened the file for r/o, non-exclusive > use may be able to copy the file. > > I thought SQLite has a live backup capability now. Surely that is the > best way to handle this. > > > Wednesday, July 8, 2009, 6:16:41 PM, you wrote: > > JS> It looks like you are trying to copy from a process other than the one > JS> which holds the lock. > > JS> Think abnout it - the lock gives exlusive access to the file to the > JS> process which sets it and blocks all other processes. > > JS> Stan Bielski wrote: > >>> Just to make sure I understood you correctly, is this what you are >>> suggesting? >>> >>> * Open file with sqlite app (in my case sqlite3) >>> * execute BEGIN EXCLUSIVE TRANSACTION; >>> * initiate the file copy >>> * COMMIT; after the copy is finished. >>> >>> I tried doing this, but very early into the copy Windows issues the >>> following error: >>> >>> Errror 0x80070021: The process cannot access the file because another >>> process has locked a portion of the file. >>> >>> >>> On Tue, Jul 7, 2009 at 1:03 PM, John Stanton<jo...@viacognis.com> wrote: >>> >>> >>>> You should synchronize your backup (copy). Try surrounding it with an >>>> exclusive transaction. >>>> >>>> Stan Bielski wrote: >>>> >>>> >>>>> Sorry for the repost, but the original thread was hijacked by another >>>>> list user. This is a serious problem IMHO; it looks like the DB can't >>>>> be backed-up without rendering the machine unusable if a query hits it >>>>> while a copy is in progress. >>>>> >>>>> Hello, >>>>> >>>>> In the course of copying a largish (20 GB) database file while >>>>> accessing it via sqlite3, the machine became very unresponsive. I >>>>> opened task manager and found that the system was using a huge amount >>>>> of virtual memory, causing it to thrash. Per-process memory usage >>>>> looked normal and did not add up to anywhere near system-wide VM >>>>> usage. >>>>> >>>>> I ran into this issue at a customer site and was able to reproduce it >>>>> using a local Windows 2008 installation. I have not installed any >>>>> backup software or a virus scanner. Storage is local disk, SQLite >>>>> version is 3.3.17. >>>>> >>>>> At first I thought that this was a general Windows problem involving a >>>>> process accessing a file that is being copied, but other binaries I >>>>> tested do not cause the same behavior that sqlite3 does. I performed >>>>> the following experiments to try to diagnose the issue. >>>>> >>>>> Case 1: >>>>> >>>>> * I copy a 20 GB sqlite DB using Windows' own copy utility (e.g. via >>>>> explorer). >>>>> * At any point during the copy, I open the file being copied in sqlite3 >>>>> * I exit sqlite3. >>>>> * During the rest of the copy the OS will consume virtual memory >>>>> linear (seemingly identical) to the amount of data copied since the >>>>> process opened the file. >>>>> >>>>> I repeated this experiment using a similarly-sized file created from >>>>> /dev/zero (i.e. an invalid DB) and the results were the same. >>>>> >>>>> Case 2: >>>>> >>>>> * I copy the sqlite DB using Windows' own copy utility (e.g. via >>>>> explorer). >>>>> * At any point during the copy, I run 'strings' with the file as an >>>>> argument. >>>>> * I exit strings. >>>>> * The copy does not result in the OS consuming additional virtual memory. >>>>> >>>>> Case 3: >>>>> >>>>> * I open the DB in sqlite3 >>>>> * I let sqlite3 idle and do not input any commands. >>>>> * I copy a sqlite DB using Windows' own copy utility (e.g. via explorer). >>>>> * I continue to let sqlite3 idle and do not input any commands. >>>>> * During the rest of the copy the OS will consume virtual memory >>>>> linear (seemingly identical) to the amount of data copied since the >>>>> process opened the file. >>>>> >>>>> Is there a workaround for this issue? Any assistance or info is >>>>> appreciated. >>>>> >>>>> Thanks, >>>>> -Stan >>>>> > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
