Documentation Source Text

  never get any real work done.  This parameter prevents an infinite loop
  by forcing the spinning thread to give up after a fixed number of attempts
  at recompiling the prepared statement.  The default setting is 5 which is
  more than adequate for most applications.  But in some obscure cases, it
  is useful to raise this parameter to 100 or more to prevent spurious
  [SQLITE_SCHEMA] errors when running [sqlite3_step()].
}

COMPILE_OPTION {SQLITE_POWERSAFE_OVERWRITE=<i>&lt;0 or 1&gt;</i>} {
  This option changes the default assumption about [powersafe overwrite]
  for the underlying filesystems for the unix and windows [VFSes].
  Setting SQLITE_POWERSAFE_OVERWRITE to 1 causes SQLite to assume that
  application-level writes cannot changes bytes outside the range of
  bytes written even if the write occurs just before a power loss.
  With SQLITE_POWERSAFE_OVERWRITE set to 0, SQLite assumes that other
  bytes in the same sector with a written byte might be changed or 
  damaged by a power loss.
}

COMPILE_OPTION {YYSTACKDEPTH=<i>&lt;max_depth&gt;</i>} {
  This macro sets the maximum depth of the LALR(1) stack used by
  the SQL parser within SQLite.  The default value is 100.  A typical
  application will use less than about 20 levels of the stack.
  Developers whose applications contain SQL statements that 
  need more than 100 LALR(1) stack entries should seriously

    crashes or the computer loses power before that data has been written
    to the disk surface.  On the other hand, some
    operations are as much as 50 or more times faster with synchronous OFF.
    </p>
 
    <p>^In [WAL] mode when synchronous is NORMAL (1), the WAL file is
    synchronized before each [checkpoint] and the database file is
    synchronized after each completed [checkpoint] and the WAL file
    header is synchronized when a WAL file begins to be reused after
    a checkpoint, but no sync operations occur during most transactions.
    ^With synchronous=FULL in WAL mode, an additional
    sync operation of the WAL file happens after each transaction commit.
    The extra WAL sync following each transaction help ensure that 
    transactions are durable across a power loss, but they do not aid
    in preserving consistency.
    If durability is not a concern, then synchronous=NORMAL is normally
    all one needs in WAL mode.</p>

    <p>^The default setting is synchronous=FULL.</p>

    <p>See also the [fullfsync] and [checkpoint_fullfsync] pragmas.</p>
}

<title>Powersafe Overwrite</title>
<tcl>hd_keywords {PSOW} {powersafe overwrite}</tcl>

<h1 align="center">Powersafe Overwrite</h1>

<p>"Powersafe overwrite" is a term used by the SQLite team to describe
a behavior of some filesystems and disk-controllers related to
data preservation during a power loss.  Powersafe overwrite
is a boolean property: either the storage system has it or it does not.

<p>We say that a system has the powersafe overwrite property if the following
statement is true:

<blockquote>
  <b>Writing a range of bytes in a file at the application level will not
  change any bytes outside of that range, even if the write occurs
  just before a crash or power failure.</b>
</blockquote>

<p>The powersafe overwrite property says nothing about the state of the
bytes that were written.  Those bytes might contain their old values,
their new values, random values, or some combination of these.  The powersafe
overwrite property merely states that writes cannot change bytes outside
of the range of bytes written.

<p>In other words, powersafe overwrite means that there is no "collateral
damage" when a power loss occurs while writing.  Only those bytes actually
being written might be damaged.

<p>In practical terms, what the powersafe write property means is that when
the disk controller detects an impending power loss, it finishes writing
whatever sector it is working on prior to parking the heads.  It means that
individual sector writes run to completion once started, even if
there is a power loss.

<p>Consider what would happen if disk sector writes are interrupted
by a power loss.  If an application writes two or three bytes in the middle
of some file, the operating system will implement this by first reading
the entire sector containing those bytes, making the change to the
sector in memory, then writing the entire sector back to the disk.  If a power
loss occurs during the writeback and the sector was not completely written,
then on the next read after reboot, error correcting codes at the end 
of the sector will probably detect irrepariable damage and the disk 
controller will read out the sector as all zeros or all ones.  Thus
values will have changed outside of the range of the two or three bytes 
that were written at the application level - a violation of the powersafe
overwrite property.

