Documentation Source Text

Check-in [8ff1145035]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Updates to PRAGMA locking_mode and WAL documentation.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 8ff114503512cdfb6d5e393c526609efb9a7dd69
User & Date: drh 2010-11-03 01:22:05.000
Context
2010-11-10
18:43
Add some more documentation for EXPLAIN QUERY PLAN. (check-in: 37f6e9f261 user: dan tags: trunk)
2010-11-03
01:22
Updates to PRAGMA locking_mode and WAL documentation. (check-in: 8ff1145035 user: drh tags: trunk)
2010-11-02
14:46
Documentation of the WAL in EXCLUSIVE locking-mode changes. (check-in: 0aa0be4de5 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/pragma.in.
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
    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>








|

|







494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
    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>

522
523
524
525
526
527
528








529
530
531
532
533
534
535

   <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







>
>
>
>
>
>
>
>







522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543

   <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
Changes to pages/wal.in.
20
21
22
23
24
25
26
27
28


29
30
31
32
33
34
35
<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.







|
|
>
>







20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
<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.
365
366
367
368
369
370
371
372
373
374

375
376




377
378


379
380


381
382

383
384





385
386


387

388

389
390
391
392
393
394
395
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.







|

|
>
|
|
>
>
>
>

|
>
>
|
<
>
>
|
<
>
|

>
>
>
>
>
|
|
>
>
|
>

>







367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388

389
390
391

392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
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.