I think everyone has this requirement, I was finding many slowness problems using synchronous = on, and I started tweaking the app with memory tables, I recommend you look into it, you can create a memory Database by specifying :memory: as the filename.
The connection you receive from this you can attach to the file database, see the ATTACH command, then you can select data from one to the other, for speed do all things in the memory until you want to save at the critical point where your process must save the data or changes it made, then you can write all of the information from the memory table with a simple insert select from the memory table then clear memory and continue, this also helps as youre not locking out other threads/processes from the file db for to long. If you search the forum you can get lots of help on ATTACH and :memory: as you need, but then I'd leave my file DB to be as ACID as possible. -----Original Message----- From: Scott Gilbert [mailto:[EMAIL PROTECTED] Sent: 01 October 2007 11:01 PM To: [email protected] Subject: [sqlite] PRAGMA synchronous=0; and crash recovery A quick introduction: I'm a software developer in Tucson AZ, and I'm a new sqlite user (version 3.4.2). I'm really very impressed with this software. Thank you very much to everyone who has contributed to it! I have an application that requires separate processes (under Linux) to concurrently update records in some shared tables (multiple SELECTs and UPDATEs inside of a transaction). Unfortunately, when I have PRAGMA synchronous = 1 or 2, it is too slow for my needs, while synchronous = 0 is plenty fast enough. It is very likely that I *will* lose power when my application is running. When this happens, I can live with losing the last few minutes of transactions, but a corrupted database file that lost all data would cause much grief to my users (and therefore me). So my questions are: With synchronous = 0, are transactions still atomic across processes? Is running with synchronous=0 likely to non-recoverably corrupt the database file on power failure? Are there any recovery tools that can restore consistancy? I apologise if this has been answered somewhere else (an hour or two of googling didn't find anything that specifically covered synchronous = 0). Thank you in advance for any replies! Cheers, -Scott ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
