Documentation Source Text

    locking-mode back to NORMAL using this pragma and then accessing the
    database file (for read or write). ^Simply setting the locking-mode to
    NORMAL is not enough - locks are not be released until the next time
    the database file is accessed.</p>

    <p>There are three reasons to set the locking-mode to EXCLUSIVE.
    <ol>
    <li>The application actually want to prevent other processes from
        accessing the database file.
    <li>The number of system calls for filesystem operations is reduced,
        possibly resulting in a small performance increase.
    <li>^[WAL] databases can be accessed in EXCLUSIVE mode without the
        use of shared memory. 
        ([WAL without shared memory | Additional information])
    </ol>
    </p>

................................................................................

   <p>^The "temp" database (in which TEMP tables and indices are stored)
   and [in-memory databases]
   always uses exclusive locking mode.  ^The locking mode of temp and
   [in-memory databases] cannot
   be changed.  ^All other databases use the normal locking mode by default
   and are affected by this pragma.</p>








}

Pragma page_size {
   <p>^(<b>PRAGMA page_size;
       <br>PRAGMA page_size = </b><i>bytes</i><b>;</b></p>
    <p>Query or set the page size of the database.)^ ^The page size
    may only be set if the database has not yet been created. ^The page

    locking-mode back to NORMAL using this pragma and then accessing the
    database file (for read or write). ^Simply setting the locking-mode to
    NORMAL is not enough - locks are not be released until the next time
    the database file is accessed.</p>

    <p>There are three reasons to set the locking-mode to EXCLUSIVE.
    <ol>
    <li>^The application wants to prevent other processes from
        accessing the database file.
    <li>^The number of system calls for filesystem operations is reduced,
        possibly resulting in a small performance increase.
    <li>^[WAL] databases can be accessed in EXCLUSIVE mode without the
        use of shared memory. 
        ([WAL without shared memory | Additional information])
    </ol>
    </p>

................................................................................

   <p>^The "temp" database (in which TEMP tables and indices are stored)
   and [in-memory databases]
   always uses exclusive locking mode.  ^The locking mode of temp and
   [in-memory databases] cannot
   be changed.  ^All other databases use the normal locking mode by default
   and are affected by this pragma.</p>

   <p>^If the locking mode is EXCLUSIVE when first entering
   [WAL | WAL journal mode], then the locking mode cannot be changed to
   NORMAL until after exiting WAL journal mode. 
   ^If the locking mode is NORMAL when first entering WAL
   journal mode, then the locking mode can be changed between NORMAL and
   EXCLUSIVE and back again at any time and without needing to exit
   WAL journal mode.</p>
}

