Re: [sqlite] WAL, synchronous=1 and durability
Hi Dan In both NORMAL mode, we do the following: 1. Write a bunch of transactions into the WAL file. 2. fsync() the WAL file. 3. Copy the data from the WAL file into the database file. 4. fsync() the database file. If a power failure occurs at any time, the next process to read the database scans the WAL file and attempts to read as many transactions as possible. If the checksum fails at any point, it stops reading. So you can lose data. Say a power failure occurs between steps 1 and 2 above. If your WAL had 5 unsynced transactions in it then following recovery you may find that none, some or all of them have survived, depending on how much of the WAL file actually made it to disk before the power failed. Thank you very much for the explanation, it is very clear! I have one last question. In my ext3 file system, I have set barrier=1, disables write cache, and also set commit=1 for per second disk sync from the kernel. In your opinion, if I used NORMAL, would it mean: (1) The most number of transactions I can lose in the WAL file is 1 second's worth? (2) If the WAL contained say 10 seconds worth of transactions (1 transaction per second) before the power failure, and the 11th second the transaction failed to make it, ie stuck between your step 1 and 2, will the first 10 seconds worth of transactions still make it? I presume yes, since you have checksums in the WAL file? (3) If there is a power failure anywhere between steps 2, 3, and 4, I presume that will be OK, since the recovery will be done on next startup. If the answer is true for the above 3 questions, then I think WAL with NORMAL is an extremely attractive option to use. Regards Keith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, synchronous=1 and durability
On 09/11/2012 01:21 PM, Keith Chew wrote: Hi Dan In both NORMAL mode, we do the following: 1. Write a bunch of transactions into the WAL file. 2. fsync() the WAL file. 3. Copy the data from the WAL file into the database file. 4. fsync() the database file. If a power failure occurs at any time, the next process to read the database scans the WAL file and attempts to read as many transactions as possible. If the checksum fails at any point, it stops reading. So you can lose data. Say a power failure occurs between steps 1 and 2 above. If your WAL had 5 unsynced transactions in it then following recovery you may find that none, some or all of them have survived, depending on how much of the WAL file actually made it to disk before the power failed. Thank you very much for the explanation, it is very clear! I have one last question. In my ext3 file system, I have set barrier=1, disables write cache, and also set commit=1 for per second disk sync from the kernel. In your opinion, if I used NORMAL, would it mean: (1) The most number of transactions I can lose in the WAL file is 1 second's worth? (2) If the WAL contained say 10 seconds worth of transactions (1 transaction per second) before the power failure, and the 11th second the transaction failed to make it, ie stuck between your step 1 and 2, will the first 10 seconds worth of transactions still make it? I presume yes, since you have checksums in the WAL file? That sounds right to me. The first 10 seconds worth of transactions and their checksums will have made it to disk so SQLite will be able to recover them the next time the db is opened. (3) If there is a power failure anywhere between steps 2, 3, and 4, I presume that will be OK, since the recovery will be done on next startup. Right. Once step 2 has taken place your transactions have been synced to disk. So even if a power failure occurs before they are copied into the db file, they can't get lost. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, synchronous=1 and durability
On 11 Sep 2012, at 7:21am, Keith Chew keith.c...@gmail.com wrote: In my ext3 file system, I have set barrier=1, disables write cache, and also set commit=1 for per second disk sync from the kernel. In your opinion, if I used NORMAL, would it mean: (1) The most number of transactions I can lose in the WAL file is 1 second's worth? (2) If the WAL contained say 10 seconds worth of transactions (1 transaction per second) before the power failure, and the 11th second the transaction failed to make it, ie stuck between your step 1 and 2, will the first 10 seconds worth of transactions still make it? I presume yes, since you have checksums in the WAL file? (3) If there is a power failure anywhere between steps 2, 3, and 4, I presume that will be OK, since the recovery will be done on next startup. If the answer is true for the above 3 questions, then I think WAL with NORMAL is an extremely attractive option to use. I'm posting this only because your questions suggest you need to know the actual way your system will perform in real life. Dan has answered this in terms of how SQLite3 works, but I wanted to stress that SQLite3 isn't the only link in the chain. SQLite3 working correctly depends on the SQLite3 calls writing directly to the disk, with the disk physically part of the computer that's executing the SQLite3 calls. If you're mounting the disk using NFS or any other network FS, the answers are different. Very few network setups correctly support fsync(). Also, your hard disk must support ACID, and must be in ACID support mode (probably jumper settings) which it probably isn't because that's almost never done except for hard disks used for servers. It's more likely to be set up to do out-of-order saves, and say That's saved. first but actually save things after the call has returned. A good test is running an interactive program like Word or GIMP on that computer, or a 3D game. If those apps run acceptably fast, then your hard disk isn't doing ACID. This is the correct way to set up a non-server computer if you want it to feel nice and fast in use. I have, as a demonstration, bought components and set up a computer in ACID mode, but then loaded mundane Windows XT and Office on it. It was unusable, with Office pausing every 50 or so keystrokes for over a second so it could write temporary savefiles. Even messing about with Windows Explorer, opening folders and moving and copying files was annoyingly slow. Booted instead into a version of RedHat (I forget which version) every time Linux tried to add a line to a log file (which Unix does constantly) the GUI would freeze for a quarter second. Not what a geek would want from his or her home computer. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, synchronous=1 and durability
I fully agree - my tests (involving many hard resets) confirm this. If I could just expound on some of your statements for clarity, for others. This is my understanding gained from testing and reading the posts -- I did not see the code. In both synchronous=NORMAL and synchronous=FULL the *-wal file may contain more than one transaction. Because data is only written to the database file at checkpoints which can be done manually or automatically by SQLite (current default set to 1000 pages). So the -wal file will always contain up to 1000 pages (the default) before getting flushed. The difference between these 2 is that with NORMAL the writes are not guaranteed, whereas with FULL the writes are guaranteed. The WAL file includes a running checksum so that if a power failure occurs, the next client to read the database can determine the prefix of the WAL file that can be considered trustworthy (as the power failure may have corrupted more recently written parts of the file). To clarify if it is not obvious: this recover happens for OFF, NORMAL or FULL when the DB is openned. In both NORMAL mode, we do the following: 1. Write a bunch of transactions into the WAL file. In FULL mode, the above is sync'ed, although I don't know whether individual writes to the WAL file are sync'ed, or when all the data belonging to the commit is written the WAL is sync'ed. In NORMAL mode this is not done and that is why it is much faster (at least 15 times faster in my basic tests). The steps below are part of a checkpoint that apply to NORMAL and FULL. This is when the WAL file reached it's page limit, or called manually by the user. This step might be redundant for FULL mode, but that's not important. 2. fsync() the WAL file. 3. Copy the data from the WAL file into the database file. 4. fsync() the database file. Synchronous=FULL mode is different. During step 1 above in FULL mode, SQLite calls fsync() on the WAL file after writing each transaction to it - before the users COMMIT command returns. Since someone else kindly pointed out that COMMIT can require several writes -- is each and every write required for a COMMIT synchronised to prevent corruption? Or does SQLite utilise the same idea as with NORMAL mode using checksums to prevent corruption? Perhaps that is why WAL+FULL is faster than non-WAL+FULL? The more sequential writes in WAL mode (as explained in the manual) must help here. If something like checksums is used to prevent corruption, maybe that also can be applied to non-WAL journal modes, as an option to speed up their NORMAL mode? @Simon Slavin Though if your OS and hardware does perform as documented then neither mode will actually lose data or cause database corruption. This doesn't make sense; OS's do I/O caching, data corruption is always a possibility then. FULL) Every COMMIT will take more time, but a power-failure can lose a maximum of one transaction. Someone already corrected this; power-failure will result in no loss of transaction, not even one. I think I know why you said that. In non-WAL and NORMAL, there are intermediate writes related to 1 commit, Also, as for network setups, docs on the WAL clearly state that the DB should not be used over a network. Other than that, I enjoyed the information about hard drives (not) supporting ACID. Good to know. @Keith Chew (original poster) I am using EXT3 with barrier=1 and write-cache disabled from the HDD... I believe there will be no difference in NORMAL or FULL modes, when measuring performance, durability or consistency (data corruption). SQLite will be probably be making pointless calls to sync the I/O buffers. The steps above (1-4) show the difference between NORMAL and FULL: in FULL every write to the WAL file is sync'ed, the rest is the same. So in your case of hardware cache being turned off, every write to the WAL file will be physical anyway. Well this excessive clearing up is giving me a callus :-) In my WAL tests with NORMAL mode and caching enabled, the longer you waited (we are talking up to ten seconds), the less data was lost after a hard reboot, so by no means all data is lost, some will make it to the file, and thanks to the checksums (as pointed out by Dan) the data will be recovered to the best of SQLite's ability. Well these are my 10 pence. On Tue, Sep 11, 2012 at 5:52 AM, Dan Kennedy danielk1...@gmail.com wrote: On 09/11/2012 11:12 AM, Keith Chew wrote: Hi Jay In WAL mode that's only half incorrect. Your description of NORMAL seems correct, but FULL should be fully durable. The WAL file may need to be checkpointed on startup, but the if the commit happens in WAL/FULL, you should have full durability. This is the reason for my question. As far as I can see, the difference between NORMAL and FULL in WAL mode is that FULL has one transaction in WAL to be checkpointed, and NORMAL has multiple. Since both of them need to checkpoint for durability, how is it that FULL
Re: [sqlite] WAL, synchronous=1 and durability
On 09/11/2012 11:03 PM, Daniel Frimerman wrote: In FULL mode, the above is sync'ed, although I don't know whether individual writes to the WAL file are sync'ed, or when all the data belonging to the commit is written the WAL is sync'ed. In NORMAL mode this is not done and that is why it is much faster (at least 15 times faster in my basic tests). The steps below are part of a checkpoint that apply to NORMAL and FULL. This is when the WAL file reached it's page limit, or called manually by the user. This step might be redundant for FULL mode, but that's not important. 2. fsync() the WAL file. 3. Copy the data from the WAL file into the database file. 4. fsync() the database file. Synchronous=FULL mode is different. During step 1 above in FULL mode, SQLite calls fsync() on the WAL file after writing each transaction to it - before the users COMMIT command returns. Since someone else kindly pointed out that COMMIT can require several writes -- is each and every write required for a COMMIT synchronised to prevent corruption? Or does SQLite utilise the same idea as with NORMAL mode using checksums to prevent corruption? Perhaps that is why WAL+FULL is faster than non-WAL+FULL? The more sequential writes in WAL mode (as explained in the manual) must help here. If something like checksums is used to prevent corruption, maybe that also can be applied to non-WAL journal modes, as an option to speed up their NORMAL mode? One fsync() per transaction written to the WAL file. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WAL, synchronous=1 and durability
Hi Using journal_mode=WAL and synchronous=2, it is my understanding (from the docs and my own power loss testing) that sqlite will maintain durability on a power loss. My question is, if sqlite can guarantee durability with synchronous=2, why can't it (from reading the docs and other posts) do that with synchronous=1? They both perform the checkpoint routine, except synchronous=1 does the checkpoints with multiple transactions in the WAL file. Regards Keith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, synchronous=1 and durability
On 10 Sep 2012, at 9:00pm, Keith Chew keith.c...@gmail.com wrote: Using journal_mode=WAL and synchronous=2, it is my understanding (from the docs and my own power loss testing) that sqlite will maintain durability on a power loss. My question is, if sqlite can guarantee durability with synchronous=2, why can't it (from reading the docs and other posts) do that with synchronous=1? Probably worth us knowing whether you've read the 'Performance Considerations' section of http://www.sqlite.org/wal.html before anyone gives an in-depth answer. The basic difference between the two settings is that FULL (2) synchronises far more frequently than NORMAL (1), meaning that in the even of power loss, fewer transactions will be lost. Though if your OS and hardware does perform as documented then neither mode will actually lose data or cause database corruption. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, synchronous=1 and durability
Hi Simon Thank you for your reply. Probably worth us knowing whether you've read the 'Performance Considerations' section of http://www.sqlite.org/wal.html Yes, I have read that, along with all the threads/posts I could find from the Internet. The thing that I cannot fully understand is how can FULL (2) guarantee durability, but NORMAL (1) cannot. Before making an attempt to look into the code, I just wanted to see if anyone can help provide some explanation. before anyone gives an in-depth answer. The basic difference between the two settings is that FULL (2) synchronises far more frequently than NORMAL (1), meaning that in the even of power loss, fewer transactions will be lost. Though if your OS and hardware does perform as documented then neither mode will actually lose data or cause database corruption. Durability means a transaction cannot be lost, and I know FULL (1) will provide that. The question is why NORMAL (1) cannot provide the same. I am using EXT3 with barrier=1 and write-cache disabled from the HDD, as far as I know this is as good as it gets for making the system reliable. So, in this case, will the NORMAL (1) actually cause data loss on power loss? I already know that corruption will not happen, I am just interested in the losing transactions or sacrifice durability as suggested in various threads/posts I have read. Regards Keith Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, synchronous=1 and durability
On 11 Sep 2012, at 12:55am, Keith Chew keith.c...@gmail.com wrote: Durability means a transaction cannot be lost When is a transaction a transaction. Under the definition of ACID a transaction is a transaction as soon is your COMMIT returns with a non-error result code. , and I know FULL (1) will provide that. The question is why NORMAL (1) cannot provide the same. Because NORMAL doesn't flush changes to disk after every single transaction. It queues a few of them up and flushes them all in one go. This makes things far faster because it's the flushing to disk which takes a lot of time. So your payoff equation is this: NORMAL) Faster but if you lose power more transactions are lost, but the database is still not corrupt. FULL) Every COMMIT will take more time, but a power-failure can lose a maximum of one transaction. Just to remind you, all this depends on your hardware supporting ACID. Which almost no hard disks do. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, synchronous=1 and durability
On Tue, Sep 11, 2012 at 01:58:23AM +0100, Simon Slavin scratched on the wall: On 11 Sep 2012, at 12:55am, Keith Chew keith.c...@gmail.com wrote: and I know FULL (1) will provide that. The question is why NORMAL (1) cannot provide the same. Because NORMAL doesn't flush changes to disk after every single transaction. It queues a few of them up and flushes them all in one go. That's not quite true. Committing a transaction takes more than one disk write. As I understand it, in FULL mode, the disk is sync'ed after each and every write. In NORMAL mode, all of the writes required to commit a transaction are made in quick succession, but the disk is only sync'ed after the last write. In most practical situations, my guess is that the sync takes longer than the writes (since the writes are mostly to the OS file-system buffers anyways). That means, for a very, very short time during the final commit process (microseconds, most likely), there are pending buffered writes. If the system were to lose power between one of these writes and the final sync, there is a very-small-but-non-zero chance the database could become corrupt. But the disk is still fully sync'ed, to the best of SQLite's ability, after each and every commit. In NORMAL mode, commits are still fully durable. NORMAL) Faster but if you lose power more transactions are lost, but the database is still not corrupt. FULL) Every COMMIT will take more time, but a power-failure can lose a maximum of one transaction. That's also incorrect. In both cases, if a transaction fully commits, you're golden. Transactions are fully durable. If COMMIT returned success, you should not lose the transaction. FULL mode, to the best of the SQLite developers' ability, protects against all corruption, but at a very high performance cost. NORMAL mode opens up an extremely small chance of corruption for a significant performance increase. The PRAGMA docs explain much of this: http://www.sqlite.org/pragma.html#pragma_synchronous When synchronous is FULL (2), the SQLite database engine will use the xSync method of the VFS to ensure that all content is safely written to the disk surface prior to continuing. This ensures that an operating system crash or power failure will not corrupt the database. FULL synchronous is very safe, but it is also slower. When synchronous is NORMAL (1), the SQLite database engine will still sync at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in NORMAL mode. But in practice, you are more likely to suffer a catastrophic disk failure or some other unrecoverable hardware fault. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, synchronous=1 and durability
Hi Jay http://www.sqlite.org/pragma.html#pragma_synchronous When synchronous is FULL (2), the SQLite database engine will use the xSync method of the VFS to ensure that all content is safely written to the disk surface prior to continuing. This ensures that an operating system crash or power failure will not corrupt the database. FULL synchronous is very safe, but it is also slower. When synchronous is NORMAL (1), the SQLite database engine will still sync at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in NORMAL mode. But in practice, you are more likely to suffer a catastrophic disk failure or some other unrecoverable hardware fault. From what I have read so far, my understanding is consistent with your explanation (except that I didn't realise corruption can happen in NORMAL, only lost of data). Regardless, I would really like to hear from a developer that the above paragraph also applies to the WAL journal mode, and not just the older journal modes, since WAL was introduced later in 3.7 onwards. Because of the architecture change in WAL, I was hoping that the durability can still be preserved while using NORMAL. Regards Keith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, synchronous=1 and durability
On Tue, Sep 11, 2012 at 03:11:57PM +1200, Keith Chew scratched on the wall: Hi Jay http://www.sqlite.org/pragma.html#pragma_synchronous When synchronous is FULL (2), the SQLite database engine will use the xSync method of the VFS to ensure that all content is safely written to the disk surface prior to continuing. This ensures that an operating system crash or power failure will not corrupt the database. FULL synchronous is very safe, but it is also slower. When synchronous is NORMAL (1), the SQLite database engine will still sync at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in NORMAL mode. But in practice, you are more likely to suffer a catastrophic disk failure or some other unrecoverable hardware fault. From what I have read so far, my understanding is consistent with your explanation (except that I didn't realise corruption can happen in NORMAL, only lost of data). In the case of both NORMAL and FULL (in the case of non-WAL mode), if the commit is successful, there should never be data loss and the changes described by the transaction should become part of the database state. If the commit did not finish, the changes described in the transaction were never part of the database state to start with and are not considered lost. In no case should NORMAL or FULL allow a commit to succeed, but not have the transaction be durable. ...except if the disks lie. Which, as Simon has pointed out, is most of the time. Regardless, I would really like to hear from a developer that the above paragraph also applies to the WAL journal mode, and not just the older journal modes, since WAL was introduced later in 3.7 onwards. It does not. The transaction model in WAL mode is totally different, so the journal modes are as well. Because of the architecture change in WAL, I was hoping that the durability can still be preserved while using NORMAL. WAL plays by a slightly different set of rules. The docs describe this fairly well, but from the sound of it you need FULL for durability. On the other hand, WAL requires fewer write to commit a transaction, so (if I'm reading this correctly) FULL in WAL mode is much faster than FULL in non-WAL mode. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, synchronous=1 and durability
On Mon, Sep 10, 2012 at 09:50:58PM -0500, Jay A. Kreibich scratched on the wall: On Tue, Sep 11, 2012 at 01:58:23AM +0100, Simon Slavin scratched on the wall: On 11 Sep 2012, at 12:55am, Keith Chew keith.c...@gmail.com wrote: and I know FULL (1) will provide that. The question is why NORMAL (1) cannot provide the same. Because NORMAL doesn't flush changes to disk after every single transaction. It queues a few of them up and flushes them all in one go. That's not quite true. Oh wait, you're talking about WAL mode, aren't you? Sorry, apparently I wasn't following the whole thread as closely as I should have been. In that case, yes... the WAL file will save up several transactions and only fully sync them when the checkpoint happens to clear the WAL file. NORMAL) Faster but if you lose power more transactions are lost, but the database is still not corrupt. FULL) Every COMMIT will take more time, but a power-failure can lose a maximum of one transaction. That's also incorrect. In WAL mode that's only half incorrect. Your description of NORMAL seems correct, but FULL should be fully durable. The WAL file may need to be checkpointed on startup, but the if the commit happens in WAL/FULL, you should have full durability. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, synchronous=1 and durability
Hi Jay In WAL mode that's only half incorrect. Your description of NORMAL seems correct, but FULL should be fully durable. The WAL file may need to be checkpointed on startup, but the if the commit happens in WAL/FULL, you should have full durability. This is the reason for my question. As far as I can see, the difference between NORMAL and FULL in WAL mode is that FULL has one transaction in WAL to be checkpointed, and NORMAL has multiple. Since both of them need to checkpoint for durability, how is it that FULL can guarantee durability for 1 transaction, but not NORMAL for multiple? From a development point of view, I would imagine that both will use the same checkpoint routine, but yet I cannot understand why FULL has a guarantee and NORMAL does not.. Regards Keith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, synchronous=1 and durability
On 09/11/2012 11:12 AM, Keith Chew wrote: Hi Jay In WAL mode that's only half incorrect. Your description of NORMAL seems correct, but FULL should be fully durable. The WAL file may need to be checkpointed on startup, but the if the commit happens in WAL/FULL, you should have full durability. This is the reason for my question. As far as I can see, the difference between NORMAL and FULL in WAL mode is that FULL has one transaction in WAL to be checkpointed, and NORMAL has multiple. Since both of them need to checkpoint for durability, how is it that FULL can guarantee durability for 1 transaction, but not NORMAL for multiple? From a development point of view, I would imagine that both will use the same checkpoint routine, but yet I cannot understand why FULL has a guarantee and NORMAL does not.. In WAL mode, when a transaction is written to disk, the modified pages are appended to the *-wal file. Later on, during a checkpoint, all the modified pages in the *-wal file are copied back into the database file. In both synchronous=NORMAL and synchronous=FULL the *-wal file may contain more than one transaction. The WAL file includes a running checksum so that if a power failure occurs, the next client to read the database can determine the prefix of the WAL file that can be considered trustworthy (as the power failure may have corrupted more recently written parts of the file). In both NORMAL mode, we do the following: 1. Write a bunch of transactions into the WAL file. 2. fsync() the WAL file. 3. Copy the data from the WAL file into the database file. 4. fsync() the database file. If a power failure occurs at any time, the next process to read the database scans the WAL file and attempts to read as many transactions as possible. If the checksum fails at any point, it stops reading. So you can lose data. Say a power failure occurs between steps 1 and 2 above. If your WAL had 5 unsynced transactions in it then following recovery you may find that none, some or all of them have survived, depending on how much of the WAL file actually made it to disk before the power failed. Synchronous=FULL mode is different. During step 1 above in FULL mode, SQLite calls fsync() on the WAL file after writing each transaction to it - before the users COMMIT command returns. In this case if the same power failure occurs there is no chance that any successfully committed transactions will be lost, as they are guaranteed to have made it to disk. So, in WAL mode, both sync=NORMAL and sync=FULL prevent database corruption. However if a power failure occurs in NORMAL mode, some recently committed transactions may have disappeared following recovery. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users