Using SQLite

Name

synchronous — Control the database disk synchronization

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 ...