Table of Contents for

Using SQLite

Common Usage

PRAGMA [database.]synchronous;
PRAGMA [database.]synchronous = mode;

Description

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:

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.

See Also

journal_mode, fullfsync