Sqlite3的synchronous的模式选择

关于Sqlite3的synchronous的模式选择，网上有很多类似的信息如下，中英文似乎都是这么一段:

1.如何设置：

PRAGMA synchronous = FULL; (2) 

PRAGMA synchronous = NORMAL; (1) 

PRAGMA synchronous = OFF; (0)
 

2.参数含义：

当synchronous设置为FULL (2), SQLite数据库引擎在紧急时刻会暂停以确定数据已经写入磁盘。这使系统崩溃或电源出问题时能确保数据库在重起后不会损坏。FULL synchronous很安全但很慢。
 

当synchronous设置为NORMAL, SQLite数据库引擎在大部分紧急时刻会暂停，但不像FULL模式下那么频繁。 NORMAL模式下有很小的几率(但不是不存在)发生电源故障导致数据库损坏的情况。但实际上，在这种情况 下很可能你的硬盘已经不能使用，或者发生了其他的不可恢复的硬件错误。
 

设置为synchronous OFF (0)时，SQLite在传递数据给系统以后直接继续而不暂停。若运行SQLite的应用程序崩溃， 数据不会损伤，但在系统崩溃或写入数据时意外断电的情况下数据库可能会损坏。另一方面，在synchronous OFF时 一些操作可能会快50倍甚至更多。在SQLite 2中，缺省值为NORMAL.而在3中修改为FULL。

但是对于上述的“SQLite数据库引擎在紧急时刻会暂停以确定数据已经写入磁盘”，阐述的并不是很清楚，其如何实现的也根本没提。

同样查找相关资料，在Sqlite3的官网论坛上也有人问类似的问题：

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.
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.
看看论坛上其他人得回答，似乎还是不是很满意其解释。

看下官网上的说明:

PRAGMA database.synchronous;
PRAGMA database.synchronous = 0 | OFF | 1 | NORMAL | 2 | FULL;

Query or change the setting of the "synchronous" flag. The first (query) form will return the synchronous setting as an integer.When synchronous is FULL (2), the SQLite database engine will use the xSync method of theVFS 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. With synchronous OFF (0), SQLite continues without syncing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk surface. On the other hand, some operations are as much as 50 or more times faster with synchronous OFF.

In WAL mode when synchronous is NORMAL (1), the WAL file is synchronized before eachcheckpoint and the database file is synchronized after each completedcheckpoint and the WAL file header is synchronized when a WAL file begins to be reused after a checkpoint, but no sync operations occur during most transactions.With synchronous=FULL in WAL mode, an additional sync operation of the WAL file happens after each transaction commit. The extra WAL sync following each transaction help ensure that transactions are durable across a power loss, but they do not aid in preserving consistency. If durability is not a concern, then synchronous=NORMAL is normally all one needs in WAL mode.

The default setting is synchronous=FULL.

See also the fullfsync and checkpoint_fullfsync pragmas.

网上赞同意见较多的解释，

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.

One fsync() per transaction written to the WAL file.

补充的意见是：

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(Atomic-原子性,Consistent-一致性,Isolated-隔离性,Durable-持久性), 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.

个人理解最关键的地方在于：

数据库为了执行备份，备份文件依赖于写磁盘，然后才会写到数据库文件磁盘中。关键在于这里的写磁盘，都是调用系统的write接口，绝大部分都是直接写缓冲区的，只有调用sync才会将缓冲区中的数据flush到磁盘。所以在write，sync，再wirte再sync的过程中，掉电后是否能恢复数据，依赖于sync是否有真正执行。从这个角度看，FULL和NORMAL的区别，似乎就只有sync调用的频率，FULL按照多人的意见是一个transaction一个sync，而NORMAL是多个transactions调一个sync