Documentation Source Text

Check-in [715819ca53]
Login

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

Overview
Comment:Add documentation on the TRUNCATE journal mode.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 715819ca5394534dc8d32153d81566e2a514d2c0
User & Date: drh 2008-09-26 21:38:05.000
Context
2008-09-28
11:51
Fix typo in the VDBE document. (check-in: 163fe7f882 user: drh tags: trunk)
2008-09-26
21:38
Add documentation on the TRUNCATE journal mode. (check-in: 715819ca53 user: drh tags: trunk)
2008-09-25
18:51
Small amount of progress on fileio.html. (check-in: 1344ab631b user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/atomiccommit.in.
1066
1067
1068
1069
1070
1071
1072































1073
1074
1075
1076
1077
1078
1079
BEGIN EXCLUSIVE;
COMMIT;
</per></blockquote>

<p>Beware of deleting persistent journal files by any other means
since the journal file might be hot, in which case deleting it will
corrupt the corresponding database file.</p>
































<h2>8.0 Testing Atomic Commit Behavior</h2>

<p>The developers of SQLite are confident that it is robust
in the face of power failures and system crashes because the
automatic test procedures do extensive checks on
the ability of SQLite to recover from simulated power loss.







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
BEGIN EXCLUSIVE;
COMMIT;
</per></blockquote>

<p>Beware of deleting persistent journal files by any other means
since the journal file might be hot, in which case deleting it will
corrupt the corresponding database file.</p>

<p>Beginning in SQLite version 3.6.4, the TRUNCATE journal mode is
also supported:</p>

<blockquote><pre>
PRAGMA journal_mode=TRUNCATE;
</pre></blockquote>

<p>In truncate journal mode, the transaction is committed by truncating
the journal file to zero length rather than deleting the journal file
(as in DELETE mode) or by zeroing the header (as in PERSIST mode).
TRUNCATE mode shares the advantage of PERSIST mode that the directory
that contains the journal file and database does not need to be updated.
Hence truncating a file is often faster than deleting it.  TRUNCATE has
the additional advantage that it is not followed by a
system call (ex: fsync()) to synchronize the change to disk.  It might
be safer if it did.
But on many modern filesystems, a truncate is an atomic and
synchronous operation and so we think that TRUNCATE will usually be safe
in the face of power failures.  If you are uncertain about whether or
not TRUNCATE will be synchronous and atomic on your filesystem and it is
important to you that your database survive a power loss or operating
system crash that occurs during the truncation operation, then you might
consider using a different journaling mode.</p>

<p>On embedded systems with synchronous filesystems, TRUNCATE is results
in slower behavior than PERSIST.  The commit operation is the same speed.
But subsequent transactions are slower following a TRUNCATE because it is
faster to overwrite existing content than to append to the end of a file.
New journal file entries will always be appended following a TRUNCATE but
will usually overwrite with PERSIST.</p>

<h2>8.0 Testing Atomic Commit Behavior</h2>

<p>The developers of SQLite are confident that it is robust
in the face of power failures and system crashes because the
automatic test procedures do extensive checks on
the ability of SQLite to recover from simulated power loss.
Changes to pages/pragma.in.
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
    current syntax or functionality as it is likely to change.</p>
</li>

<tcl>Subsection journal_mode</tcl>
<li><p><b>PRAGMA journal_mode;
       <br>PRAGMA <i>database</i>.journal_mode;
       <br>PRAGMA journal_mode
              = <i>DELETE | PERSIST | OFF</i>
       <br>PRAGMA <i>database</i>.journal_mode
              = <i>DELETE | PERSIST | 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







|

|







259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
    current syntax or functionality as it is likely to change.</p>
</li>

<tcl>Subsection journal_mode</tcl>
<li><p><b>PRAGMA journal_mode;
       <br>PRAGMA <i>database</i>.journal_mode;
       <br>PRAGMA journal_mode
              = <i>DELETE | TRUNCATE | PERSIST | OFF</i>
       <br>PRAGMA <i>database</i>.journal_mode
              = <i>DELETE | TRUNCATE | PERSIST | 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
288
289
290
291
292
293
294





295
296
297
298
299
300
301

    <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">
    Atomic Commit In SQLite</a> for additional detail.)</p>






    <p>The PERSIST journaling mode prevents the rollback journal from
    being deleted at the end of each transaction.  Instead, the header
    of the journal is overwritten with zeros.  This will prevent other
    database connections from rolling the journal back.  The PERSIST
    journaling mode is useful as an optimization on platforms where
    deleting a file is much more expensive than overwriting the first







>
>
>
>
>







288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306

    <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">
    Atomic Commit In SQLite</a> for additional detail.)</p>

    <p>The TRUNCATE journaling mode commits transactions by truncating
    the rollback journal to zero-length instead of deleting it.  On many
    systems, truncating a file is much faster than deleting the file since
    the containing directory need to be changed.</p>

    <p>The PERSIST journaling mode prevents the rollback journal from
    being deleted at the end of each transaction.  Instead, the header
    of the journal is overwritten with zeros.  This will prevent other
    database connections from rolling the journal back.  The PERSIST
    journaling mode is useful as an optimization on platforms where
    deleting a file is much more expensive than overwriting the first