journal_mode

Name

journal_mode — Control the creation and cleanup of journal files

Common Usage

PRAGMA journal_mode;
PRAGMA journal_mode = mode;
PRAGMA database.journal_mode;
PRAGMA database.journal_mode = mode;

Description

The journal_mode pragma gets or sets the journal mode. The journal mode controls how the journal file is stored and processed.

Journal files are used by SQLite to roll back transactions due to an explicit ROLLBACK command, or because an unrecoverable error was encountered (such as a constraint violation). Normally, a journal file is required to process any SQL command that causes a modification to a database file. The active lifetime of a journal file extends from the first database modification to the end of the commit process. Journal files are required for both auto-commit transactions (individual SQL commands) as well as explicit transactions. Journal files are a critical part of the transaction process.

There are five supported journal modes:

DELETE: This is the normal behavior. At the conclusion of a transaction, the journal file is deleted.
TRUNCATE: The journal file is truncated to a length of zero bytes. On many filesystems, this is slightly faster than a delete.
PERSIST: The journal file is left in place, but the header is overwritten to indicate the journal is no longer valid. On some filesystems, this can be faster still, as the file blocks remain allocated.
MEMORY: The journal record is held in memory, rather than on disk. This option is extremely fast, but also somewhat dangerous. If an application crashes or is killed while a transaction is in progress, it is likely to result in a corrupt database file.
OFF: No journal record is kept. As with the MEMORY journal mode, if the application crashes or is killed, the database will likely become corrupted. Further, if the journal is completely turned off, the database has no rollback capability. The ROLLBACK command should not be used on a database that has the journal turned off.

The behavior of the journal_mode pragma depends on whether an explicit database name is given or not. If no database name is given, the pragma gets or sets the default value stored in the database connection. This is the mode that will be used for any newly attached database, and will not modify database files that are already open.

Each open database also has its own unique journal mode. If a database name is given, this pragma will get or set the journal mode for that specific database. If you want to set the mode of the main database, you must explicitly use the main database name.

All versions of the journal_mode pragma will return the current value as a lowercase text value.

In-memory databases only support the MEMORY and OFF modes. Any attempt to set an in-memory database to a different mode will silently fail and the existing mode will be returned.

See Also

journal_size_limit, synchronous, ROLLBACK TRANSACTION [SQL Cmd, Ap C]