<h2>SQLite Assumptions About Powersafe Overwrite</h2>

<p>All versions of SQLite up to and including version 3.7.9 assume that
the filesystem does <u>not</u> provide powersafe overwrite.  SQLite 
has traditionaly assumed that when any one byte of a file changes, all
other bytes within the same sector of that byte have the potential of
being corrupted on a power loss.  When writing, SQLite has made sure
to journal all bytes in the same sector of any modifications
and it pads journal files out to the next sector boundary so that
subsequent appends to that journal cannot damage prior records.
SQLite understands the sector size to be the value returned by the
xSectorSize method in the [VFS].  The SQLite team has often referred
to the value returned by xSectorSize as the "blast radius" of a write,
since it expresses the range of bytes that might be damaged if a power
loss occurs during the write.
The default [VFSes] for unix and windows have always returned 512 as 
the sector size (or blast radius) for all versions of SQLite up to
and including version 3.7.9.

<p>Newer disk drives have begun using 4096 byte sectors however.  Beginning
with SQLite version 3.7.10, the SQLite development team experimented with 
changes xSectorSize to report 4096 bytes as the blast radius.
This had the effect of increasing write overhead on
many databases.  For a database with a [PRAGMA page_size] of 1024
(a very common choice) making a change to a single page in the database
now requires SQLite to backup three other adjacent pages to the rollback
journal, whereas formerly it only had to backup the one page that was
changing.  In [WAL mode], each transaction had to be padded out to the
next 4096-byte boundary in the WAL file, rather than the next 512-byte
boundary, resulting in thousands of extra bytes needing to be written
per transaction.

<p>The extra write overhead prompted a reexamination of assumptions about
powersafe overwrite.  With modern disk drives, the capacity has become
so large and the data density so great that a single sector is very
small and writing a single sector takes very little time.  We know that
disk drives can detect an impending power loss and continue
to operate for some small amount of time on residual energy because those
drives are able to park their heads before spinning down.  And
so if an impending power loss is detectable by the disk controller, it
seems reasonable that the controller will finish writing
whatever sector it is current working on when the imminent power loss 
is first detected, prior to parking the heads, as long as finishing
work on the sector does not take too long, which it should not with
small and dense sectors.  Hence it seems reasonable
to assume powersafe overwrite for modern disks.  Indeed, BerkeleyDB has
made this assumption for decades, we are told.  Caution is advised
though. As Roger Binns noted on the SQLite develpers mailing list:
"'poorly written' should be the main assumption about drive firmware."

<h2>Changes In SQLite Version 3.7.10</h2>

<p>The [VFS] for SQLite version 3.7.10 adds a new device characteristic 
named [SQLITE_IOCAP_POWERSAFE_OVERWRITE].  Database files that report this
characteristic are assumed to reside on storage systems that have the
powersafe overwrite property.
The default unix and windows [VFSes] now report
[SQLITE_IOCAP_POWERSAFE_OVERWRITE] if SQLite is compiled with
[SQLITE_POWERSAFE_OVERWRITE | -DSQLITE_POWERSAFE_OVERWRITE=1] or they
make the legacy assumption that storage does not have the powersafe
overwrite property if compiled with
[SQLITE_POWERSAFE_OVERWRITE | -DSQLITE_POWERSAFE_OVERWRITE=0].
For now, the default is for powersafe overwrite to be turned on, though
we may revisit this in the future and default it off.

<p>The powersafe overwrite property for individual databases can be
specified as the database is opened using the "psow" query parameter
with a [URI filename].  For example, to always assume powersafe
overwrite for a file (perhaps to ensure maximum write performance), 
open it as

<blockquote>
   file:somefile.db?psow=1
</blockquote>

<p>Or to be extra safe with a database and to force SQLite to assume the
database lacks powersafe overwrite, open it using

<blockquote>
   file:somefile.db?psow=0
</blockquote>

<p>There is also a new [SQLITE_FCNTL_POWERSAFE_OVERWRITE] opcode for
the [sqlite3_file_control()] that allows
an application to query the powersafe overwrite property for a database
file.