Pragma page_size {
   <p>^(<b>PRAGMA page_size;
       <br>PRAGMA page_size = </b><i>bytes</i><b>;</b></p>
    <p>Query or set the page size of the database.)^ ^The page size
    may only be set if the database has not yet been created. ^The page

<li>WAL uses many fewer fsync() operations and is thus less vulnerable to
    problems on systems where the fsync() system call is broken.
</ol>

<p>But there are also disadvantages:</p>

<ol>
<li>WAL is only available when the [sqlite3_vfs | VFS] 


    supports shared-memory primitives.  The built-in unix and windows VFSes
    support this but third-party extension VFSes for custom operating
    systems might not.
<li>All processes using a database must be on the same host computer;
    WAL does not work over a network filesystem.
<li>Transactions that involve changes against multiple [ATTACH | ATTACHed]
    databases are atomic for each individual database, but are not
    atomic across all databases as a set.
................................................................................
For example, if it is known that a particular database
will only be accessed by threads within a single process, the wal-index
can be implemented using heap memory instead of true shared memory.</p>

<tcl>hd_fragment noshm {WAL without shared memory}</tcl>
<h2>Use of WAL Without Shared-Memory</h2>

<p>Beginning in SQLite version 3.7.4, WAL databases can be read and
written even if shared memory is unavailable as long as the

[locking_mode] is set to EXCLUSIVE.  In other words, a process can
read and write a WAL database without using shared memory if that
process is guaranteed to be the only process accessing the database.</p>





<p>When [locking_mode] is EXCLUSIVE, the SQLite connection creates its
own private wal-index in heap memory.  While a wal-index is held in heap
memory, the [locking_mode] cannot be changed back to normal - invocations






of "PRAGMA locking_mode=NORMAL" become no-ops.  In order to change back
to NORMAL locking_mode, the database must first be converted to use
a rollback journal, for example by "PRAGMA journal_mode=DELETE;"</p>



<p>To access a pre-existing WAL database without using shared memory,
the database connection must run "PRAGMA locking_mode=EXCLUSIVE;" prior
to any attempt to read or write the database.</p>













<h2>Backwards Compatibility</h2>

<p>The database file format is unchanged for WAL mode.  However, the
WAL file and the [wal-index] are new concepts and so older versions of 
SQLite will not know
how to recover a crashed SQLite database that was operating in WAL mode
when the crash occurred.

<li>WAL uses many fewer fsync() operations and is thus less vulnerable to
    problems on systems where the fsync() system call is broken.
</ol>

<p>But there are also disadvantages:</p>

<ol>
<li>WAL normally requires that the [sqlite3_vfs | VFS] 
    supports shared-memory primitives.
    (Exception: [WAL without shared memory])
    The built-in unix and windows VFSes
    support this but third-party extension VFSes for custom operating
    systems might not.
<li>All processes using a database must be on the same host computer;
    WAL does not work over a network filesystem.
<li>Transactions that involve changes against multiple [ATTACH | ATTACHed]
    databases are atomic for each individual database, but are not
    atomic across all databases as a set.
................................................................................
For example, if it is known that a particular database
will only be accessed by threads within a single process, the wal-index
can be implemented using heap memory instead of true shared memory.</p>

<tcl>hd_fragment noshm {WAL without shared memory}</tcl>
<h2>Use of WAL Without Shared-Memory</h2>

<p>Beginning in SQLite version 3.7.4, ^WAL databases can be created, read, and
written even if shared memory is unavailable as long as the
[locking_mode] is set to EXCLUSIVE before the first attempted access.
In other words, a process can interact with
a WAL database without using shared memory if that
process is guaranteed to be the only process accessing the database.
^This feature allows WAL databases to be created, read, and written
by legacy [sqlite3_vfs | VFSes] that lack the "version 2" shared-memory
methods xShmMap, xShmLock, xShmBarrier, and xShmUnmap on the
[sqlite3_io_methods] object.</p>




<p>^(If EXCLUSIVE locking mode is set prior to the first WAL-mode 
database access, then SQLite never attempts to call any of the
shared-memory methods and hence no shared-memory
wal-index is ever created.)^
^(In that case, the database connection remains in EXCLUSIVE mode
as long as the journal mode is WAL; attempts to change the locking
mode using "<tt>PRAGMA locking_mode=NORMAL;</tt>" are no-ops.)^


^The only way to change out of EXCLUSIVE locking mode is to first
change out of WAL journal mode.</p>




<p>^If NORMAL locking mode is in effect for the first WAL-mode database
access, then the shared-memory wal-index is created.  ^This means that the
underlying VFS must support the "version 2" shared-memory.
^If the VFS does not support shared-memory methods, then the attempt to
open a database that is already in WAL mode, or the attempt convert a
database into WAL mode, will fail.
^As long a connection is using a shared-memory wal-index, the locking
mode can be changed freely between NORMAL and EXCLUSIVE.  ^It is only
when the shared-memory wal-index is omitted, when the locking mode is
EXCLUSIVE prior to the first WAL-mode database access, that the locking
mode is stuck in EXCLUSIVE.</p>

<tcl>hd_fragment bkwrds {WAL backwards compatibility}</tcl>
<h2>Backwards Compatibility</h2>

<p>The database file format is unchanged for WAL mode.  However, the
WAL file and the [wal-index] are new concepts and so older versions of 
SQLite will not know
how to recover a crashed SQLite database that was operating in WAL mode
when the crash occurred.