The synchronous pragma gets or sets the current
disk synchronization mode. This controls how aggressively SQLite
will write data all the way out to physical storage.
Because most physical storage systems (such as hard drives) are very slow when compared to processor and memory speeds, most computing environments have a large number of caches and buffers between an application and the actual, long-term physical storage system. These layers introduce a significant window of time between the time when an application is told the data was successfully written, and the time when the data is actually written to long-term storage. Three or four seconds is typical, but in some cases this window can be a dozen seconds or more. If the system crashes or suffers a power failure within that window, some of the “written” data will be lost.
If that were to happen to an SQLite database file, the database would undoubtedly become corrupted. To properly enforce transactions and prevent corruption, SQLite depends on writes being permanent, even in the face of a system crash or power failure. This requires that SQLite write commands happen in order and go all the way to the physical storage. To accomplish this, SQLite will request an immediate disk synchronization after any critical write operations. This causes the application to pause until the operating system can confirm that the data has been written to long-term storage.
While this is very safe, it is also very slow. In some situations, it may be acceptable to turn off some of these protections in favor of raw speed. While this isn’t recommended for long-term situations, it can make sense for short, repeatable operations, such as bulk-loading import data. Just be sure to make a backup first.
SQLite offers three levels of protection, as shown in the following table:
| Mode | Meaning |
|---|---|
0 or
OFF | No syncs at all |
1 or
NORMAL | Sync after each sequence of critical disk operations |
2 or
FULL | Sync after each critical disk operation |
The set mode can be either the name or the integer equivalent. The returned value will always be an integer.
In FULL mode, a full synchronization is done after
each and every critical disk operation. This mode is designed to
avoid corruption in the face of any application crash, system
crash, or power failure. It is the safest, but also the slowest.
The default mode is FULL (not
NORMAL).
In NORMAL mode, a full synchronization is done
after each sequence of critical disk operations. This mode
reduces the total number of synchronization calls, but
introduces a very small chance of having a system crash or power
failure corrupt the database file. NORMAL mode attempts to strike a balance
between good protection and moderate performance.
In OFF mode, no attempt is made to flush or
synchronize writes, leaving it up to the operating system to
write out any filesystem cache at its own convenience. This mode
is much, much faster than the other two modes, but leaves SQLite
wide open to system crashes and power failures, even after a
transaction has completed.
Be aware that on some systems, a
power failure can still cause database corruption, even if
running in FULL mode. There
are a number of disk controllers that will falsely report a
synchronization request as successfully completed before all of
the data is actually written to nonvolatile storage. If a power
failure happens while data is still being held in the disk
controller cache, a file corruption may still occur.
Unfortunately, there isn’t anything SQLite or the host operating
system can do about this, as there is no way for the operating
system or SQLite to know the information is untrue.