Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| SHA1 Hash: | 9b3d9280b7271e3c03cc7b161e411e903c19ea84 |
|---|---|
| Date: | 2013-02-12 13:45:20 |
| User: | drh |
| Comment: | Updates to the temporary disk file document. |
Tags And Properties
- branch=trunk inherited from [b2e03e19ab]
- sym-trunk inherited from [b2e03e19ab]
Changes
Changes to pages/tempfiles.in
33 updated. But there is no guarantee that future versions of SQLite 33 updated. But there is no guarantee that future versions of SQLite 34 will use temporary files in the same way. New kinds of temporary 34 will use temporary files in the same way. New kinds of temporary 35 files might be employed and some of 35 files might be employed and some of 36 the current temporary file uses might be discontinued 36 the current temporary file uses might be discontinued 37 in future releases of SQLite. 37 in future releases of SQLite. 38 </p> 38 </p> 39 39 40 <tcl>hd_fragment seventypes</tcl> | 40 <tcl>hd_fragment types</tcl> 41 <h2>2.0 Seven Kinds Of Temporary Files</h2> | 41 <h2>2.0 Nine Kinds Of Temporary Files</h2> 42 42 43 <p> 43 <p> 44 SQLite currently uses seven distinct types of temporary files: | 44 SQLite currently uses nine distinct types of temporary files: 45 </p> 45 </p> 46 46 47 <ol> 47 <ol> 48 <li>Rollback journals</li> 48 <li>Rollback journals</li> 49 <li>Master journals</li> 49 <li>Master journals</li> > 50 <li>Write-ahead Log (WAL) files</li> > 51 <li>Shared-memory files</li> 50 <li>Statement journals</li> 52 <li>Statement journals</li> 51 <li>TEMP databases</li> 53 <li>TEMP databases</li> 52 <li>Materializations of views and subqueries</li> 54 <li>Materializations of views and subqueries</li> 53 <li>Transient indices</li> 55 <li>Transient indices</li> 54 <li>Transient databases used by VACUUM</li> 56 <li>Transient databases used by VACUUM</li> 55 </ol> 57 </ol> 56 58 ................................................................................................................................................................................ 126 as described above. The PERSIST journal mode foregoes the deletion of 128 as described above. The PERSIST journal mode foregoes the deletion of 127 the journal file and instead overwrites the rollback journal header 129 the journal file and instead overwrites the rollback journal header 128 with zeros, which prevents other processes from rolling back the 130 with zeros, which prevents other processes from rolling back the 129 journal and thus has the same effect as deleting the journal file, though 131 journal and thus has the same effect as deleting the journal file, though 130 without the expense of actually removing the file from disk. In other 132 without the expense of actually removing the file from disk. In other 131 words, journal mode PERSIST exhibits the same behavior as is seen 133 words, journal mode PERSIST exhibits the same behavior as is seen 132 in EXCLUSIVE locking mode. The 134 in EXCLUSIVE locking mode. The 133 OFF journal mode causes SQLite to forego creating a rollback journal | 135 OFF journal mode causes SQLite to the rollback journal. 134 in the first place. The OFF journal mode disables the atomic | 136 The OFF journal mode disables the atomic 135 commit and rollback capabilities of SQLite. The ROLLBACK command 137 commit and rollback capabilities of SQLite. The ROLLBACK command 136 is not available when OFF journal mode is set. And if a crash or power 138 is not available when OFF journal mode is set. And if a crash or power 137 loss occurs in the middle of a transaction that uses the OFF journal 139 loss occurs in the middle of a transaction that uses the OFF journal 138 mode, no recovery is possible and the database file will likely 140 mode, no recovery is possible and the database file will likely 139 go corrupt. 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. 140 </p> 147 </p> 141 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. > 200 </p> > 201 142 <tcl>hd_fragment masterjrnl</tcl> 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 <p> 205 <p> 146 The master journal file is used as part of the atomic commit 206 The master journal file is used as part of the atomic commit 147 process when a single transaction makes changes to multiple 207 process when a single transaction makes changes to multiple 148 databases that have been added to a single [database connection] 208 databases that have been added to a single [database connection] 149 using the [ATTACH] statement. The master journal file is always 209 using the [ATTACH] statement. The master journal file is always 150 located in the same directory as the main database file 210 located in the same directory as the main database file ................................................................................................................................................................................ 172 commit were interrupted in the middle by a crash or power loss, then 232 commit were interrupted in the middle by a crash or power loss, then 173 the changes to one of the databases might complete while the changes 233 the changes to one of the databases might complete while the changes 174 to another database might roll back. The master journal causes all 234 to another database might roll back. The master journal causes all 175 changes in all databases to either rollback or commit together. 235 changes in all databases to either rollback or commit together. 176 </p> 236 </p> 177 237 178 <tcl>hd_fragment stmtjrnl</tcl> 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 <p> 241 <p> 182 A statement journal file is used to rollback partial results of 242 A statement journal file is used to rollback partial results of 183 a single statement within a larger transaction. For example, suppose 243 a single statement within a larger transaction. For example, suppose 184 an UPDATE statement will attempt to modify 100 rows in the database. 244 an UPDATE statement will attempt to modify 100 rows in the database. 185 But after modifying the first 50 rows, the UPDATE hits 245 But after modifying the first 50 rows, the UPDATE hits 186 a constraint violation which should block the entire statement. 246 a constraint violation which should block the entire statement. ................................................................................................................................................................................ 219 deleted at the conclusion of the transaction. The size of the 279 deleted at the conclusion of the transaction. The size of the 220 statement journal is proportional to the size of the change implemented 280 statement journal is proportional to the size of the change implemented 221 by the UPDATE or INSERT statement that caused the statement journal 281 by the UPDATE or INSERT statement that caused the statement journal 222 to be created. 282 to be created. 223 </p> 283 </p> 224 284 225 <tcl>hd_fragment tempdb</tcl> 285 <tcl>hd_fragment tempdb</tcl> 226 <h3>2.4 TEMP Databases</h3> | 286 <h3>2.6 TEMP Databases</h3> 227 287 228 <p>Tables created using the "CREATE TEMP TABLE" syntax are only 288 <p>Tables created using the "CREATE TEMP TABLE" syntax are only 229 visible to the [database connection] in which the "CREATE TEMP TABLE" 289 visible to the [database connection] in which the "CREATE TEMP TABLE" 230 statement is originally evaluated. These TEMP tables, together 290 statement is originally evaluated. These TEMP tables, together 231 with any associated indices, triggers, and views, are collectively 291 with any associated indices, triggers, and views, are collectively 232 stored in a separate temporary database file that is created as 292 stored in a separate temporary database file that is created as 233 soon as the first "CREATE TEMP TABLE" statement is seen. 293 soon as the first "CREATE TEMP TABLE" statement is seen. ................................................................................................................................................................................ 254 <p> 314 <p> 255 The temporary files associated with the TEMP database and its 315 The temporary files associated with the TEMP database and its 256 rollback journal are only created if the application makes use 316 rollback journal are only created if the application makes use 257 of the "CREATE TEMP TABLE" statement. 317 of the "CREATE TEMP TABLE" statement. 258 </p> 318 </p> 259 319 260 <tcl>hd_fragment views</tcl> 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 <p>Queries that contain subqueries must sometime evaluate 323 <p>Queries that contain subqueries must sometime evaluate 264 the subqueries separately and store the results in a temporary 324 the subqueries separately and store the results in a temporary 265 table, then use the content of the temporary table to evaluate 325 table, then use the content of the temporary table to evaluate 266 the outer query. 326 the outer query. 267 We call this "materializing" the subquery. 327 We call this "materializing" the subquery. 268 The query optimizer in SQLite attempts to avoid materializing, 328 The query optimizer in SQLite attempts to avoid materializing, ................................................................................................................................................................................ 360 or not the subquery or outer query contain aggregate functions, 420 or not the subquery or outer query contain aggregate functions, 361 ORDER BY or GROUP BY clauses, LIMIT clauses, and so forth. 421 ORDER BY or GROUP BY clauses, LIMIT clauses, and so forth. 362 The rules for when a query and cannot be flattened are 422 The rules for when a query and cannot be flattened are 363 very complex and are beyond the scope of this document. 423 very complex and are beyond the scope of this document. 364 </p> 424 </p> 365 425 366 <tcl>hd_fragment transidx</tcl> 426 <tcl>hd_fragment transidx</tcl> 367 <h3>2.6 Transient Indices</h3> | 427 <h3>2.8 Transient Indices</h3> 368 428 369 <p> 429 <p> 370 SQLite may make use of transient indices to 430 SQLite may make use of transient indices to 371 implement SQL language features such as: 431 implement SQL language features such as: 372 </p> 432 </p> 373 433 374 <ul> 434 <ul> ................................................................................................................................................................................ 454 <p> 514 <p> 455 Note that the UNION ALL operator for compound queries does not 515 Note that the UNION ALL operator for compound queries does not 456 use transient indices by itself (though of course the right 516 use transient indices by itself (though of course the right 457 and left subqueries of the UNION ALL might use transient indices 517 and left subqueries of the UNION ALL might use transient indices 458 depending on how they are composed.) 518 depending on how they are composed.) 459 519 460 <tcl>hd_fragment vacuumdb</tcl> 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 <p> 523 <p> 464 The [VACUUM] command works by creating a temporary file 524 The [VACUUM] command works by creating a temporary file 465 and then rebuilding the entire database into that temporary 525 and then rebuilding the entire database into that temporary 466 file. Then the content of the temporary file is copied back 526 file. Then the content of the temporary file is copied back 467 into the original database file and the temporary file is 527 into the original database file and the temporary file is 468 deleted. 528 deleted. ................................................................................................................................................................................ 474 file will be no larger than the original database. 534 file will be no larger than the original database. 475 </p> 535 </p> 476 536 477 <tcl>hd_fragment tempstore *tempstore</tcl> 537 <tcl>hd_fragment tempstore *tempstore</tcl> 478 <h2>3.0 The SQLITE_TEMP_STORE Compile-Time Parameter and Pragma</h2> 538 <h2>3.0 The SQLITE_TEMP_STORE Compile-Time Parameter and Pragma</h2> 479 539 480 <p> 540 <p> 481 The rollback journal, master journal, | 541 The temporary files associated with transaction control, namely 482 and statement journal files are always written | 542 the rollback journal, master journal, write-ahead log (WAL) files, 483 to disk. | 543 and shared-memory files, are always written to disk. 484 But the other kinds of temporary files might be stored in memory 544 But the other kinds of temporary files might be stored in memory 485 only and never written to disk. 545 only and never written to disk. 486 Whether or not temporary files other than the rollback, 546 Whether or not temporary files other than the rollback, 487 master, and statement journals are written to disk or stored only in memory 547 master, and statement journals are written to disk or stored only in memory 488 depends on the [SQLITE_TEMP_STORE] compile-time parameter, the 548 depends on the [SQLITE_TEMP_STORE] compile-time parameter, the 489 [temp_store pragma], 549 [temp_store pragma], 490 and on the size of the temporary file. 550 and on the size of the temporary file.