Documentation Source Text

Check-in [0aa0be4de5]
Login

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

Overview
Comment:Documentation of the WAL in EXCLUSIVE locking-mode changes.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0aa0be4de54f6999e8d66ab2afd7ad60245888ec
User & Date: drh 2010-11-02 14:46:40
Context
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
2010-10-14
17:27
Fix the join-op syntax diagram so that it allows LEFT JOIN in addition to LEFT OUTER JOIN. check-in: 1a0decefd2 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

37
38
39
40
41
42
43









44
45
46
47
48
49
50
      <a href="http://www.sqlite.org/src/timeline">
      http://www.sqlite.org/src/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}










chng {2010 October 08 (3.7.3)} {
<li> Added the [sqlite3_create_function_v2()] interface that includes a
     destructor callback.
<li> Added support for [custom r-tree queries] using application-supplied
     callback routines to define the boundary of the query region.
<li> The default page cache strives more diligently to avoid using memory







>
>
>
>
>
>
>
>
>







37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
      <a href="http://www.sqlite.org/src/timeline">
      http://www.sqlite.org/src/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}

chng {2010 December 08 (3.7.4)} {
<li> Added the [sqlite3_blob_reopen()] interface to allow an existing
     [sqlite3_blob] object to be rebound to a new row.
<li> Use the new [sqlite3_blob_reopen()] interface to improve the performance
     of FTS.
<li> [sqlite3_vfs | VFSes] that does not support shared memory are allowed
     to access [WAL] databases if [PRAGMA locking_mode] is set to EXCLUSIVE.
}

chng {2010 October 08 (3.7.3)} {
<li> Added the [sqlite3_create_function_v2()] interface that includes a
     destructor callback.
<li> Added support for [custom r-tree queries] using application-supplied
     callback routines to define the boundary of the query region.
<li> The default page cache strives more diligently to avoid using memory

Changes to pages/docs.in.

58
59
60
61
62
63
64




65
66
67
68
69
70
71
}
doc {Alphabetical Listing Of Documents} {doclist.html} {
  A list of all titled pages on this website, sorted by title.
}
doc {Website Keyword Index} {keyword_index.html} {
  A cross-reference from keywords to various pages within this website.
}






heading {SQLite Programming Interfaces} {
  Documentation describing the APIs used to program SQLite, and the SQL
  dialect that it interprets.
}








>
>
>
>







58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
}
doc {Alphabetical Listing Of Documents} {doclist.html} {
  A list of all titled pages on this website, sorted by title.
}
doc {Website Keyword Index} {keyword_index.html} {
  A cross-reference from keywords to various pages within this website.
}
doc {Permuted Title Index} {sitemap.html#pindex} {
  Also known as a "keyword in context" or "KWIC" index or as a concordance,
  this document is a listing of all other documents sorted by keyword.
}


heading {SQLite Programming Interfaces} {
  Documentation describing the APIs used to program SQLite, and the SQL
  dialect that it interprets.
}

Changes to pages/index.in.

83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99

</td>
<td width="20"></td><td bgcolor="#044a64" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_7_3.html">Version 3.7.3</a>
of SQLite is recommended for all new development.
Upgrading from version 3.7.2 is optional.
Upgrading from all other SQLite versions is recommended.</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>
<li> <a href="features.html">Features</a> </li>







|

|







83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99

</td>
<td width="20"></td><td bgcolor="#044a64" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_7_4.html">Version 3.7.4</a>
of SQLite is recommended for all new development.
Upgrading from versions 3.7.2 and 3.7.3 is optional.
Upgrading from all other SQLite versions is recommended.</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>
<li> <a href="features.html">Features</a> </li>

Changes to pages/pragma.in.

492
493
494
495
496
497
498
499

500
501
502
503






504
505
506
507
508
509
510
    <p>^Database locks obtained by a connection in EXCLUSIVE mode may be
    released either by closing the database connection, or by setting the
    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 two reasons to set the locking-mode to EXCLUSIVE. One

    is if the application actually wants to prevent other processes from
    accessing the database file. The other is that a small number of
    filesystem operations are saved by optimizations enabled in this
    mode. This may be significant in embedded environments.</p>







    <p>^(When the locking_mode pragma specifies a particular database,
    for example:</p>

    <blockquote>
PRAGMA <b>main.</b>locking_mode=EXCLUSIVE;
    </blockquote>







|
>
|
|
<
|
>
>
>
>
>
>







492
493
494
495
496
497
498
499
500
501
502

503
504
505
506
507
508
509
510
511
512
513
514
515
516
    <p>^Database locks obtained by a connection in EXCLUSIVE mode may be
    released either by closing the database connection, or by setting the
    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>^(When the locking_mode pragma specifies a particular database,
    for example:</p>

    <blockquote>
PRAGMA <b>main.</b>locking_mode=EXCLUSIVE;
    </blockquote>

Changes to pages/wal.in.

362
363
364
365
366
367
368



















369
370
371
372
373
374
375
<p>Specialized applications for which the default implementation of
shared memory is unacceptable can devise alternative methods via a
custom [sqlite3_vfs | VFS].  
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>





















<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







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







362
363
364
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
<p>Specialized applications for which the default implementation of
shared memory is unacceptable can devise alternative methods via a
custom [sqlite3_vfs | VFS].  
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