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