Debian Codegraph @

www/psow.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>Powersafe Overwrite</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118
119
120
121 <h1 align="center">Powersafe Overwrite</h1>
122
123 <p>"Powersafe overwrite" is a term used by the SQLite team to describe
124 a behavior of some filesystems and disk-controllers related to
125 data preservation during a power loss. Powersafe overwrite
126 is a boolean property: either the storage system has it or it does not.
127
128 <p>We say that a system has the powersafe overwrite property if the following
129 statement is true:
130
131 <blockquote>
132 <b>When an application writes a range of bytes in a file, no
133 bytes outside of that range will change, even if the write occurs
134 just before a crash or power failure.</b>
135 </blockquote>
136
137 <p>The powersafe overwrite property says nothing about the state of the
138 bytes that were written. Those bytes might contain their old values,
139 their new values, random values, or some combination of these. The powersafe
140 overwrite property merely states that writes cannot change bytes outside
141 of the range of bytes written.
142
143 <p>In other words, powersafe overwrite means that there is no "collateral
144 damage" when a power loss occurs while writing. Only those bytes actually
145 being written might be damaged.
146
147 <p>In practical terms, what the powersafe write property means is that when
148 the disk controller detects an impending power loss, it finishes writing
149 whatever sector it is working on prior to parking the heads. It means that
150 individual sector writes will complete once started, even if
151 there is a power loss.
152
153 <p>Consider what would happen if disk sector writes are interrupted
154 by a power loss. If an application writes two or three bytes in the middle
155 of some file, the operating system will implement this by first reading
156 the entire sector containing those bytes, making the change to the
157 sector in memory, then writing the entire sector back to the disk. If a power
158 loss occurs during the writeback and the sector was not completely written,
159 then on the next read after reboot, error correcting codes
160 in the sector will probably detect irreparable damage and the disk
161 controller will read out the sector as all zeros or all ones. Thus
162 values will have changed outside of the range of the two or three bytes
163 that were written at the application level - a violation of the powersafe
164 overwrite property.
165
166 <h2>SQLite Assumptions About Powersafe Overwrite</h2>
167
168 <p>All versions of SQLite up to and including <a href="releaselog/3_7_9.html">version 3.7.9</a>
169 (2011-11-01) assume that
170 the filesystem does <u>not</u> provide powersafe overwrite. SQLite
171 has traditionally assumed that when any one byte of a file changes, all
172 other bytes within the same sector of that byte have the potential of
173 being corrupted on a power loss. When writing, SQLite has made sure
174 to journal all bytes in the same sector of any modifications
175 and it pads journal files out to the next sector boundary so that
176 subsequent appends to that journal cannot damage prior records.
177 SQLite understands the sector size to be the value returned by the
178 xSectorSize method in the <a href="vfs.html">VFS</a>. The SQLite team has often referred
179 to the value returned by xSectorSize as the "blast radius" of a write,
180 since it expresses the range of bytes that might be damaged if a power
181 loss occurs during the write.
182 The default <a href="vfs.html">VFSes</a> for unix and windows have always returned 512 as
183 the sector size (or blast radius) for all versions of SQLite up to
184 and including version 3.7.9.
185
186 <p>Newer disk drives have begun using 4096 byte sectors however. Beginning
187 with SQLite <a href="releaselog/3_7_10.html">version 3.7.10</a> (2012-01-16),
188 the SQLite development team experimented with
189 changes to 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
198 per transaction.
199
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."
217
218 <a name="tornpage"></a>
219
220 <h2>Torn Pages</h2>
221
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.
228 </p>
229
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>
235
236 <h2>Changes In SQLite Version 3.7.10</h2>
237
238 <p>The <a href="vfs.html">VFS</a> for SQLite <a href="releaselog/3_7_10.html">version 3.7.10</a> (2012-01-16)
239 adds a new device characteristic
240 named <a href="c3ref/c_iocap_atomic.html">SQLITE_IOCAP_POWERSAFE_OVERWRITE</a>. Database files that report this
241 characteristic are assumed to reside on storage systems that have the
242 powersafe overwrite property.
243 The default unix and windows <a href="vfs.html">VFSes</a> now report
244 <a href="c3ref/c_iocap_atomic.html">SQLITE_IOCAP_POWERSAFE_OVERWRITE</a> if SQLite is compiled with
245 <a href="compile.html#powersafe_overwrite">-DSQLITE_POWERSAFE_OVERWRITE=1</a> or they
246 make the legacy assumption that storage does not have the powersafe
247 overwrite property if compiled with
248 <a href="compile.html#powersafe_overwrite">-DSQLITE_POWERSAFE_OVERWRITE=0</a>.
249 For now, the default is for powersafe overwrite to be turned on, though
250 we may revisit this in the future and default it off.
251
252 <p>The powersafe overwrite property for individual databases can be
253 specified as the database is opened using the "psow" query parameter
254 with a <a href="uri.html">URI filename</a>. For example, to always assume powersafe
255 overwrite for a file (perhaps to ensure maximum write performance),
256 open it as
257
258 <blockquote>
259 file:somefile.db?psow=1
260 </blockquote>
261
262 <p>Or to be extra safe with a database and to force SQLite to assume the
263 database lacks powersafe overwrite, open it using
264
265 <blockquote>
266 file:somefile.db?psow=0
267 </blockquote>
268
269 <p>There is also a new <a href="c3ref/c_fcntl_begin_atomic_write.html#sqlitefcntlpowersafeoverwrite">SQLITE_FCNTL_POWERSAFE_OVERWRITE</a> opcode for
270 the <a href="c3ref/file_control.html">sqlite3_file_control()</a> that allows
271 an application to query the powersafe overwrite property for a database
272 file.
273
274 <hr>
275 <h2>Notes:</h2>
276 <ol><li value=1><p>
277 SQLite never assumes atomic page writes <em>in its default configurations</em>.
278 But a custom <a href="vfs.html">VFS</a> can set one of the
279 <a href="c3ref/c_iocap_atomic.html">SQLITE_IOCAP_ATOMIC</a> bits in the result of the xDeviceCharacteristic()
280 method and then SQLite will assume that page writes are atomic. The
281 application must supply a custom VFS to accomplish this, however, since
282 none of the standard VFSes will ever set any of the atomic bits in the
283 xDeviceCharacteristics() vector.
284 </ol>
285 <p align="center"><small><i>This page was last updated on 2025-05-31 13:08:22Z </small></i></p>
286