Table of Contents for

Using SQLite

Common Usage

PRAGMA locking_mode;
PRAGMA locking_mode = mode;
PRAGMA database.locking_mode;
PRAGMA database.locking_mode = mode;

Description

The locking_mode pragma controls how database file locks are managed. The mode can either be NORMAL or EXCLUSIVE. In normal mode, the database connection acquires and releases the appropriate locks with each transaction. In exclusive mode, the locks are acquired in the normal fashion, but are not released when a transaction finishes.

Although exclusive locking mode prevents any other database connections from accessing the database, it also provides better performance. This may be an easy trade-off in situations such as some embedded environments, where it is very unlikely that multiple processes will ever attempt to access the same database.

Exclusive locking reduces the start-up cost of a transaction by eliminating the need to acquire the appropriate locks. It also allows SQLite to skip several file reads at the start of each transaction. For example, when SQLite starts a new transaction, it will normally reread the database header to verify that the database schema has not changed. This is not required if the database is in exclusive mode.

Since temporary and in-memory databases cannot be accessed by more than one database connection, they are always in exclusive mode. Any attempt to set these database types to normal mode will silently fail.

The behavior of the locking_mode pragma depends on if an explicit database 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. 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 get or set the mode of the main database, you must explicitly use the main database name.

There are two ways to release the locks of a database in exclusive mode. First, the database can simply be closed (or detached). This will release any locks. The database can be reopened (or reattached). Even if the default locking mode is exclusive, the locks will not be acquired until the database goes through a transaction cycle.

The second way to release the locks is to place the database into normal locked mode and execute some SQL command that will force a lock/unlock transaction cycle. Simply putting the database into normal mode will not release any locks that have already been acquired.

See Also

journal_mode, lock_status, ATTACH DATABASE [SQL Cmd, Ap C], DETACH DATABASE [SQL Cmd, Ap C]