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
