1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>Powersafe Overwrite</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
123
<h1 align="center">Powersafe Overwrite</h1>
125
<p>"Powersafe overwrite" is a term used by the SQLite team to describe
126
a behavior of some filesystems and disk-controllers related to
127
data preservation during a power loss. Powersafe overwrite
128
is a boolean property: either the storage system has it or it does not.
130
<p>We say that a system has the powersafe overwrite property if the following
134
<b>When an application writes a range of bytes in a file, no
135
bytes outside of that range will change, even if the write occurs
136
just before a crash or power failure.</b>
139
<p>The powersafe overwrite property says nothing about the state of the
140
bytes that were written. Those bytes might contain their old values,
141
their new values, random values, or some combination of these. The powersafe
142
overwrite property merely states that writes cannot change bytes outside
143
of the range of bytes written.
145
<p>In other words, powersafe overwrite means that there is no "collateral
146
damage" when a power loss occurs while writing. Only those bytes actually
147
being written might be damaged.
149
<p>In practical terms, what the powersafe write property means is that when
150
the disk controller detects an impending power loss, it finishes writing
151
whatever sector it is working on prior to parking the heads. It means that
152
individual sector writes will complete once started, even if
153
there is a power loss.
155
<p>Consider what would happen if disk sector writes are interrupted
156
by a power loss. If an application writes two or three bytes in the middle
157
of some file, the operating system will implement this by first reading
158
the entire sector containing those bytes, making the change to the
159
sector in memory, then writing the entire sector back to the disk. If a power
160
loss occurs during the writeback and the sector was not completely written,
161
then on the next read after reboot, error correcting codes
162
in the sector will probably detect irreparable damage and the disk
163
controller will read out the sector as all zeros or all ones. Thus
164
values will have changed outside of the range of the two or three bytes
165
that were written at the application level - a violation of the powersafe
168
<h2>SQLite Assumptions About Powersafe Overwrite</h2>
170
<p>All versions of SQLite up to and including version 3.7.9 assume that
171
the filesystem does <u>not</u> provide powersafe overwrite. SQLite
172
has traditionally assumed that when any one byte of a file changes, all
173
other bytes within the same sector of that byte have the potential of
174
being corrupted on a power loss. When writing, SQLite has made sure
175
to journal all bytes in the same sector of any modifications
176
and it pads journal files out to the next sector boundary so that
177
subsequent appends to that journal cannot damage prior records.
178
SQLite understands the sector size to be the value returned by the
179
xSectorSize method in the <a href="vfs.html">VFS</a>. The SQLite team has often referred
180
to the value returned by xSectorSize as the "blast radius" of a write,
181
since it expresses the range of bytes that might be damaged if a power
182
loss occurs during the write.
183
The default <a href="vfs.html">VFSes</a> for unix and windows have always returned 512 as
184
the sector size (or blast radius) for all versions of SQLite up to
185
and including version 3.7.9.
187
<p>Newer disk drives have begun using 4096 byte sectors however. Beginning
188
with SQLite version 3.7.10, the SQLite development team experimented with
189
changes xSectorSize to report 4096 bytes as the blast radius.
190
This had the effect of increasing write overhead on
191
many databases. For a database with a <a href="pragma.html#pragma_page_size">PRAGMA page_size</a> of 1024
192
(a very common choice) making a change to a single page in the database
193
now requires SQLite to backup three other adjacent pages to the rollback
194
journal, whereas formerly it only had to backup the one page that was
195
changing. In <a href="wal.html">WAL mode</a>, each transaction had to be padded out to the
196
next 4096-byte boundary in the WAL file, rather than the next 512-byte
197
boundary, resulting in thousands of extra bytes being written
200
<p>The extra write overhead prompted a reexamination of assumptions about
201
powersafe overwrite. With modern disk drives, the capacity has become
202
so large and the data density so great that a single sector is very
203
small and writing a single sector takes very little time. We know that
204
disk drives can detect an impending power loss and continue
205
to operate for some small amount of time on residual energy because those
206
drives are able to park their heads before spinning down. And
207
so if an impending power loss is detectable by the disk controller, it
208
seems reasonable that the controller will finish writing
209
whatever sector it is current working on when the imminent power loss
210
is first detected, prior to parking the heads, as long as doing so
211
does not take too long, which it should not with
212
small and dense sectors. Hence it seems reasonable
213
to assume powersafe overwrite for modern disks. Indeed, BerkeleyDB has
214
made this assumption for decades, we are told. Caution is advised
215
though. As Roger Binns noted on the SQLite developers mailing list:
216
"'poorly written' should be the main assumption about drive firmware."
218
<a name="tornpage"></a>
222
<p>A torn page occurs when a database page is larger than a disk sector,
223
the database page is written to disk, but a power loss occurs prior to
224
all sectors of the database page being written. Then, upon recovery, part of
225
the database page will have the old content while some other parts of the
226
page will have the new content. Some database engines assume that
227
page writes are atomic and hence a torn page is an unrecoverable error.
230
<p>SQLite never assumes that database page writes are atomic,
231
regardless of the PSOW setting.<sup>(1)</sup>
232
And hence SQLite is always able to automatically recover from torn pages
233
induced by a crash. Enabling PSOW does not decrease SQLite's ability
234
to recover from a torn page.</p>
236
<h2>Changes In SQLite Version 3.7.10</h2>
238
<p>The <a href="vfs.html">VFS</a> for SQLite version 3.7.10 adds a new device characteristic
239
named <a href="c3ref/c_iocap_atomic.html">SQLITE_IOCAP_POWERSAFE_OVERWRITE</a>. Database files that report this
240
characteristic are assumed to reside on storage systems that have the
241
powersafe overwrite property.
242
The default unix and windows <a href="vfs.html">VFSes</a> now report
243
<a href="c3ref/c_iocap_atomic.html">SQLITE_IOCAP_POWERSAFE_OVERWRITE</a> if SQLite is compiled with
244
<a href="compile.html#powersafe_overwrite">-DSQLITE_POWERSAFE_OVERWRITE=1</a> or they
245
make the legacy assumption that storage does not have the powersafe
246
overwrite property if compiled with
247
<a href="compile.html#powersafe_overwrite">-DSQLITE_POWERSAFE_OVERWRITE=0</a>.
248
For now, the default is for powersafe overwrite to be turned on, though
249
we may revisit this in the future and default it off.
251
<p>The powersafe overwrite property for individual databases can be
252
specified as the database is opened using the "psow" query parameter
253
with a <a href="uri.html">URI filename</a>. For example, to always assume powersafe
254
overwrite for a file (perhaps to ensure maximum write performance),
258
file:somefile.db?psow=1
261
<p>Or to be extra safe with a database and to force SQLite to assume the
262
database lacks powersafe overwrite, open it using
265
file:somefile.db?psow=0
268
<p>There is also a new <a href="c3ref/c_fcntl_chunk_size.html#sqlitefcntlpowersafeoverwrite">SQLITE_FCNTL_POWERSAFE_OVERWRITE</a> opcode for
269
the <a href="c3ref/file_control.html">sqlite3_file_control()</a> that allows
270
an application to query the powersafe overwrite property for a database
276
SQLite never assumes atomic page writes <em>in its default configurations</em>.
277
But a custom <a href="vfs.html">VFS</a> can set one of the
278
<a href="c3ref/c_iocap_atomic.html">SQLITE_IOCAP_ATOMIC</a> bits in the result of the xDeviceCharacteristic()
279
method and then SQLite will assume that page writes are atomic. The
280
application must supply a custom VFS to accomplish this, however, since
281
none of the standard VFSes will ever set any of the atomic bits in the
282
xDeviceCharacteristics() vector.
added
removed
psow.html
