Sqlite3的synchronous的模式选择

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

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 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. 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 each checkpoint and the database file is synchronized after each completed checkpoint 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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!