Documentation Source Text

Check-in [9b3d9280b7]
Login

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

Overview
Comment:Updates to the temporary disk file document.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:9b3d9280b7271e3c03cc7b161e411e903c19ea84
User & Date: drh 2013-02-12 13:45:20
Context
2013-02-12
13:49
Nokia back on the Sponsors section of the homepage. check-in: 8197ce8dbb user: drh tags: trunk
13:45
Updates to the temporary disk file document. check-in: 9b3d9280b7 user: drh tags: trunk
2013-01-25
15:06
Correction to the description of the WAL file checksum computation. check-in: 0a8b8c3b49 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/tempfiles.in.

    33     33   updated.  But there is no guarantee that future versions of SQLite
    34     34   will use temporary files in the same way.  New kinds of temporary
    35     35   files might be employed  and some of
    36     36   the current temporary file uses might be discontinued
    37     37   in future releases of SQLite.
    38     38   </p>
    39     39   
    40         -<tcl>hd_fragment seventypes</tcl>
    41         -<h2>2.0 Seven Kinds Of Temporary Files</h2>
           40  +<tcl>hd_fragment types</tcl>
           41  +<h2>2.0 Nine Kinds Of Temporary Files</h2>
    42     42   
    43     43   <p>
    44         -SQLite currently uses seven distinct types of temporary files:
           44  +SQLite currently uses nine distinct types of temporary files:
    45     45   </p>
    46     46   
    47     47   <ol>
    48     48   <li>Rollback journals</li>
    49     49   <li>Master journals</li>
           50  +<li>Write-ahead Log (WAL) files</li>
           51  +<li>Shared-memory files</li>
    50     52   <li>Statement journals</li>
    51     53   <li>TEMP databases</li>
    52     54   <li>Materializations of views and subqueries</li>
    53     55   <li>Transient indices</li>
    54     56   <li>Transient databases used by VACUUM</li>
    55     57   </ol>
    56     58   
................................................................................
   126    128   as described above.  The PERSIST journal mode foregoes the deletion of
   127    129   the journal file and instead overwrites the rollback journal header
   128    130   with zeros, which prevents other processes from rolling back the
   129    131   journal and thus has the same effect as deleting the journal file, though
   130    132   without the expense of actually removing the file from disk.  In other
   131    133   words, journal mode PERSIST exhibits the same behavior as is seen
   132    134   in EXCLUSIVE locking mode. The
   133         -OFF journal mode causes SQLite to forego creating a rollback journal
   134         -in the first place.  The OFF journal mode disables the atomic
          135  +OFF journal mode causes SQLite to the rollback journal.
          136  +The OFF journal mode disables the atomic
   135    137   commit and rollback capabilities of SQLite.  The ROLLBACK command
   136    138   is not available when OFF journal mode is set.  And if a crash or power
   137    139   loss occurs in the middle of a transaction that uses the OFF journal
   138    140   mode, no recovery is possible and the database file will likely
   139    141   go corrupt.
          142  +The MEMORY journal mode causes the rollback journal to be stored in
          143  +memory rather than on disk.  The ROLLBACK command still works when
          144  +the journal mode is MEMORY, but because no file exists on disks for
          145  +recovery, a crash or power loss in the middle of a transaction that uses
          146  +the MEMORY journal mode will likely result in a corrupt database.
          147  +</p>
          148  +
          149  +<tcl>hd_fragment walfile</tcl>
          150  +<h3>2.2 Write-Ahead Log (WAL) Files</h3>
          151  +
          152  +<p>
          153  +A write-ahead log or WAL file is used in place of a rollback journal
          154  +when SQLite is operating in [WAL mode].  As with the rollback journal,
          155  +the purpose of the WAL file is to implement atomic commit and rollback.
          156  +The WAL file is always located in the same directory
          157  +as the database file and has the same name as the database
          158  +file except with the 4 characters "<b>-wal</b>" appended.
          159  +The WAL file is created when the first connection to the
          160  +database is opened and is normally removed when the last
          161  +connection to the database closes.  However, if the last connection
          162  +does not shutdown cleanly, the WAL file will remain in the filesystem
          163  +and will be automatically cleaned up the next time the database is
          164  +opened.
          165  +</p>
          166  +
          167  +<tcl>hd_fragment shmfile</tcl>
          168  +<h3>2.3 Shared-Memory Files</h3>
          169  +
          170  +<p>
          171  +When operating in [WAL mode], all SQLite database connections associated
          172  +with the same database file need to share some memory that is used as an
          173  +index for the WAL file.  In most implementations, this shared memory is
          174  +implemented by calling mmap() on a file created for this sole purpose:
          175  +the shared-memory file.  The shared-memory file, if it exists, is located
          176  +in the same directory as the database file and has the same name as the
          177  +database file except with the 4 characters "<b>-shm</b>" appended.
          178  +Shared memory files only exist while running in WAL mode.
          179  +</p>
          180  +
          181  +<p>
          182  +The shared-memory file contains no persistent content.  The only purpose
          183  +of the shared-memory file is to provide a block of shared memory for use
          184  +by multiple processes all accessing the same database in WAL mode.
          185  +If the [VFS] is able to providean  alternative method for accessing shared
          186  +memory, then that alternative method might be used rather than the
          187  +shared-memory file.  For example, if [PRAGMA locking_mode] is set to
          188  +EXCLUSIVE (meaning that only one process is able to access the database
          189  +file) then the shared memory will be allocated from heap rather than out
          190  +of the shared-memory file, and the shared-memory file will never be
          191  +created.
          192  +</p>
          193  +
          194  +<p>
          195  +The shared-memory file has the same lifetime as its associated WAL file.
          196  +The shared-memory file is created when the WAL file is created and is
          197  +deleted when the WAL file is deleted.  During WAL file recovery, the
          198  +shared memory file is recreated from scratch based on the contents of
          199  +the WAL file being recovered.
   140    200   </p>
   141    201   
   142    202   <tcl>hd_fragment masterjrnl</tcl>
   143         -<h3>2.2 Master Journal Files</h3>
          203  +<h3>2.4 Master Journal Files</h3>
   144    204   
   145    205   <p>
   146    206   The master journal file is used as part of the atomic commit
   147    207   process when a single transaction makes changes to multiple
   148    208   databases that have been added to a single [database connection]
   149    209   using the [ATTACH] statement.  The master journal file is always
   150    210   located in the same directory as the main database file
