📄 tempfiles.html
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"><html><head><title>Temporary Files Used By SQLite</title><style type="text/css">body { margin: auto; font-family: "Verdana" "sans-serif"; padding: 8px 1%;}a { color: #45735f }a:visited { color: #734559 }.logo { position:absolute; margin:3px; }.tagline { float:right; text-align:right; font-style:italic; width:240px; margin:12px; margin-top:58px;}.toolbar { font-variant: small-caps; text-align: center; line-height: 1.6em; margin: 0; padding:1px 8px;}.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }.toolbar a:visited { color: white; }.toolbar a:hover { color: #80a796; background: white; }.content { margin: 5%; }.content dt { font-weight:bold; }.content dd { margin-bottom: 25px; margin-left:20%; }.content ul { padding:0px; padding-left: 15px; margin:0px; }/* rounded corners */.se { background: url(images/se.png) 100% 100% no-repeat #80a796}.sw { background: url(images/sw.png) 0% 100% no-repeat }.ne { background: url(images/ne.png) 100% 0% no-repeat }.nw { background: url(images/nw.png) 0% 0% no-repeat }</style><meta http-equiv="content-type" content="text/html; charset=UTF-8"> </head><body><div><!-- container div to satisfy validator --><a href="index.html"><img class="logo" src="images/SQLite.gif" alt="SQLite Logo" border="0"></a><div><!-- IE hack to prevent disappearing logo--></div><div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div><table width=100% style="clear:both"><tr><td> <div class="se"><div class="sw"><div class="ne"><div class="nw"> <div class="toolbar"> <a href="about.html">About</a> <a href="sitemap.html">Sitemap</a> <a href="docs.html">Documentation</a> <a href="download.html">Download</a> <a href="copyright.html">License</a> <a href="news.html">News</a> <a href="http://www.sqlite.org/cvstrac/index">Developers</a> <a href="support.html">Support</a> </div></div></div></div></div></td></tr></table> <h1 align="center">SQLite's Use Of Temporary Disk Files</h1><h2>1.0 Introduction</h2><p>On of the <a href="different.html">distinctive features</a> ofSQLite is that a database consists of a single disk file.This simplifies the use of SQLite since moving or backing up adatabase is a simple as copying a single file. It also makesSQLite appropriate for use as an<a href="whentouse.html#appfileformat">application file format</a>.But while a complete database is held in a single disk file,SQLite does make use of many temporary files during thecourse of processing a database.</p><p>This article describes the various temporary files that SQLitecreates and uses. It describes when the files are created, whenthey are deleted, what they are used for, why they are important,and how to avoid them on systems where creating temporary files isexpensive.</p><p>The manner in which SQLite uses temporary files is not consideredpart of the contract that SQLite makes with applications. Theinformation in this document is a correct description of howSQLite operates at the time that this document was written or lastupdated. But there is no guarantee that future versions of SQLitewill use temporary files in the same way. New kinds of temporaryfiles might be employed and some ofthe current temporary file uses might be discontinuedin future releases of SQLite.</p><h2>2.0 Seven Kinds Of Temporary Files</h2><p>SQLite currently uses seven distinct types of temporary files:</p><ol><li>Rollback journals</li><li>Master journals</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><p>Additional information about each of these temporary file typesis in the sequel.</p><h3>2.1 Rollback Journals</h3><p>A rollback journal is a temporary file used to implementatomic commit and rollback capabilities in SQLite.(For a detailed discussion of how this works, seethe separate document titled<a href="atomiccommit.html">Atomic Commit In SQLite</a>.)The rollback journal is always located in the same directoryas the database file and has the same name as the databasefile except with the 8 characters "<b>-journal</b>" appended.The rollback journal is usually created when a transactionis first started and is usually deleted when a transactioncommits or rolls back.The rollback journal file is essential for implementing theatomic commit and rollback capabilities of SQLite. Withouta rollback journal, SQLite would be unable to rollback anincomplete transaction, and if a crash or power loss occurredin the middle of a transaction the entire database would likelygo corrupt without a rollback journal.</p><p>The rollback journal is <i>usually</i> created and destroyed at thestart and end of a transaction, respectively. But there are exceptionsto this rule.</p><p>If a crash or power loss occurs in the middle of a transaction,then the rollback journal file is left on disk. The next timeanother application attempts to open the database file, it noticesthe presence of the abandoned rollback journal (we call it a "hotjournal" in this circumstance) and uses the information in thejournal to restore the database to its state prior to the startof the incomplete transaction. This is how SQLite implementsatomic commit.</p><p>If an application puts SQLite in <a href="pragma.html#pragma_locking_mode">exclusive locking mode</a> usingthe pragma:</p><blockquote><pre>PRAGMA locking_mode=EXCLUSIVE;</pre></blockquote><p>SQLite creates a new rollback journal at the start of the firsttransaction within an exclusive locking mode session. But at theconclusion of the transaction, it does not delete the rollbackjournal. The rollback journal might be truncated, or its headermight be zeroed (depending on what version of SQLite you are using)but the rollback journal is not deleted. The rollback journal isnot deleted until exclusive access mode is exited.</p><p>Rollback journal creation and deletion is also changed by the<a href="pragma.html#pragma_journal_mode">journal_mode pragma</a>.The default journaling mode is DELETE, which is the default behaviorof deleting the rollback journal file at the end of each transaction,as described above. The PERSIST journal mode foregoes the deletion ofthe journal file and instead overwrites the rollback journal headerwith zeros, which prevents other processes from rolling back thejournal and thus has the same effect as deleting the journal file, thoughwithout the expense of actually removing the file from disk. In otherwords, journal mode PERSIST exhibits the same behavior as is seenin EXCLUSIVE locking mode. TheOFF journal mode causes SQLite to forego creating a rollback journalin the first place. The OFF journal mode disables the atomiccommit and rollback capabilities of SQLite. The ROLLBACK commandis not available when OFF journal mode is set. And if a crash or powerloss occurs in the middle of a transaction that uses the OFF journalmode, no recovery is possible and the database file will likelygo corrupt.</p><h3>2.2 Master Journal Files</h3><p>The master journal file is used as part of the atomic commitprocess when a single transaction makes changes to multipledatabases that have been added to a single <a href="c3ref/sqlite3.html">database connection</a>using the <a href="lang_attach.html">ATTACH</a> statement. The master journal file is alwayslocated in the same directory as the main database file(the main database file is the database that is identifiedin the original <a href="c3ref/open.html">sqlite3_open()</a>, <a href="c3ref/open.html">sqlite3_open16()</a>, or<a href="c3ref/open.html">sqlite3_open_v2()</a> call that created the <a href="c3ref/sqlite3.html">database connection</a>)with a randomized suffix. The master database file containsthe names of all of the various attached auxiliary databasesthat were changed during the transaction. The multi-databasetransaction commits when the master journal file is deleted.See the documentation titled<a href="atomiccommit.html">Atomic Commit In SQLite</a> foradditional detail.</p><p>The master journal file is only created in cases where a single<a href="c3ref/sqlite3.html">database connection</a> is talking with two or more databases filesas a result of using <a href="lang_attach.html">ATTACH</a> to connection to auxiliary databases,and where a single transaction modifies more than one of thosedatabase files.Without the master journal, the transaction commit on a multi-databasetransaction would be atomic for each database individually, but itwould not be atomic across all databases. In other words, if thecommit were interrupted in the middle by a crash or power loss, thenthe changes to one of the databases might complete while the changesto another database might roll back. The master journal causes allchanges in all databases to either rollback or commit together.</p><h3>2.3 Statement Journal Files</h3><p>A statement journal file is used to rollback partial results ofa single statement within a larger transaction. For example, supposean UPDATE statement will attempt to modify 100 rows in the database.But after modifying the first 50 rows, the UPDATE hitsa constraint violation which should block the entire statement.The statement journal is used to undo the first 50 row changesso that the database is restored to the state it was in at the startof the statement.</p><p>A statement journal is only created for an UPDATE or INSERT statementthat might change muliple rows of a database and which might hit aconstraint or a RAISE exception within a trigger and thus need toundo partial results.If the UPDATE or INSERT is not contained within BEGIN...COMMIT and ifthere are no other active statements on the same database connection thenno statement journal is created since the ordinaryrollback journal can be used instead.The statement journal is also omitted if an alternative<a href="lang_conflict.html">conflict resolution algorithm</a> isused. For example:</p><blockquote><pre>UPDATE OR FAIL ...UPDATE OR IGNORE ...UPDATE OR REPLACE ...INSERT OR FAIL ...INSERT OR IGNORE ...INSERT OR REPLACE ...REPLACE INTO ....</pre></blockquote><p>The statement journal is given a randomized name, not necessarilyin the same directory as the main database, and is automaticallydeleted at the conclusion of the transaction. The size of thestatement journal is proportional to the size of the change implementedby the UPDATE or INSERT statement that caused the statement journalto be created.</p><h3>2.4 TEMP Databases</h3><p>Tables created using the "CREATE TEMP TABLE" syntax are onlyvisible to the <a href="c3ref/sqlite3.html">database connection</a> in which the "CREATE TEMP TABLE"statement is originally evaluated. These TEMP tables, togetherwith any associated indices, triggers, and views, are collectivelystored in a separate temporary database file that is created assoon as the first "CREATE TEMP TABLE" statement is seen.This separate temporary database file also has an associatedrollback journal.The temporary database file used to store TEMP tables is deletedautomatically when the <a href="c3ref/sqlite3.html">database connection</a> is closedusing <a href="c3ref/close.html">sqlite3_close()</a>.</p><p>The TEMP database file is very similar to auxiliary databasefiles added using the <a href="lang_attach.html">ATTACH</a> statement, though with a fewspecial properties.The TEMP database is always automatically deleted when the<a href="c3ref/sqlite3.html">database connection</a> is closed.The TEMP database always uses the<a href="pragma.html#pragma_synchronous">synchronous=OFF</a> and<a href="pragma.html#pragma_journal_mode">journal_mode=PERSIST</a><a href="pragma.html#syntax">PRAGMA</a> settings.And, the TEMP database cannot be used with <a href="lang_detach.html">DETACH</a> nor cananother process <a href="lang_attach.html">ATTACH</a> the TEMP database.</p><p>The temporary files associated with the TEMP database and itsrollback journal are only created if the application makes useof the "CREATE TEMP TABLE" statement.</p><h3>2.5 Materializations Of Views And Subqueries</h3>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -