Documentation Source Text

Check-in [b1e171c029]
Login

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

Overview
Comment:Update PRAGMA journal_mode documentation. Fix typos in the wal.html document.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: b1e171c02901886b2b5695361df17b1a58845ebf
User & Date: drh 2010-07-08 18:12:04
Context
2010-07-13
13:57
Updates to the lockingv3.html document. check-in: c354e08449 user: drh tags: trunk
2010-07-08
18:12
Update PRAGMA journal_mode documentation. Fix typos in the wal.html document. check-in: b1e171c029 user: drh tags: trunk
2010-07-07
02:03
Update the compile-time options document and footprint size bounds. Updates to the file format document. check-in: 0992cf9ad0 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/pragma.in.

360
361
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
...
403
404
405
406
407
408
409
410




411
412
413
414
415
416
417
       <br>PRAGMA </b><i>database</i><b>.journal_mode
              = <i>DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF</i></b></p>

    <p>This pragma queries or sets the journal mode for databases
    associated with the current [database connection].</p>)^

    <p>^The first two forms of this pragma query the current journaling
    mode.  ^In the first form, the default journal_mode is returned.
    ^The default journaling mode is the mode used by databases added
    to the connection by subsequent [ATTACH] statements.  ^The second
    form returns the current journaling mode for a specific database.</p>

    <p>^The last two forms change the journaling mode.  ^The 4th form
    changes the journaling mode for a specific database connection.
    ^Use "main" for the main database (the database that was opened by
    the original [sqlite3_open()], [sqlite3_open16()], or
    [sqlite3_open_v2()] interface call) and use "temp" for database
    that holds TEMP tables.  ^The 3rd form changes the journaling mode
    on all databases and it also changes the default journaling mode that
    will be used for new databases added by subsequent [ATTACH]
    commands.  ^The new journal mode is returned.  ^If the journal mode
    could not be changed, the original journal mode is returned.</p>

    <p>^The DELETE journaling mode is the normal behavior.  ^In the DELETE
    mode, the rollback journal is deleted at the conclusion of each
    transaction.  Indeed, the delete operation is the action that causes
    the transaction to commit.
    (See the documented titled <a href="atomiccommit.html">
................................................................................
    <p>^The MEMORY journaling mode stores the rollback journal in 
    volatile RAM.  ^This saves disk I/O but at the expense of database
    safety and integrity.  ^If the application using SQLite crashes in
    the middle of a transaction when the MEMORY journaling mode is set,
    then the database file will very likely go corrupt.</p>

    <p>^The WAL journaling mode uses a [write-ahead log] instead of a
    rollback journal to implement transactions.</p>





    <p>^The OFF journaling mode disables the rollback journal completely.
    ^No rollback journal is ever created and hence there is never a rollback
    journal to delete.  ^The OFF journaling mode disables the atomic
    commit and rollback capabilities of SQLite. The [ROLLBACK] command
    no longer works; it behaves in an undefined way.  Applications must
    avoid using the [ROLLBACK] command when the journal mode is OFF.







|
|
<
<


|




|
<
|







 







|
>
>
>
>







360
361
362
363
364
365
366
367
368


369
370
371
372
373
374
375
376

377
378
379
380
381
382
383
384
...
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
       <br>PRAGMA </b><i>database</i><b>.journal_mode
              = <i>DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF</i></b></p>

    <p>This pragma queries or sets the journal mode for databases
    associated with the current [database connection].</p>)^

    <p>^The first two forms of this pragma query the current journaling
    mode for <i>database</i>.  ^When <i>database</i> is omitted, the
    "main" database is queried.</p>



    <p>^The last two forms change the journaling mode.  ^The 4th form
    changes the journaling mode for a specific database connection named.
    ^Use "main" for the main database (the database that was opened by
    the original [sqlite3_open()], [sqlite3_open16()], or
    [sqlite3_open_v2()] interface call) and use "temp" for database
    that holds TEMP tables.  ^The 3rd form changes the journaling mode
    on all databases attached to the connection.

    ^The new journal mode is returned.  ^If the journal mode
    could not be changed, the original journal mode is returned.</p>

    <p>^The DELETE journaling mode is the normal behavior.  ^In the DELETE
    mode, the rollback journal is deleted at the conclusion of each
    transaction.  Indeed, the delete operation is the action that causes
    the transaction to commit.
    (See the documented titled <a href="atomiccommit.html">
................................................................................
    <p>^The MEMORY journaling mode stores the rollback journal in 
    volatile RAM.  ^This saves disk I/O but at the expense of database
    safety and integrity.  ^If the application using SQLite crashes in
    the middle of a transaction when the MEMORY journaling mode is set,
    then the database file will very likely go corrupt.</p>

    <p>^The WAL journaling mode uses a [write-ahead log] instead of a
    rollback journal to implement transactions.  ^The WAL journaling mode
    is persistent; after being set it stays in effect
    across multiple database connections and after closing and
    reopening the database.  ^A database in WAL journaling mode
    can only be accessed by SQLite version 3.7.0 or later.</p>

    <p>^The OFF journaling mode disables the rollback journal completely.
    ^No rollback journal is ever created and hence there is never a rollback
    journal to delete.  ^The OFF journaling mode disables the atomic
    commit and rollback capabilities of SQLite. The [ROLLBACK] command
    no longer works; it behaves in an undefined way.  Applications must
    avoid using the [ROLLBACK] command when the journal mode is OFF.

Changes to pages/wal.in.

30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
..
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
    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.
<li>It is not possible to change the database page size after entering WAL
    mode, either on an empty databse or by using [VACUUM] or by restoring
    from a backup using the [backup API].  You must be in a rollback journal
    mode to change the page size.
<li>WAL might be very slightly slower (perhaps 1% or 2% slower)
    than the traditional rollback-journal approach
    in applications that do mostly reads and seldom write.
<li>There is an additional persistent "*-wal" file associated with each
    database, which can make SQLite less appealing for use as an 
................................................................................
there are now three primitive operations:  reading, writing, and
checkpointing.</p>

<p>By default, SQLite does a checkpoint automatically when the WAL file
reaches a threshold size of 1000 pages.  Applications using WAL do
not have to do anything in order to for these checkpoints to occur.  
But if they want to, applications can adjust the automatic checkpoint
threshold.  Or they can turn off automatic the checkpoint and run 
checkpoints during idle moments or in a separate thread or process.</p>

<tcl>hd_fragment concurrency {WAL concurrency}</tcl>
<h3>Concurrency</h3>

<p>When a read operation begins on a WAL-mode database, it first
remembers the location of the last valid commit record in the WAL.







|







 







|







30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
..
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
    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.
<li>It is not possible to change the database page size after entering WAL
    mode, either on an empty database or by using [VACUUM] or by restoring
    from a backup using the [backup API].  You must be in a rollback journal
    mode to change the page size.
<li>WAL might be very slightly slower (perhaps 1% or 2% slower)
    than the traditional rollback-journal approach
    in applications that do mostly reads and seldom write.
<li>There is an additional persistent "*-wal" file associated with each
    database, which can make SQLite less appealing for use as an 
................................................................................
there are now three primitive operations:  reading, writing, and
checkpointing.</p>

<p>By default, SQLite does a checkpoint automatically when the WAL file
reaches a threshold size of 1000 pages.  Applications using WAL do
not have to do anything in order to for these checkpoints to occur.  
But if they want to, applications can adjust the automatic checkpoint
threshold.  Or they can turn off the automatic checkpoints and run 
checkpoints during idle moments or in a separate thread or process.</p>

<tcl>hd_fragment concurrency {WAL concurrency}</tcl>
<h3>Concurrency</h3>

<p>When a read operation begins on a WAL-mode database, it first
remembers the location of the last valid commit record in the WAL.