................................................................................
   172    232   commit were interrupted in the middle by a crash or power loss, then
   173    233   the changes to one of the databases might complete while the changes
   174    234   to another database might roll back.  The master journal causes all
   175    235   changes in all databases to either rollback or commit together.
   176    236   </p>
   177    237   
   178    238   <tcl>hd_fragment stmtjrnl</tcl>
   179         -<h3>2.3 Statement Journal Files</h3>
          239  +<h3>2.5 Statement Journal Files</h3>
   180    240   
   181    241   <p>
   182    242   A statement journal file is used to rollback partial results of
   183    243   a single statement within a larger transaction.  For example, suppose
   184    244   an UPDATE statement will attempt to modify 100 rows in the database.
   185    245   But after modifying the first 50 rows, the UPDATE hits
   186    246   a constraint violation which should block the entire statement.
................................................................................
   219    279   deleted at the conclusion of the transaction.  The size of the
   220    280   statement journal is proportional to the size of the change implemented
   221    281   by the UPDATE or INSERT statement that caused the statement journal
   222    282   to be created.
   223    283   </p>
   224    284   
   225    285   <tcl>hd_fragment tempdb</tcl>
   226         -<h3>2.4 TEMP Databases</h3>
          286  +<h3>2.6 TEMP Databases</h3>
   227    287   
   228    288   <p>Tables created using the "CREATE TEMP TABLE" syntax are only
   229    289   visible to the [database connection] in which the "CREATE TEMP TABLE"
   230    290   statement is originally evaluated.  These TEMP tables, together
   231    291   with any associated indices, triggers, and views, are collectively
   232    292   stored in a separate temporary database file that is created as
   233    293   soon as the first "CREATE TEMP TABLE" statement is seen.
................................................................................
   254    314   <p>
   255    315   The temporary files associated with the TEMP database and its
   256    316   rollback journal are only created if the application makes use
   257    317   of the "CREATE TEMP TABLE" statement.
   258    318   </p>
   259    319   
   260    320   <tcl>hd_fragment views</tcl>
   261         -<h3>2.5 Materializations Of Views And Subqueries</h3>
          321  +<h3>2.7 Materializations Of Views And Subqueries</h3>
   262    322   
   263    323   <p>Queries that contain subqueries must sometime evaluate
   264    324   the subqueries separately and store the results in a temporary
   265    325   table, then use the content of the temporary table to evaluate
   266    326   the outer query.
   267    327   We call this "materializing" the subquery.
   268    328   The query optimizer in SQLite attempts to avoid materializing,
................................................................................
   360    420   or not the subquery or outer query contain aggregate functions,
   361    421   ORDER BY or GROUP BY clauses, LIMIT clauses, and so forth.
   362    422   The rules for when a query and cannot be flattened are
   363    423   very complex and are beyond the scope of this document.
   364    424   </p>
   365    425   
   366    426   <tcl>hd_fragment transidx</tcl>
   367         -<h3>2.6 Transient Indices</h3>
          427  +<h3>2.8 Transient Indices</h3>
   368    428   
   369    429   <p>
   370    430   SQLite may make use of transient indices to
   371    431   implement SQL language features such as:
   372    432   </p>
   373    433   
   374    434   <ul>
................................................................................
   454    514   <p>
   455    515   Note that the UNION ALL operator for compound queries does not
   456    516   use transient indices by itself (though of course the right
   457    517   and left subqueries of the UNION ALL might use transient indices
   458    518   depending on how they are composed.)
   459    519   
   460    520   <tcl>hd_fragment vacuumdb</tcl>
   461         -<h3>2.7 Transient Database Used By [VACUUM]</h3>
          521  +<h3>2.9 Transient Database Used By [VACUUM]</h3>
   462    522   
   463    523   <p>
   464    524   The [VACUUM] command works by creating a temporary file
   465    525   and then rebuilding the entire database into that temporary
   466    526   file.  Then the content of the temporary file is copied back
   467    527   into the original database file and the temporary file is
   468    528   deleted.
................................................................................
   474    534   file will be no larger than the original database.
   475    535   </p>
   476    536   
   477    537   <tcl>hd_fragment tempstore *tempstore</tcl>
   478    538   <h2>3.0 The SQLITE_TEMP_STORE Compile-Time Parameter and Pragma</h2>
   479    539   
   480    540   <p>
   481         -The rollback journal, master journal,
   482         -and statement journal files are always written
   483         -to disk.
          541  +The temporary files associated with transaction control, namely
          542  +the rollback journal, master journal, write-ahead log (WAL) files,
          543  +and shared-memory files, are always written to disk.
   484    544   But the other kinds of temporary files might be stored in memory
   485    545   only and never written to disk.
   486    546   Whether or not temporary files other than the rollback,
   487    547   master, and statement journals are written to disk or stored only in memory
   488    548   depends on the [SQLITE_TEMP_STORE] compile-time parameter, the
   489    549   [temp_store pragma],
   490    550   and on the size of the temporary file.