| 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>Atomic Commit In SQLite</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 |
<div class=fancy> |
| 119 |
<div class=nosearch> |
| 120 |
<div class="fancy_title"> |
| 121 |
Atomic Commit In SQLite |
| 122 |
</div> |
| 123 |
<details class="fancy_toc"> |
| 124 |
<summary>Table Of Contents</summary> |
| 125 |
<div id="toc_sub"><div class="fancy-toc1"><a href="#_introduction">1. Introduction</a></div> |
| 126 |
<div class="fancy-toc1"><a href="#_hardware_assumptions">2. Hardware Assumptions</a></div> |
| 127 |
<div class="fancy-toc1"><a href="#_single_file_commit">3. Single File Commit</a></div> |
| 128 |
<div class="fancy-toc2"><a href="#_initial_state">3.1. Initial State</a></div> |
| 129 |
<div class="fancy-toc2"><a href="#_acquiring_a_read_lock">3.2. Acquiring A Read Lock</a></div> |
| 130 |
<div class="fancy-toc2"><a href="#_reading_information_out_of_the_database">3.3. Reading Information Out Of The Database</a></div> |
| 131 |
<div class="fancy-toc2"><a href="#_obtaining_a_reserved_lock">3.4. Obtaining A Reserved Lock</a></div> |
| 132 |
<div class="fancy-toc2"><a href="#_creating_a_rollback_journal_file">3.5. Creating A Rollback Journal File</a></div> |
| 133 |
<div class="fancy-toc2"><a href="#_changing_database_pages_in_user_space">3.6. Changing Database Pages In User Space</a></div> |
| 134 |
<div class="fancy-toc2"><a href="#_flushing_the_rollback_journal_file_to_mass_storage">3.7. Flushing The Rollback Journal File To Mass Storage</a></div> |
| 135 |
<div class="fancy-toc2"><a href="#_obtaining_an_exclusive_lock">3.8. Obtaining An Exclusive Lock</a></div> |
| 136 |
<div class="fancy-toc2"><a href="#_writing_changes_to_the_database_file">3.9. Writing Changes To The Database File</a></div> |
| 137 |
<div class="fancy-toc2"><a href="#flushing_changes_to_mass_storage">3.10. Flushing Changes To Mass Storage</a></div> |
| 138 |
<div class="fancy-toc2"><a href="#deleting_the_rollback_journal">3.11. Deleting The Rollback Journal</a></div> |
| 139 |
<div class="fancy-toc2"><a href="#releasing_the_lock">3.12. Releasing The Lock</a></div> |
| 140 |
<div class="fancy-toc1"><a href="#_rollback">4. Rollback</a></div> |
| 141 |
<div class="fancy-toc2"><a href="#_when_something_goes_wrong_">4.1. When Something Goes Wrong...</a></div> |
| 142 |
<div class="fancy-toc2"><a href="#_hot_rollback_journals">4.2. Hot Rollback Journals</a></div> |
| 143 |
<div class="fancy-toc2"><a href="#_obtaining_an_exclusive_lock_on_the_database">4.3. Obtaining An Exclusive Lock On The Database</a></div> |
| 144 |
<div class="fancy-toc2"><a href="#_rolling_back_incomplete_changes">4.4. Rolling Back Incomplete Changes</a></div> |
| 145 |
<div class="fancy-toc2"><a href="#_deleting_the_hot_journal">4.5. Deleting The Hot Journal</a></div> |
| 146 |
<div class="fancy-toc2"><a href="#_continue_as_if_the_uncompleted_writes_had_never_happened">4.6. Continue As If The Uncompleted Writes Had Never Happened</a></div> |
| 147 |
<div class="fancy-toc1"><a href="#_multi_file_commit">5. Multi-file Commit</a></div> |
| 148 |
<div class="fancy-toc2"><a href="#_separate_rollback_journals_for_each_database">5.1. Separate Rollback Journals For Each Database</a></div> |
| 149 |
<div class="fancy-toc2"><a href="#_the_super_journal_file">5.2. The Super-Journal File</a></div> |
| 150 |
<div class="fancy-toc2"><a href="#_updating_rollback_journal_headers">5.3. Updating Rollback Journal Headers</a></div> |
| 151 |
<div class="fancy-toc2"><a href="#_updating_the_database_files">5.4. Updating The Database Files</a></div> |
| 152 |
<div class="fancy-toc2"><a href="#_delete_the_super_journal_file">5.5. Delete The Super-Journal File</a></div> |
| 153 |
<div class="fancy-toc2"><a href="#_clean_up_the_rollback_journals">5.6. Clean Up The Rollback Journals</a></div> |
| 154 |
<div class="fancy-toc1"><a href="#_additional_details_of_the_commit_process">6. Additional Details Of The Commit Process</a></div> |
| 155 |
<div class="fancy-toc2"><a href="#_always_journal_complete_sectors">6.1. Always Journal Complete Sectors</a></div> |
| 156 |
<div class="fancy-toc2"><a href="#_dealing_with_garbage_written_into_journal_files">6.2. Dealing With Garbage Written Into Journal Files</a></div> |
| 157 |
<div class="fancy-toc2"><a href="#_cache_spill_prior_to_commit">6.3. Cache Spill Prior To Commit</a></div> |
| 158 |
<div class="fancy-toc1"><a href="#_optimizations">7. Optimizations</a></div> |
| 159 |
<div class="fancy-toc2"><a href="#_cache_retained_between_transactions">7.1. Cache Retained Between Transactions</a></div> |
| 160 |
<div class="fancy-toc2"><a href="#_exclusive_access_mode">7.2. Exclusive Access Mode</a></div> |
| 161 |
<div class="fancy-toc2"><a href="#_do_not_journal_freelist_pages">7.3. Do Not Journal Freelist Pages</a></div> |
| 162 |
<div class="fancy-toc2"><a href="#_single_page_updates_and_atomic_sector_writes">7.4. Single Page Updates And Atomic Sector Writes</a></div> |
| 163 |
<div class="fancy-toc2"><a href="#_filesystems_with_safe_append_semantics">7.5. Filesystems With Safe Append Semantics</a></div> |
| 164 |
<div class="fancy-toc2"><a href="#_persistent_rollback_journals">7.6. Persistent Rollback Journals</a></div> |
| 165 |
<div class="fancy-toc1"><a href="#_testing_atomic_commit_behavior">8. Testing Atomic Commit Behavior</a></div> |
| 166 |
<div class="fancy-toc1"><a href="#_things_that_can_go_wrong">9. Things That Can Go Wrong</a></div> |
| 167 |
<div class="fancy-toc2"><a href="#_broken_locking_implementations">9.1. Broken Locking Implementations</a></div> |
| 168 |
<div class="fancy-toc2"><a href="#_incomplete_disk_flushes">9.2. Incomplete Disk Flushes</a></div> |
| 169 |
<div class="fancy-toc2"><a href="#_partial_file_deletions">9.3. Partial File Deletions</a></div> |
| 170 |
<div class="fancy-toc2"><a href="#_garbage_written_into_files">9.4. Garbage Written Into Files</a></div> |
| 171 |
<div class="fancy-toc2"><a href="#_deleting_or_renaming_a_hot_journal">9.5. Deleting Or Renaming A Hot Journal</a></div> |
| 172 |
<div class="fancy-toc1"><a href="#_future_directions_and_conclusion">10. Future Directions And Conclusion</a></div> |
| 173 |
</div> |
| 174 |
</details> |
| 175 |
</div> |
| 176 |
|
| 177 |
|
| 178 |
|
| 179 |
|
| 180 |
<h1 id="_introduction"><span>1. </span> Introduction</h1> |
| 181 |
|
| 182 |
<p>An important feature of transactional databases like SQLite |
| 183 |
is "atomic commit". |
| 184 |
Atomic commit means that either all database changes within a single |
| 185 |
transaction occur or none of them occur. With atomic commit, it |
| 186 |
is as if many different writes to different sections of the database |
| 187 |
file occur instantaneously and simultaneously. |
| 188 |
Real hardware serializes writes to mass storage, and writing |
| 189 |
a single sector takes a finite amount of time. |
| 190 |
So it is impossible to truly write many different sectors of a |
| 191 |
database file simultaneously and/or instantaneously. |
| 192 |
But the atomic commit logic within |
| 193 |
SQLite makes it appear as if the changes for a transaction |
| 194 |
are all written instantaneously and simultaneously.</p> |
| 195 |
|
| 196 |
<p>SQLite has the important property that transactions appear |
| 197 |
to be atomic even if the transaction is interrupted by an |
| 198 |
operating system crash or power failure.</p> |
| 199 |
|
| 200 |
<p>This article describes the techniques used by SQLite to create the |
| 201 |
illusion of atomic commit.</p> |
| 202 |
|
| 203 |
<p>The information in this article applies only when SQLite is operating |
| 204 |
in "rollback mode", or in other words when SQLite is not |
| 205 |
using a <a href="wal.html">write-ahead log</a>. SQLite still supports atomic commit when |
| 206 |
write-ahead logging is enabled, but it accomplishes atomic commit by |
| 207 |
a different mechanism from the one described in this article. See |
| 208 |
the <a href="wal.html">write-ahead log documentation</a> for additional information on how |
| 209 |
SQLite supports atomic commit in that context.</p> |
| 210 |
|
| 211 |
<a name="hardware"></a> |
| 212 |
|
| 213 |
<h1 id="_hardware_assumptions"><span>2. </span> Hardware Assumptions</h1> |
| 214 |
|
| 215 |
<p>Throughout this article, we will call the mass storage device "disk" |
| 216 |
even though the mass storage device might really be flash memory.</p> |
| 217 |
|
| 218 |
<p>We assume that disk is written in chunks which we call a "sector". |
| 219 |
It is not possible to modify any part of the disk smaller than a sector. |
| 220 |
To change a part of the disk smaller than a sector, you have to read in |
| 221 |
the full sector that contains the part you want to change, make the |
| 222 |
change, then write back out the complete sector.</p> |
| 223 |
|
| 224 |
<p>On a traditional spinning disk, a sector is the minimum unit of transfer |
| 225 |
in both directions, both reading and writing. On flash memory, however, |
| 226 |
the minimum size of a read is typically much smaller than a minimum write. |
| 227 |
SQLite is only concerned with the minimum write amount and so for the |
| 228 |
purposes of this article, when we say "sector" we mean the minimum amount |
| 229 |
of data that can be written to mass storage in a single go.</p> |
| 230 |
|
| 231 |
<p> |
| 232 |
Prior to SQLite version 3.3.14, a sector size of 512 bytes was |
| 233 |
assumed in all cases. There was a compile-time option to change |
| 234 |
this but the code had never been tested with a larger value. The |
| 235 |
512 byte sector assumption seemed reasonable since until very recently |
| 236 |
all disk drives used a 512 byte sector internally. However, there |
| 237 |
has recently been a push to increase the sector size of disks to |
| 238 |
4096 bytes. Also the sector size |
| 239 |
for flash memory is usually larger than 512 bytes. For these reasons, |
| 240 |
versions of SQLite beginning with 3.3.14 have a method in the OS |
| 241 |
interface layer that interrogates the underlying filesystem to find |
| 242 |
the true sector size. As currently implemented (version 3.5.0) this |
| 243 |
method still returns a hard-coded value of 512 bytes, since there |
| 244 |
is no standard way of discovering the true sector size on either |
| 245 |
Unix or Windows. But the method is available for embedded device |
| 246 |
manufacturers to tweak according to their own needs. And we have |
| 247 |
left open the possibility of filling in a more meaningful implementation |
| 248 |
on Unix and Windows in the future.</p> |
| 249 |
|
| 250 |
<p>SQLite has traditionally assumed that a sector write is <u>not</u> atomic. |
| 251 |
However, SQLite does always assume that a sector write is linear. By "linear" |
| 252 |
we mean that SQLite assumes that when writing a sector, the hardware begins |
| 253 |
at one end of the data and writes byte by byte until it gets to |
| 254 |
the other end. The write might go from beginning to end or from |
| 255 |
end to beginning. If a power failure occurs in the middle of a |
| 256 |
sector write it might be that part of the sector was modified |
| 257 |
and another part was left unchanged. The key assumption by SQLite |
| 258 |
is that if any part of the sector gets changed, then either the |
| 259 |
first or the last bytes will be changed. So the hardware will |
| 260 |
never start writing a sector in the middle and work towards the |
| 261 |
ends. We do not know if this assumption is always true but it |
| 262 |
seems reasonable.</p> |
| 263 |
|
| 264 |
<p>The previous paragraph states that SQLite does not assume that |
| 265 |
sector writes are atomic. This is true by default. But as of |
| 266 |
SQLite version 3.5.0, there is a new interface called the |
| 267 |
Virtual File System (<a href="vfs.html">VFS</a>) interface. The <a href="vfs.html">VFS</a> is the only means |
| 268 |
by which SQLite communicates to the underlying filesystem. The |
| 269 |
code comes with default VFS implementations for Unix and Windows |
| 270 |
and there is a mechanism for creating new custom VFS implementations |
| 271 |
at runtime. In this new VFS interface there is a method called |
| 272 |
xDeviceCharacteristics. This method interrogates the underlying |
| 273 |
filesystem to discover various properties and behaviors that the |
| 274 |
filesystem may or may not exhibit. The xDeviceCharacteristics |
| 275 |
method might indicate that sector writes are atomic, and if it does |
| 276 |
so indicate, SQLite will try to take advantage of that fact. But |
| 277 |
the default xDeviceCharacteristics method for both Unix and Windows |
| 278 |
does not indicate atomic sector writes and so these optimizations |
| 279 |
are normally omitted.</p> |
| 280 |
|
| 281 |
<p>SQLite assumes that the operating system will buffer writes and |
| 282 |
that a write request will return before data has actually been stored |
| 283 |
in the mass storage device. |
| 284 |
SQLite further assumes that write operations will be reordered by |
| 285 |
the operating system. |
| 286 |
For this reason, SQLite does a "flush" or "fsync" operation at key |
| 287 |
points. SQLite assumes that the flush or fsync will not return until |
| 288 |
all pending write operations for the file that is being flushed have |
| 289 |
completed. We are told that the flush and fsync primitives |
| 290 |
are broken on some versions of Windows and Linux. This is unfortunate. |
| 291 |
It opens SQLite up to the possibility of database corruption following |
| 292 |
a power loss in the middle of a commit. However, there is nothing |
| 293 |
that SQLite can do to test for or remedy the situation. SQLite |
| 294 |
assumes that the operating system that it is running on works as |
| 295 |
advertised. If that is not quite the case, well then hopefully you |
| 296 |
will not lose power too often.</p> |
| 297 |
|
| 298 |
<p>SQLite assumes that when a file grows in length that the new |
| 299 |
file space originally contains garbage and then later is filled in |
| 300 |
with the data actually written. In other words, SQLite assumes that |
| 301 |
the file size is updated before the file content. This is a |
| 302 |
pessimistic assumption and SQLite has to do some extra work to make |
| 303 |
sure that it does not cause database corruption if power is lost |
| 304 |
between the time when the file size is increased and when the |
| 305 |
new content is written. The xDeviceCharacteristics method of |
| 306 |
the <a href="vfs.html">VFS</a> might indicate that the filesystem will always write the |
| 307 |
data before updating the file size. (This is the |
| 308 |
SQLITE_IOCAP_SAFE_APPEND property for those readers who are looking |
| 309 |
at the code.) When the xDeviceCharacteristics method indicates |
| 310 |
that files content is written before the file size is increased, |
| 311 |
SQLite can forego some of its pedantic database protection steps |
| 312 |
and thereby decrease the amount of disk I/O needed to perform a |
| 313 |
commit. The current implementation, however, makes no such assumptions |
| 314 |
for the default VFSes for Windows and Unix.</p> |
| 315 |
|
| 316 |
<p>SQLite assumes that a file deletion is atomic from the |
| 317 |
point of view of a user process. By this we mean that if SQLite |
| 318 |
requests that a file be deleted and the power is lost during the |
| 319 |
delete operation, once power is restored either the file will |
| 320 |
exist completely with all if its original content unaltered, or |
| 321 |
else the file will not be seen in the filesystem at all. If |
| 322 |
after power is restored the file is only partially deleted, |
| 323 |
if some of its data has been altered or erased, |
| 324 |
or the file has been truncated but not completely removed, then |
| 325 |
database corruption will likely result.</p> |
| 326 |
|
| 327 |
<p>SQLite assumes that the detection and/or correction of |
| 328 |
bit errors caused by cosmic rays, thermal noise, quantum |
| 329 |
fluctuations, device driver bugs, or other mechanisms, is the |
| 330 |
responsibility of the underlying hardware and operating system. |
| 331 |
SQLite does not add any redundancy to the database file for |
| 332 |
the purpose of detecting corruption or I/O errors. |
| 333 |
SQLite assumes that the data it reads is exactly the same data |
| 334 |
that it previously wrote.</p> |
| 335 |
|
| 336 |
<p>By default, SQLite assumes that an operating system call to write |
| 337 |
a range of bytes will not damage or alter any bytes outside of that range |
| 338 |
even if a power loss or OS crash occurs during that write. We |
| 339 |
call this the "<a href="psow.html">powersafe overwrite</a>" property. |
| 340 |
Prior to <a href="releaselog/3_7_9.html">version 3.7.9</a> (2011-11-01), |
| 341 |
SQLite did not assume powersafe overwrite. But with the standard |
| 342 |
sector size increasing from 512 to 4096 bytes on most disk drives, it |
| 343 |
has become necessary to assume powersafe overwrite in order to maintain |
| 344 |
historical performance levels and so powersafe overwrite is assumed by |
| 345 |
default in recent versions of SQLite. The assumption of powersafe |
| 346 |
overwrite property can be disabled at compile-time or at run-time if |
| 347 |
desired. See the <a href="psow.html">powersafe overwrite documentation</a> for further |
| 348 |
details. |
| 349 |
|
| 350 |
|
| 351 |
<a name="section_3_0"></a> |
| 352 |
</p><h1 id="_single_file_commit"><span>3. </span> Single File Commit</h1> |
| 353 |
|
| 354 |
<p>We begin with an overview of the steps SQLite takes in order to |
| 355 |
perform an atomic commit of a transaction against a single database |
| 356 |
file. The details of file formats used to guard against damage from |
| 357 |
power failures and techniques for performing an atomic commit across |
| 358 |
multiple databases are discussed in later sections.</p> |
| 359 |
|
| 360 |
<a name="initstate"></a> |
| 361 |
|
| 362 |
<h2 id="_initial_state"><span>3.1. </span> Initial State</h2> |
| 363 |
|
| 364 |
<img src="images/ac/commit-0.gif" align="right" hspace="15"> |
| 365 |
|
| 366 |
<p>The state of the computer when a database connection is |
| 367 |
first opened is shown conceptually by the diagram at the |
| 368 |
right. |
| 369 |
The area of the diagram on the extreme right (labeled "Disk") represents |
| 370 |
information stored on the mass storage device. Each rectangle is |
| 371 |
a sector. The blue color represents that the sectors contain |
| 372 |
original data. |
| 373 |
The middle area is the operating systems disk cache. At the |
| 374 |
onset of our example, the cache is cold and this is represented |
| 375 |
by leaving the rectangles of the disk cache empty. |
| 376 |
The left area of the diagram shows the content of memory for |
| 377 |
the process that is using SQLite. The database connection has |
| 378 |
just been opened and no information has been read yet, so the |
| 379 |
user space is empty. |
| 380 |
</p> |
| 381 |
<br clear="both"> |
| 382 |
|
| 383 |
<a name="rdlck"></a> |
| 384 |
|
| 385 |
<a name="section_3_2"></a> |
| 386 |
<h2 id="_acquiring_a_read_lock"><span>3.2. </span> Acquiring A Read Lock</h2> |
| 387 |
|
| 388 |
<img src="images/ac/commit-1.gif" align="right" hspace="15"> |
| 389 |
|
| 390 |
<p>Before SQLite can write to a database, it must first read |
| 391 |
the database to see what is there already. Even if it is just |
| 392 |
appending new data, SQLite still has to read in the database |
| 393 |
schema from the "<a href="schematab.html">sqlite_schema</a>" table so that it can know |
| 394 |
how to parse the INSERT statements and discover where in the |
| 395 |
database file the new information should be stored.</p> |
| 396 |
|
| 397 |
<p>The first step toward reading from the database file |
| 398 |
is obtaining a shared lock on the database file. A "shared" |
| 399 |
lock allows two or more database connections to read from the |
| 400 |
database file at the same time. But a shared lock prevents |
| 401 |
another database connection from writing to the database file |
| 402 |
while we are reading it. This is necessary because if another |
| 403 |
database connection were writing to the database file at the |
| 404 |
same time we are reading from the database file, we might read |
| 405 |
some data before the change and other data after the change. |
| 406 |
This would make it appear as if the change made by the other |
| 407 |
process is not atomic.</p> |
| 408 |
|
| 409 |
<p>Notice that the shared lock is on the operating system |
| 410 |
disk cache, not on the disk itself. File locks |
| 411 |
really are just flags within the operating system kernel, |
| 412 |
usually. (The details depend on the specific OS layer |
| 413 |
interface.) Hence, the lock will instantly vanish if the |
| 414 |
operating system crashes or if there is a power loss. It |
| 415 |
is usually also the case that the lock will vanish if the |
| 416 |
process that created the lock exits.</p> |
| 417 |
|
| 418 |
<br clear="both"> |
| 419 |
|
| 420 |
<a name="section_3_3"></a> |
| 421 |
<h2 id="_reading_information_out_of_the_database"><span>3.3. </span> Reading Information Out Of The Database</h2> |
| 422 |
|
| 423 |
<img src="images/ac/commit-2.gif" align="right" hspace="15"> |
| 424 |
|
| 425 |
<p>After the shared lock is acquired, we can begin reading |
| 426 |
information from the database file. In this scenario, we |
| 427 |
are assuming a cold cache, so information must first be |
| 428 |
read from mass storage into the operating system cache then |
| 429 |
transferred from operating system cache into user space. |
| 430 |
On subsequent reads, some or all of the information might |
| 431 |
already be found in the operating system cache and so only |
| 432 |
the transfer to user space would be required.</p> |
| 433 |
|
| 434 |
<p>Usually only a subset of the pages in the database file |
| 435 |
are read. In this example we are showing three |
| 436 |
pages out of eight being read. In a typical application, a |
| 437 |
database will have thousands of pages and a query will normally |
| 438 |
only touch a small percentage of those pages.</p> |
| 439 |
|
| 440 |
<br clear="both"> |
| 441 |
|
| 442 |
<a name="rsvdlock"></a> |
| 443 |
|
| 444 |
<h2 id="_obtaining_a_reserved_lock"><span>3.4. </span> Obtaining A Reserved Lock</h2> |
| 445 |
|
| 446 |
<img src="images/ac/commit-3.gif" align="right" hspace="15"> |
| 447 |
|
| 448 |
<p>Before making changes to the database, SQLite first |
| 449 |
obtains a "reserved" lock on the database file. A reserved |
| 450 |
lock is similar to a shared lock in that both a reserved lock |
| 451 |
and shared lock allow other processes to read from the database |
| 452 |
file. A single reserve lock can coexist with multiple shared |
| 453 |
locks from other processes. However, there can only be a |
| 454 |
single reserved lock on the database file. Hence only a |
| 455 |
single process can be attempting to write to the database |
| 456 |
at one time.</p> |
| 457 |
|
| 458 |
<p>The idea behind a reserved lock is that it signals that |
| 459 |
a process intends to modify the database file in the near |
| 460 |
future but has not yet started to make the modifications. |
| 461 |
And because the modifications have not yet started, other |
| 462 |
processes can continue to read from the database. However, |
| 463 |
no other process should also begin trying to write to the |
| 464 |
database.</p> |
| 465 |
|
| 466 |
<br clear="both"> |
| 467 |
<a name="section_3_5"></a> |
| 468 |
<h2 id="_creating_a_rollback_journal_file"><span>3.5. </span> Creating A Rollback Journal File</h2> |
| 469 |
<img src="images/ac/commit-4.gif" align="right" hspace="15"> |
| 470 |
|
| 471 |
<p>Prior to making any changes to the database file, SQLite first |
| 472 |
creates a separate rollback journal file and writes into the |
| 473 |
rollback journal the original |
| 474 |
content of the database pages that are to be altered. |
| 475 |
The idea behind the rollback journal is that it contains |
| 476 |
all information needed to restore the database back to |
| 477 |
its original state.</p> |
| 478 |
|
| 479 |
<p>The rollback journal contains a small header (shown in green |
| 480 |
in the diagram) that records the original size of the database |
| 481 |
file. So if a change causes the database file to grow, we |
| 482 |
will still know the original size of the database. The page |
| 483 |
number is stored together with each database page that is |
| 484 |
written into the rollback journal.</p> |
| 485 |
|
| 486 |
<p> |
| 487 |
When a new file is created, most desktop operating systems |
| 488 |
(Windows, Linux, Mac OS X) will not actually write anything to |
| 489 |
disk. The new file is created in the operating systems disk |
| 490 |
cache only. The file is not created on mass storage until sometime |
| 491 |
later, when the operating system has a spare moment. This creates |
| 492 |
the impression to users that I/O is happening much faster than |
| 493 |
is possible when doing real disk I/O. We illustrate this idea in |
| 494 |
the diagram to the right by showing that the new rollback journal |
| 495 |
appears in the operating system disk cache only and not on the |
| 496 |
disk itself.</p> |
| 497 |
|
| 498 |
<br clear="both"> |
| 499 |
<a name="section_3_6"></a> |
| 500 |
<h2 id="_changing_database_pages_in_user_space"><span>3.6. </span> Changing Database Pages In User Space</h2> |
| 501 |
<img src="images/ac/commit-5.gif" align="right" hspace="15"> |
| 502 |
|
| 503 |
<p>After the original page content has been saved in the rollback |
| 504 |
journal, the pages can be modified in user memory. Each database |
| 505 |
connection has its own private copy of user space, so the changes |
| 506 |
that are made in user space are only visible to the database connection |
| 507 |
that is making the changes. Other database connections still see |
| 508 |
the information in operating system disk cache buffers which have |
| 509 |
not yet been changed. And so even though one process is busy |
| 510 |
modifying the database, other processes can continue to read their |
| 511 |
own copies of the original database content.</p> |
| 512 |
|
| 513 |
<br clear="both"> |
| 514 |
<a name="section_3_7"></a> |
| 515 |
<h2 id="_flushing_the_rollback_journal_file_to_mass_storage"><span>3.7. </span> Flushing The Rollback Journal File To Mass Storage</h2> |
| 516 |
<img src="images/ac/commit-6.gif" align="right" hspace="15"> |
| 517 |
|
| 518 |
<p>The next step is to flush the content of the rollback journal |
| 519 |
file to nonvolatile storage. |
| 520 |
As we will see later, |
| 521 |
this is a critical step in insuring that the database can survive |
| 522 |
an unexpected power loss. |
| 523 |
This step also takes a lot of time, since writing to nonvolatile |
| 524 |
storage is normally a slow operation.</p> |
| 525 |
|
| 526 |
<p>This step is usually more complicated than simply flushing |
| 527 |
the rollback journal to the disk. On most platforms two separate |
| 528 |
flush (or fsync()) operations are required. The first flush writes |
| 529 |
out the base rollback journal content. Then the header of the |
| 530 |
rollback journal is modified to show the number of pages in the |
| 531 |
rollback journal. Then the header is flushed to disk. The details |
| 532 |
on why we do this header modification and extra flush are provided |
| 533 |
in a later section of this paper.</p> |
| 534 |
|
| 535 |
<br clear="both"> |
| 536 |
<a name="section_3_8"></a> |
| 537 |
<h2 id="_obtaining_an_exclusive_lock"><span>3.8. </span> Obtaining An Exclusive Lock</h2> |
| 538 |
<img src="images/ac/commit-7.gif" align="right" hspace="15"> |
| 539 |
|
| 540 |
<p>Prior to making changes to the database file itself, we must |
| 541 |
obtain an exclusive lock on the database file. Obtaining an |
| 542 |
exclusive lock is really a two-step process. First SQLite obtains |
| 543 |
a "pending" lock. Then it escalates the pending lock to an |
| 544 |
exclusive lock.</p> |
| 545 |
|
| 546 |
<p>A pending lock allows other processes that already have a |
| 547 |
shared lock to continue reading the database file. But it |
| 548 |
prevents new shared locks from being established. The idea |
| 549 |
behind a pending lock is to prevent writer starvation caused |
| 550 |
by a large pool of readers. There might be dozens, even hundreds, |
| 551 |
of other processes trying to read the database file. Each process |
| 552 |
acquires a shared lock before it starts reading, reads what it |
| 553 |
needs, then releases the shared lock. If, however, there are |
| 554 |
many different processes all reading from the same database, it |
| 555 |
might happen that a new process always acquires its shared lock before |
| 556 |
the previous process releases its shared lock. And so there is |
| 557 |
never an instant when there are no shared locks on the database |
| 558 |
file and hence there is never an opportunity for the writer to |
| 559 |
seize the exclusive lock. A pending lock is designed to prevent |
| 560 |
that cycle by allowing existing shared locks to proceed but |
| 561 |
blocking new shared locks from being established. Eventually |
| 562 |
all shared locks will clear and the pending lock will then be |
| 563 |
able to escalate into an exclusive lock.</p> |
| 564 |
|
| 565 |
<br clear="both"> |
| 566 |
<a name="section_3_9"></a> |
| 567 |
<h2 id="_writing_changes_to_the_database_file"><span>3.9. </span> Writing Changes To The Database File</h2> |
| 568 |
<img src="images/ac/commit-8.gif" align="right" hspace="15"> |
| 569 |
|
| 570 |
<p>Once an exclusive lock is held, we know that no other |
| 571 |
processes are reading from the database file and it is |
| 572 |
safe to write changes into the database file. Usually |
| 573 |
those changes only go as far as the operating systems disk |
| 574 |
cache and do not make it all the way to mass storage.</p> |
| 575 |
|
| 576 |
<br clear="both"> |
| 577 |
<a name="section_3_10"></a> |
| 578 |
<h2 id="flushing_changes_to_mass_storage"><span>3.10. </span>Flushing Changes To Mass Storage</h2> |
| 579 |
<img src="images/ac/commit-9.gif" align="right" hspace="15"> |
| 580 |
|
| 581 |
<p>Another flush must occur to make sure that all the |
| 582 |
database changes are written into nonvolatile storage. |
| 583 |
This is a critical step to ensure that the database will |
| 584 |
survive a power loss without damage. However, because |
| 585 |
of the inherent slowness of writing to disk or flash memory, |
| 586 |
this step together with the rollback journal file flush in section |
| 587 |
3.7 above takes up most of the time required to complete a |
| 588 |
transaction commit in SQLite.</p> |
| 589 |
|
| 590 |
<br clear="both"> |
| 591 |
<a name="section_3_11"></a> |
| 592 |
<h2 id="deleting_the_rollback_journal"><span>3.11. </span>Deleting The Rollback Journal</h2> |
| 593 |
<img src="images/ac/commit-A.gif" align="right" hspace="15"> |
| 594 |
|
| 595 |
<p>After the database changes are all safely on the mass |
| 596 |
storage device, the rollback journal file is deleted. |
| 597 |
This is the instant where the transaction commits. |
| 598 |
If a power failure or system crash occurs prior to this |
| 599 |
point, then recovery processes to be described later make |
| 600 |
it appear as if no changes were ever made to the database |
| 601 |
file. If a power failure or system crash occurs after |
| 602 |
the rollback journal is deleted, then it appears as if |
| 603 |
all changes have been written to disk. Thus, SQLite gives |
| 604 |
the appearance of having made no changes to the database |
| 605 |
file or having made the complete set of changes to the |
| 606 |
database file depending on whether or not the rollback |
| 607 |
journal file exists.</p> |
| 608 |
|
| 609 |
<p>Deleting a file is not really an atomic operation, but |
| 610 |
it appears to be from the point of view of a user process. |
| 611 |
A process is always able to ask the operating system "does |
| 612 |
this file exist?" and the process will get back a yes or no |
| 613 |
answer. After a power failure that occurs during a |
| 614 |
transaction commit, SQLite will ask the operating system |
| 615 |
whether or not the rollback journal file exists. If the |
| 616 |
answer is "yes" then the transaction is incomplete and is |
| 617 |
rolled back. If the answer is "no" then it means the transaction |
| 618 |
did commit.</p> |
| 619 |
|
| 620 |
<p>The existence of a transaction depends on whether or |
| 621 |
not the rollback journal file exists and the deletion |
| 622 |
of a file appears to be an atomic operation from the point of |
| 623 |
view of a user-space process. Therefore, |
| 624 |
a transaction appears to be an atomic operation.</p> |
| 625 |
|
| 626 |
<p>The act of deleting a file is expensive on many systems. |
| 627 |
As an optimization, SQLite can be configured to truncate |
| 628 |
the journal file to zero bytes in length |
| 629 |
or overwrite the journal file header with zeros. In either |
| 630 |
case, the resulting journal file is no longer capable of rolling |
| 631 |
back and so the transaction still commits. Truncating a file |
| 632 |
to zero length, like deleting a file, is assumed to be an atomic |
| 633 |
operation from the point of view of a user process. Overwriting |
| 634 |
the header of the journal with zeros is not atomic, but if any |
| 635 |
part of the header is malformed the journal will not roll back. |
| 636 |
Hence, one can say that the commit occurs as soon as the header |
| 637 |
is sufficiently changed to make it invalid. Typically this happens |
| 638 |
as soon as the first byte of the header is zeroed.</p> |
| 639 |
|
| 640 |
<br clear="both"> |
| 641 |
<a name="section_3_12"></a> |
| 642 |
<h2 id="releasing_the_lock"><span>3.12. </span>Releasing The Lock</h2> |
| 643 |
<img src="images/ac/commit-B.gif" align="right" hspace="15"> |
| 644 |
|
| 645 |
<p>The last step in the commit process is to release the |
| 646 |
exclusive lock so that other processes can once again |
| 647 |
start accessing the database file.</p> |
| 648 |
|
| 649 |
<p>In the diagram at the right, we show that the information |
| 650 |
that was held in user space is cleared when the lock is released. |
| 651 |
This used to be literally true for older versions of SQLite. But |
| 652 |
more recent versions of SQLite keep the user space information |
| 653 |
in memory in case it might be needed again at the start of the |
| 654 |
next transaction. It is cheaper to reuse information that is |
| 655 |
already in local memory than to transfer the information back |
| 656 |
from the operating system disk cache or to read it off of the |
| 657 |
disk drive again. Prior to reusing the information in user space, |
| 658 |
we must first reacquire the shared lock and then we have to check |
| 659 |
to make sure that no other process modified the database file while |
| 660 |
we were not holding a lock. There is a counter in the first page |
| 661 |
of the database that is incremented every time the database file |
| 662 |
is modified. We can find out if another process has modified the |
| 663 |
database by checking that counter. If the database was modified, |
| 664 |
then the user space cache must be cleared and reread. But it is |
| 665 |
commonly the case that no changes have been made and the user |
| 666 |
space cache can be reused for a significant performance savings.</p> |
| 667 |
|
| 668 |
<br clear="both"> |
| 669 |
<a name="rollback"></a> |
| 670 |
|
| 671 |
<h1 id="_rollback"><span>4. </span> Rollback</h1> |
| 672 |
|
| 673 |
<p>An atomic commit is supposed to happen instantaneously. But the processing |
| 674 |
described above clearly takes a finite amount of time. |
| 675 |
Suppose the power to the computer were cut |
| 676 |
part way through the commit operation described above. In order |
| 677 |
to maintain the illusion that the changes were instantaneous, we |
| 678 |
have to "rollback" any partial changes and restore the database to |
| 679 |
the state it was in prior to the beginning of the transaction.</p> |
| 680 |
|
| 681 |
<a name="crisis"></a> |
| 682 |
|
| 683 |
<h2 id="_when_something_goes_wrong_"><span>4.1. </span> When Something Goes Wrong...</h2> |
| 684 |
<img src="images/ac/rollback-0.gif" align="right" hspace="15"> |
| 685 |
|
| 686 |
<p>Suppose the power loss occurred |
| 687 |
during <a href="#section_3_10">step 3.10</a> above, |
| 688 |
while the database changes were being written to disk. |
| 689 |
After power is restored, the situation might be something |
| 690 |
like what is shown to the right. We were trying to change |
| 691 |
three pages of the database file but only one page was |
| 692 |
successfully written. Another page was partially written |
| 693 |
and a third page was not written at all.</p> |
| 694 |
|
| 695 |
<p>The rollback journal is complete and intact on disk when |
| 696 |
the power is restored. This is a key point. The reason for |
| 697 |
the flush operation in <a href="#section_3_7">step 3.7</a> |
| 698 |
is to make absolutely sure that |
| 699 |
all of the rollback journal is safely on nonvolatile storage |
| 700 |
prior to making any changes to the database file itself.</p> |
| 701 |
|
| 702 |
<br clear="both"> |
| 703 |
<a name="section_4_2"></a> |
| 704 |
<h2 id="_hot_rollback_journals"><span>4.2. </span> Hot Rollback Journals</h2> |
| 705 |
<img src="images/ac/rollback-1.gif" align="right" hspace="15"> |
| 706 |
|
| 707 |
<p>The first time that any SQLite process attempts to access |
| 708 |
the database file, it obtains a shared lock as described in |
| 709 |
<a href="#section_3_2">section 3.2</a> above. |
| 710 |
But then it notices that there is a |
| 711 |
rollback journal file present. SQLite then checks to see if |
| 712 |
the rollback journal is a "hot journal". A hot journal is |
| 713 |
a rollback journal that needs to be played back in order to |
| 714 |
restore the database to a sane state. A hot journal only |
| 715 |
exists when an earlier process was in the middle of committing |
| 716 |
a transaction when it crashed or lost power.</p> |
| 717 |
|
| 718 |
<p>A rollback journal is a "hot" journal if all of the following |
| 719 |
are true:</p> |
| 720 |
|
| 721 |
<ul> |
| 722 |
<li>The rollback journal exists. |
| 723 |
</li><li>The rollback journal is not an empty file. |
| 724 |
</li><li>There is no reserved lock on the main database file. |
| 725 |
</li><li>The header of the rollback journal is well-formed and in particular |
| 726 |
has not been zeroed out. |
| 727 |
</li><li>The rollback journal does not |
| 728 |
contain the name of a super-journal file (see |
| 729 |
<a href="#section_5_5">section 5.5</a> below) or if does |
| 730 |
contain the name of a super-journal, then that super-journal |
| 731 |
file exists. |
| 732 |
</li></ul> |
| 733 |
|
| 734 |
<p>The presence of a hot journal is our indication |
| 735 |
that a previous process was trying to commit a transaction but |
| 736 |
it aborted for some reason prior to the completion of the |
| 737 |
commit. A hot journal means that |
| 738 |
the database file is in an inconsistent state and needs to |
| 739 |
be repaired (by rollback) prior to being used.</p> |
| 740 |
|
| 741 |
<br clear="both"> |
| 742 |
<a name="exlock"></a> |
| 743 |
|
| 744 |
<h2 id="_obtaining_an_exclusive_lock_on_the_database"><span>4.3. </span> Obtaining An Exclusive Lock On The Database</h2> |
| 745 |
<img src="images/ac/rollback-2.gif" align="right" hspace="15"> |
| 746 |
|
| 747 |
<p>The first step toward dealing with a hot journal is to |
| 748 |
obtain an exclusive lock on the database file. This prevents two |
| 749 |
or more processes from trying to rollback the same hot journal |
| 750 |
at the same time.</p> |
| 751 |
|
| 752 |
<br clear="both"> |
| 753 |
<a name="section_4_4"></a> |
| 754 |
<h2 id="_rolling_back_incomplete_changes"><span>4.4. </span> Rolling Back Incomplete Changes</h2> |
| 755 |
<img src="images/ac/rollback-3.gif" align="right" hspace="15"> |
| 756 |
|
| 757 |
<p>Once a process obtains an exclusive lock, it is permitted |
| 758 |
to write to the database file. It then proceeds to read the |
| 759 |
original content of pages out of the rollback journal and write |
| 760 |
that content back to where it came from in the database file. |
| 761 |
Recall that the header of the rollback journal records the original |
| 762 |
size of the database file prior to the start of the aborted |
| 763 |
transaction. SQLite uses this information to truncate the |
| 764 |
database file back to its original size in cases where the |
| 765 |
incomplete transaction caused the database to grow. At the |
| 766 |
end of this step, the database should be the same size and |
| 767 |
contain the same information as it did before the start of |
| 768 |
the aborted transaction.</p> |
| 769 |
|
| 770 |
<br clear="both"> |
| 771 |
<a name="delhotjrnl"></a> |
| 772 |
|
| 773 |
<h2 id="_deleting_the_hot_journal"><span>4.5. </span> Deleting The Hot Journal</h2> |
| 774 |
<img src="images/ac/rollback-4.gif" align="right" hspace="15"> |
| 775 |
|
| 776 |
<p>After all information in the rollback journal has been |
| 777 |
played back into the database file (and flushed to disk in case |
| 778 |
we encounter yet another power failure), the hot rollback journal |
| 779 |
can be deleted.</p> |
| 780 |
|
| 781 |
<p>As in <a href="#section_3_11">section 3.11</a>, the journal |
| 782 |
file might be truncated to zero length or its header might |
| 783 |
be overwritten with zeros as an optimization on systems where |
| 784 |
deleting a file is expensive. Either way, the journal is no |
| 785 |
longer hot after this step.</p> |
| 786 |
|
| 787 |
<br clear="both"> |
| 788 |
<a name="cont"></a> |
| 789 |
|
| 790 |
<h2 id="_continue_as_if_the_uncompleted_writes_had_never_happened"><span>4.6. </span> Continue As If The Uncompleted Writes Had Never Happened</h2> |
| 791 |
<img src="images/ac/rollback-5.gif" align="right" hspace="15"> |
| 792 |
|
| 793 |
<p>The final recovery step is to reduce the exclusive lock back |
| 794 |
to a shared lock. Once this happens, the database is back in the |
| 795 |
state that it would have been if the aborted transaction had never |
| 796 |
started. Since all of this recovery activity happens completely |
| 797 |
automatically and transparently, it appears to the program using |
| 798 |
SQLite as if the aborted transaction had never begun.</p> |
| 799 |
|
| 800 |
<br clear="both"> |
| 801 |
<a name="multicommit"></a> |
| 802 |
|
| 803 |
<h1 id="_multi_file_commit"><span>5. </span> Multi-file Commit</h1> |
| 804 |
|
| 805 |
<p>SQLite allows a single |
| 806 |
<a href="c3ref/sqlite3.html">database connection</a> to talk to |
| 807 |
two or more database files simultaneously through the use of |
| 808 |
the <a href="lang_attach.html">ATTACH DATABASE</a> command. |
| 809 |
When multiple database files are modified within a single |
| 810 |
transaction, all files are updated atomically. |
| 811 |
In other words, either all of the database files are updated or |
| 812 |
else none of them are. |
| 813 |
Achieving an atomic commit across multiple database files is |
| 814 |
more complex that doing so for a single file. This section |
| 815 |
describes how SQLite works that bit of magic.</p> |
| 816 |
|
| 817 |
<a name="multijrnl"></a> |
| 818 |
|
| 819 |
<h2 id="_separate_rollback_journals_for_each_database"><span>5.1. </span> Separate Rollback Journals For Each Database</h2> |
| 820 |
<img src="images/ac/multi-0.gif" align="right" hspace="15"> |
| 821 |
|
| 822 |
<p>When multiple database files are involved in a transaction, |
| 823 |
each database has its own rollback journal and each database |
| 824 |
is locked separately. The diagram at the right shows a scenario |
| 825 |
where three different database files have been modified within |
| 826 |
one transaction. The situation at this step is analogous to |
| 827 |
the single-file transaction scenario at |
| 828 |
<a href="#section_3_6">step 3.6</a>. Each database file has |
| 829 |
a reserved lock. For each database, the original content of pages |
| 830 |
that are being changed have been written into the rollback journal |
| 831 |
for that database, but the content of the journals have not yet |
| 832 |
been flushed to disk. No changes have been made to the database |
| 833 |
file itself yet, though presumably there are changes being held |
| 834 |
in user memory.</p> |
| 835 |
|
| 836 |
<p>For brevity, the diagrams in this section are simplified from |
| 837 |
those that came before. Blue color still signifies original content |
| 838 |
and pink still signifies new content. But the individual pages |
| 839 |
in the rollback journal and the database file are not shown and |
| 840 |
we are not making the distinction between information in the |
| 841 |
operating system cache and information that is on disk. All of |
| 842 |
these factors still apply in a multi-file commit scenario. They |
| 843 |
just take up a lot of space in the diagrams and they do not add |
| 844 |
any new information, so they are omitted here.</p> |
| 845 |
|
| 846 |
<br clear="both"> |
| 847 |
<a name="sprjrnl"></a> |
| 848 |
|
| 849 |
<h2 id="_the_super_journal_file"><span>5.2. </span> The Super-Journal File</h2> |
| 850 |
<img src="images/ac/multi-1.gif" align="right" hspace="15"> |
| 851 |
|
| 852 |
<p>The next step in a multi-file commit is the creation of a |
| 853 |
"super-journal" file. The name of the super-journal file is |
| 854 |
the same name as the original database filename (the database |
| 855 |
that was opened using the |
| 856 |
<a href="c3ref/open.html">sqlite3_open()</a> interface, |
| 857 |
not one of the <a href="lang_attach.html">ATTACHed</a> auxiliary |
| 858 |
databases) with the text "<b>-mj</b><i>HHHHHHHH</i>" appended where |
| 859 |
<i>HHHHHHHH</i> is a random 32-bit hexadecimal number. The |
| 860 |
random <i>HHHHHHHH</i> suffix changes for every new super-journal.</p> |
| 861 |
|
| 862 |
<p><i>(Nota bene: The formula for computing the super-journal filename |
| 863 |
given in the previous paragraph corresponds to the implementation as |
| 864 |
of SQLite version 3.5.0. But this formula is not part of the SQLite |
| 865 |
specification and is subject to change in future releases.)</i></p> |
| 866 |
|
| 867 |
<p>Unlike the rollback journals, the super-journal does not contain |
| 868 |
any original database page content. Instead, the super-journal contains |
| 869 |
the full pathnames for rollback journals for every database that is |
| 870 |
participating in the transaction.</p> |
| 871 |
|
| 872 |
<p>After the super-journal is constructed, its content is flushed |
| 873 |
to disk before any further actions are taken. On Unix, the directory |
| 874 |
that contains the super-journal is also synced in order to make sure |
| 875 |
the super-journal file will appear in the directory following a |
| 876 |
power failure.</p> |
| 877 |
|
| 878 |
<p>The purpose of the super-journal is to ensure that multi-file |
| 879 |
transactions are atomic across a power-loss. But if the database files |
| 880 |
have other settings that compromise integrity across a power-loss event |
| 881 |
(such as <a href="pragma.html#pragma_synchronous">PRAGMA synchronous=OFF</a> or <a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode=MEMORY</a>) then |
| 882 |
the creation of the super-journal is omitted, as an optimization. |
| 883 |
|
| 884 |
<br clear="both"> |
| 885 |
<a name="multijrnlupdate"></a> |
| 886 |
|
| 887 |
</p><h2 id="_updating_rollback_journal_headers"><span>5.3. </span> Updating Rollback Journal Headers</h2> |
| 888 |
<img src="images/ac/multi-2.gif" align="right" hspace="15"> |
| 889 |
|
| 890 |
<p>The next step is to record the full pathname of the super-journal file |
| 891 |
in the header of every rollback journal. Space to hold the |
| 892 |
super-journal filename was reserved at the beginning of each rollback journal |
| 893 |
as the rollback journals were created.</p> |
| 894 |
|
| 895 |
<p>The content of each rollback journal is flushed to disk both before |
| 896 |
and after the super-journal filename is written into the rollback |
| 897 |
journal header. It is important to do both of these flushes. Fortunately, |
| 898 |
the second flush is usually inexpensive since typically only a single |
| 899 |
page of the journal file (the first page) has changed.</p> |
| 900 |
|
| 901 |
<p>This step is analogous to |
| 902 |
<a href="#section_3_7">step 3.7</a> in the single-file commit |
| 903 |
scenario described above.</p> |
| 904 |
|
| 905 |
<br clear="both"> |
| 906 |
<a name="multidbupdate"></a> |
| 907 |
|
| 908 |
<h2 id="_updating_the_database_files"><span>5.4. </span> Updating The Database Files</h2> |
| 909 |
<img src="images/ac/multi-3.gif" align="right" hspace="15"> |
| 910 |
|
| 911 |
<p>Once all rollback journal files have been flushed to disk, it |
| 912 |
is safe to begin updating database files. We have to obtain an |
| 913 |
exclusive lock on all database files before writing the changes. |
| 914 |
After all the changes are written, it is important to flush the |
| 915 |
changes to disk so that they will be preserved in the event of |
| 916 |
a power failure or operating system crash.</p> |
| 917 |
|
| 918 |
<p>This step corresponds to steps |
| 919 |
<a href="#section_3_8">3.8</a>, |
| 920 |
<a href="#section_3_9">3.9</a>, and |
| 921 |
<a href="#section_3_10">3.10</a> in the single-file commit |
| 922 |
scenario described previously.</p> |
| 923 |
|
| 924 |
|
| 925 |
<br clear="both"> |
| 926 |
<a name="section_5_5"></a> |
| 927 |
<h2 id="_delete_the_super_journal_file"><span>5.5. </span> Delete The Super-Journal File</h2> |
| 928 |
<img src="images/ac/multi-4.gif" align="right" hspace="15"> |
| 929 |
|
| 930 |
<p>The next step is to delete the super-journal file. |
| 931 |
This is the point where the multi-file transaction commits. |
| 932 |
This step corresponds to |
| 933 |
<a href="#section_3_11">step 3.11</a> in the single-file |
| 934 |
commit scenario where the rollback journal is deleted.</p> |
| 935 |
|
| 936 |
<p>If a power failure or operating system crash occurs at this |
| 937 |
point, the transaction will not rollback when the system reboots |
| 938 |
even though there are rollback journals present. The |
| 939 |
difference is the super-journal pathname in the header of the |
| 940 |
rollback journal. Upon restart, SQLite only considers a journal |
| 941 |
to be hot and will only playback the journal if there is no |
| 942 |
super-journal filename in the header (which is the case for |
| 943 |
a single-file commit) or if the super-journal file still |
| 944 |
exists on disk.</p> |
| 945 |
|
| 946 |
<br clear="both"> |
| 947 |
<a name="cleanup"></a> |
| 948 |
|
| 949 |
<h2 id="_clean_up_the_rollback_journals"><span>5.6. </span> Clean Up The Rollback Journals</h2> |
| 950 |
<img src="images/ac/multi-5.gif" align="right" hspace="15"> |
| 951 |
|
| 952 |
<p>The final step in a multi-file commit is to delete the |
| 953 |
individual rollback journals and drop the exclusive locks on |
| 954 |
the database files so that other processes can see the changes. |
| 955 |
This corresponds to |
| 956 |
<a href="#section_3_12">step 3.12</a> in the single-file |
| 957 |
commit sequence.</p> |
| 958 |
|
| 959 |
<p>The transaction has already committed at this point so timing |
| 960 |
is not critical in the deletion of the rollback journals. |
| 961 |
The current implementation deletes a single rollback journal |
| 962 |
then unlocks the corresponding database file before proceeding |
| 963 |
to the next rollback journal. But in the future we might change |
| 964 |
this so that all rollback journals are deleted before any database |
| 965 |
files are unlocked. As long as the rollback journal is deleted before |
| 966 |
its corresponding database file is unlocked it does not matter in what |
| 967 |
order the rollback journals are deleted or the database files are |
| 968 |
unlocked.</p> |
| 969 |
|
| 970 |
<a name="moredetail"></a> |
| 971 |
|
| 972 |
<h1 id="_additional_details_of_the_commit_process"><span>6. </span> Additional Details Of The Commit Process</h1> |
| 973 |
|
| 974 |
<p><a href="#section_3_0">Section 3.0</a> above provides an overview of |
| 975 |
how atomic commit works in SQLite. But it glosses over a number of |
| 976 |
important details. The following subsections will attempt to fill |
| 977 |
in the gaps.</p> |
| 978 |
|
| 979 |
<a name="completesectors"></a> |
| 980 |
|
| 981 |
<h2 id="_always_journal_complete_sectors"><span>6.1. </span> Always Journal Complete Sectors</h2> |
| 982 |
|
| 983 |
<p>When the original content of a database page is written into |
| 984 |
the rollback journal (as shown in <a href="#section_3_5">section 3.5</a>), |
| 985 |
SQLite always writes a complete sector of data, even if the |
| 986 |
page size of the database is smaller than the sector size. |
| 987 |
Historically, the sector size in SQLite has been hard coded to 512 |
| 988 |
bytes and since the minimum page size is also 512 bytes, this has never |
| 989 |
been an issue. But beginning with SQLite version 3.3.14, it is possible |
| 990 |
for SQLite to use mass storage devices with a sector size larger than 512 |
| 991 |
bytes. So, beginning with version 3.3.14, whenever any page within a |
| 992 |
sector is written into the journal file, all pages in that same sector |
| 993 |
are stored with it.</p> |
| 994 |
|
| 995 |
<p>It is important to store all pages of a sector in the rollback |
| 996 |
journal in order to prevent database corruption following a power |
| 997 |
loss while writing the sector. Suppose that pages 1, 2, 3, and 4 are |
| 998 |
all stored in sector 1 and that page 2 is modified. In order to write |
| 999 |
the changes to page 2, the underlying hardware must also rewrite the |
| 1000 |
content of pages 1, 3, and 4 since the hardware must write the complete |
| 1001 |
sector. If this write operation is interrupted by a power outage, |
| 1002 |
one or more of the pages 1, 3, or 4 might be left with incorrect data. |
| 1003 |
Hence, to avoid lasting corruption to the database, the original content |
| 1004 |
of all of those pages must be contained in the rollback journal.</p> |
| 1005 |
|
| 1006 |
<a name="journalgarbage"></a> |
| 1007 |
|
| 1008 |
<h2 id="_dealing_with_garbage_written_into_journal_files"><span>6.2. </span> Dealing With Garbage Written Into Journal Files</h2> |
| 1009 |
|
| 1010 |
<p>When data is appended to the end of the rollback journal, |
| 1011 |
SQLite normally makes the pessimistic assumption that the file |
| 1012 |
is first extended with invalid "garbage" data and that afterwards |
| 1013 |
the correct data replaces the garbage. In other words, SQLite assumes |
| 1014 |
that the file size is increased first and then afterwards the content |
| 1015 |
is written into the file. If a power failure occurs after the file |
| 1016 |
size has been increased but before the file content has been written, |
| 1017 |
the rollback journal can be left containing garbage data. If after |
| 1018 |
power is restored, another SQLite process sees the rollback journal |
| 1019 |
containing the garbage data and tries to roll it back into the original |
| 1020 |
database file, it might copy some of the garbage into the database file |
| 1021 |
and thus corrupt the database file.</p> |
| 1022 |
|
| 1023 |
<p>SQLite uses two defenses against this problem. In the first place, |
| 1024 |
SQLite records the number of pages in the rollback journal in the header |
| 1025 |
of the rollback journal. This number is initially zero. So during an |
| 1026 |
attempt to rollback an incomplete (and possibly corrupt) rollback |
| 1027 |
journal, the process doing the rollback will see that the journal |
| 1028 |
contains zero pages and will thus make no changes to the database. Prior |
| 1029 |
to a commit, the rollback journal is flushed to disk to ensure that |
| 1030 |
all content has been synced to disk and there is no "garbage" left |
| 1031 |
in the file, and only then is the page count in the header changed from |
| 1032 |
zero to the number of pages in the rollback journal. The rollback journal |
| 1033 |
header is always kept in a separate sector from any page data so that |
| 1034 |
it can be overwritten and flushed without risking damage to a data |
| 1035 |
page if a power outage occurs. Notice that the rollback journal |
| 1036 |
is flushed to disk twice: once to write the page content and a second |
| 1037 |
time to write the page count in the header.</p> |
| 1038 |
|
| 1039 |
<p>The previous paragraph describes what happens when the |
| 1040 |
synchronous pragma setting is "full".</p> |
| 1041 |
|
| 1042 |
<blockquote> |
| 1043 |
PRAGMA synchronous=FULL; |
| 1044 |
</blockquote> |
| 1045 |
|
| 1046 |
<p>The default synchronous setting is full so the above is what usually |
| 1047 |
happens. However, if the synchronous setting is lowered to "normal", |
| 1048 |
SQLite only flushes the rollback journal once, after the page count has |
| 1049 |
been written. |
| 1050 |
This carries a risk of corruption because it might happen that the |
| 1051 |
modified (non-zero) page count reaches the disk surface before all |
| 1052 |
of the data does. The data will have been written first, but SQLite |
| 1053 |
assumes that the underlying filesystem can reorder write requests and |
| 1054 |
that the page count can be burned into oxide first even though its |
| 1055 |
write request occurred last. So as a second line of defense, SQLite |
| 1056 |
also uses a 32-bit checksum on every page of data in the rollback |
| 1057 |
journal. This checksum is evaluated for each page during rollback |
| 1058 |
while rolling back a journal as described in |
| 1059 |
<a href="#section_4_4">section 4.4</a>. If an incorrect checksum |
| 1060 |
is seen, the rollback is abandoned. Note that the checksum does |
| 1061 |
not guarantee that the page data is correct since there is a small |
| 1062 |
but finite probability that the checksum might be right even if the data is |
| 1063 |
corrupt. But the checksum does at least make such an error unlikely. |
| 1064 |
</p> |
| 1065 |
|
| 1066 |
<p>Note that the checksums in the rollback journal are not necessary |
| 1067 |
if the synchronous setting is FULL. We only depend on the checksums |
| 1068 |
when synchronous is lowered to NORMAL. Nevertheless, the checksums |
| 1069 |
never hurt and so they are included in the rollback journal regardless |
| 1070 |
of the synchronous setting.</p> |
| 1071 |
|
| 1072 |
<a name="cachespill"></a> |
| 1073 |
|
| 1074 |
<h2 id="_cache_spill_prior_to_commit"><span>6.3. </span> Cache Spill Prior To Commit</h2> |
| 1075 |
|
| 1076 |
<p>The commit process shown in <a href="#section_3_0">section 3.0</a> |
| 1077 |
assumes that all database changes fit in memory until it is time to |
| 1078 |
commit. This is the common case. But sometimes a larger change will |
| 1079 |
overflow the user-space cache prior to transaction commit. In those |
| 1080 |
cases, the cache must spill to the database before the transaction |
| 1081 |
is complete.</p> |
| 1082 |
|
| 1083 |
<p>At the beginning of a cache spill, the status of the database |
| 1084 |
connection is as shown in <a href="#section_3_6">step 3.6</a>. |
| 1085 |
Original page content has been saved in the rollback journal and |
| 1086 |
modifications of the pages exist in user memory. To spill the cache, |
| 1087 |
SQLite executes steps <a href="#section_3_7">3.7</a> through |
| 1088 |
<a href="#section_3_9">3.9</a>. In other words, the rollback journal |
| 1089 |
is flushed to disk, an exclusive lock is acquired, and changes are |
| 1090 |
written into the database. But the remaining steps are deferred |
| 1091 |
until the transaction really commits. A new journal header is |
| 1092 |
appended to the end of the rollback journal (in its own sector) |
| 1093 |
and the exclusive database lock is retained, but otherwise processing |
| 1094 |
returns to <a href="#section_3_6">step 3.6</a>. When the transaction |
| 1095 |
commits, or if another cache spill occurs, steps |
| 1096 |
<a href="#section_3_7">3.7</a> and <a href="#section_3_9">3.9</a> are |
| 1097 |
repeated. (Step <a href="#section_3_8">3.8</a> is omitted on second |
| 1098 |
and subsequent passes since an exclusive database lock is already held |
| 1099 |
due to the first pass.)</p> |
| 1100 |
|
| 1101 |
<p>A cache spill causes the lock on the database file to |
| 1102 |
escalate from reserved to exclusive. This reduces concurrency. |
| 1103 |
A cache spill also causes extra disk flush or fsync operations to |
| 1104 |
occur and these operations are slow, hence a cache spill can |
| 1105 |
seriously reduce performance. |
| 1106 |
For these reasons a cache spill is avoided whenever possible.</p> |
| 1107 |
|
| 1108 |
<a name="opts"></a> |
| 1109 |
|
| 1110 |
<h1 id="_optimizations"><span>7. </span> Optimizations</h1> |
| 1111 |
|
| 1112 |
<p>Profiling indicates that for most systems and in most circumstances |
| 1113 |
SQLite spends most of its time doing disk I/O. It follows then that |
| 1114 |
anything we can do to reduce the amount of disk I/O will likely have a |
| 1115 |
large positive impact on the performance of SQLite. This section |
| 1116 |
describes some of the techniques used by SQLite to try to reduce the |
| 1117 |
amount of disk I/O to a minimum while still preserving atomic commit.</p> |
| 1118 |
|
| 1119 |
<a name="keepcache"></a> |
| 1120 |
|
| 1121 |
<h2 id="_cache_retained_between_transactions"><span>7.1. </span> Cache Retained Between Transactions</h2> |
| 1122 |
|
| 1123 |
<p><a href="#section_3_12">Step 3.12</a> of the commit process shows |
| 1124 |
that once the shared lock has been released, all user-space cache |
| 1125 |
images of database content must be discarded. This is done because |
| 1126 |
without a shared lock, other processes are free to modify the database |
| 1127 |
file content and so any user-space image of that content might become |
| 1128 |
obsolete. Consequently, each new transaction would begin by rereading |
| 1129 |
data which had previously been read. This is not as bad as it sounds |
| 1130 |
at first since the data being read is still likely in the operating |
| 1131 |
systems file cache. So the "read" is really just a copy of data |
| 1132 |
from kernel space into user space. But even so, it still takes time.</p> |
| 1133 |
|
| 1134 |
<p>Beginning with SQLite version 3.3.14 a mechanism has been added |
| 1135 |
to try to reduce the needless rereading of data. In newer versions |
| 1136 |
of SQLite, the data in the user-space pager cache is retained when |
| 1137 |
the lock on the database file is released. Later, after the |
| 1138 |
shared lock is acquired at the beginning of the next transaction, |
| 1139 |
SQLite checks to see if any other process has modified the database |
| 1140 |
file. If the database has been changed in any way since the lock |
| 1141 |
was last released, the user-space cache is erased at that point. |
| 1142 |
But commonly the database file is unchanged and the user-space cache |
| 1143 |
can be retained, and some unnecessary read operations can be avoided.</p> |
| 1144 |
|
| 1145 |
<p>In order to determine whether or not the database file has changed, |
| 1146 |
SQLite uses a counter in the database header (in bytes 24 through 27) |
| 1147 |
which is incremented during every change operation. SQLite saves a copy |
| 1148 |
of this counter prior to releasing its database lock. Then after |
| 1149 |
acquiring the next database lock it compares the saved counter value |
| 1150 |
against the current counter value and erases the cache if the values |
| 1151 |
are different, or reuses the cache if they are the same.</p> |
| 1152 |
|
| 1153 |
<a name="section_7_2"></a> |
| 1154 |
<h2 id="_exclusive_access_mode"><span>7.2. </span> Exclusive Access Mode</h2> |
| 1155 |
|
| 1156 |
<p>SQLite version 3.3.14 adds the concept of "Exclusive Access Mode". |
| 1157 |
In exclusive access mode, SQLite retains the exclusive |
| 1158 |
database lock at the conclusion of each transaction. This prevents |
| 1159 |
other processes from accessing the database, but in many deployments |
| 1160 |
only a single process is using a database so this is not a |
| 1161 |
serious problem. The advantage of exclusive access mode is that |
| 1162 |
disk I/O can be reduced in three ways:</p> |
| 1163 |
|
| 1164 |
<ol> |
| 1165 |
<li><p>It is not necessary to increment the change counter in the |
| 1166 |
database header for transactions after the first transaction. This |
| 1167 |
will often save a write of page one to both the rollback |
| 1168 |
journal and the main database file.</p></li> |
| 1169 |
|
| 1170 |
<li><p>No other processes can change the database so there is never |
| 1171 |
a need to check the change counter and clear the user-space cache |
| 1172 |
at the beginning of a transaction.</p></li> |
| 1173 |
|
| 1174 |
<li><p>Each transaction can be committed by overwriting the rollback |
| 1175 |
journal header with zeros rather than deleting the journal file. |
| 1176 |
This avoids having to modify the directory entry for the journal file |
| 1177 |
and it avoids having to deallocate disk sectors associated with the |
| 1178 |
journal. Furthermore, the next transaction will overwrite existing |
| 1179 |
journal file content rather than append new content and on most systems |
| 1180 |
overwriting is much faster than appending.</p></li> |
| 1181 |
</ol> |
| 1182 |
|
| 1183 |
<p>The third optimization, zeroing the journal file header rather than |
| 1184 |
deleting the rollback journal file, |
| 1185 |
does not depend on holding an exclusive lock at all times. |
| 1186 |
This optimization can be set independently of exclusive lock mode |
| 1187 |
using the <a href="pragma.html#pragma_journal_mode">journal_mode pragma</a> |
| 1188 |
as described in <a href="#section_7_6">section 7.6</a> below.</p> |
| 1189 |
|
| 1190 |
<a name="freelistjrnl"></a> |
| 1191 |
|
| 1192 |
<h2 id="_do_not_journal_freelist_pages"><span>7.3. </span> Do Not Journal Freelist Pages</h2> |
| 1193 |
|
| 1194 |
<p>When information is deleted from an SQLite database, the pages used |
| 1195 |
to hold the deleted information are added to a "<a href="fileformat2.html#freelist">freelist</a>". Subsequent |
| 1196 |
inserts will draw pages off of this freelist rather than expanding the |
| 1197 |
database file.</p> |
| 1198 |
|
| 1199 |
<p>Some freelist pages contain critical data; specifically the locations |
| 1200 |
of other freelist pages. But most freelist pages contain nothing useful. |
| 1201 |
These latter freelist pages are called "leaf" pages. We are free to |
| 1202 |
modify the content of a leaf freelist page in the database without |
| 1203 |
changing the meaning of the database in any way.</p> |
| 1204 |
|
| 1205 |
<p>Because the content of leaf freelist pages is unimportant, SQLite |
| 1206 |
avoids storing leaf freelist page content in the rollback journal |
| 1207 |
in <a href="#section_3_5">step 3.5</a> of the commit process. |
| 1208 |
If a leaf freelist page is changed and that change does not get rolled back |
| 1209 |
during a transaction recovery, the database is not harmed by the omission. |
| 1210 |
Similarly, the content of a new freelist page is never written back |
| 1211 |
into the database at <a href="#section_3_9">step 3.9</a> nor |
| 1212 |
read from the database at <a href="#section_3_3">step 3.3</a>. |
| 1213 |
These optimizations can greatly reduce the amount of I/O that occurs |
| 1214 |
when making changes to a database file that contains free space.</p> |
| 1215 |
|
| 1216 |
<a name="atomicsector"></a> |
| 1217 |
|
| 1218 |
<h2 id="_single_page_updates_and_atomic_sector_writes"><span>7.4. </span> Single Page Updates And Atomic Sector Writes</h2> |
| 1219 |
|
| 1220 |
<p>Beginning in SQLite version 3.5.0, the new Virtual File System (VFS) |
| 1221 |
interface contains a method named xDeviceCharacteristics which reports |
| 1222 |
on special properties that the underlying mass storage device |
| 1223 |
might have. Among the special properties that |
| 1224 |
xDeviceCharacteristics might report is the ability to do an |
| 1225 |
atomic sector write.</p> |
| 1226 |
|
| 1227 |
<p>Recall that by default SQLite assumes that sector writes are |
| 1228 |
linear but not atomic. A linear write starts at one end of the |
| 1229 |
sector and changes information byte by byte until it gets to the |
| 1230 |
other end of the sector. If a power loss occurs in the middle of |
| 1231 |
a linear write then part of the sector might be modified while the |
| 1232 |
other end is unchanged. In an atomic sector write, either the entire |
| 1233 |
sector is overwritten or else nothing in the sector is changed.</p> |
| 1234 |
|
| 1235 |
<p>We believe that most modern disk drives implement atomic sector |
| 1236 |
writes. When power is lost, the drive uses energy stored in capacitors |
| 1237 |
and/or the angular momentum of the disk platter to provide power to |
| 1238 |
complete any operation in progress. Nevertheless, there are so many |
| 1239 |
layers in between the write system call and the on-board disk drive |
| 1240 |
electronics that we take the safe approach in both Unix and w32 VFS |
| 1241 |
implementations and assume that sector writes are not atomic. On the |
| 1242 |
other hand, device |
| 1243 |
manufacturers with more control over their filesystems might want |
| 1244 |
to consider enabling the atomic write property of xDeviceCharacteristics |
| 1245 |
if their hardware really does do atomic writes.</p> |
| 1246 |
|
| 1247 |
<p>When sector writes are atomic and the page size of a database is |
| 1248 |
the same as a sector size, and when there is a database change that |
| 1249 |
only touches a single database page, then SQLite skips the whole |
| 1250 |
journaling and syncing process and simply writes the modified page |
| 1251 |
directly into the database file. The change counter in the first |
| 1252 |
page of the database file is modified separately since no harm is |
| 1253 |
done if power is lost before the change counter can be updated.</p> |
| 1254 |
|
| 1255 |
<a name="safeappend"></a> |
| 1256 |
|
| 1257 |
<h2 id="_filesystems_with_safe_append_semantics"><span>7.5. </span> Filesystems With Safe Append Semantics</h2> |
| 1258 |
|
| 1259 |
<p>Another optimization introduced in SQLite version 3.5.0 makes |
| 1260 |
use of "safe append" behavior of the underlying disk. |
| 1261 |
Recall that SQLite assumes that when data is appended to a file |
| 1262 |
(specifically to the rollback journal) that the size of the file |
| 1263 |
is increased first and that the content is written second. So |
| 1264 |
if power is lost after the file size is increased but before the |
| 1265 |
content is written, the file is left containing invalid "garbage" |
| 1266 |
data. The xDeviceCharacteristics method of the VFS might, however, |
| 1267 |
indicate that the filesystem implements "safe append" semantics. |
| 1268 |
This means that the content is written before the file size is |
| 1269 |
increased so that it is impossible for garbage to be introduced |
| 1270 |
into the rollback journal by a power loss or system crash.</p> |
| 1271 |
|
| 1272 |
<p>When safe append semantics are indicated for a filesystem, |
| 1273 |
SQLite always stores the special value of -1 for the page count |
| 1274 |
in the header of the rollback journal. The -1 page count value |
| 1275 |
tells any process attempting to rollback the journal that the |
| 1276 |
number of pages in the journal should be computed from the journal |
| 1277 |
size. This -1 value is never changed. So that when a commit |
| 1278 |
occurs, we save a single flush operation and a sector write of |
| 1279 |
the first page of the journal file. Furthermore, when a cache |
| 1280 |
spill occurs we no longer need to append a new journal header |
| 1281 |
to the end of the journal; we can simply continue appending |
| 1282 |
new pages to the end of the existing journal.</p> |
| 1283 |
|
| 1284 |
<a name="section_7_6"></a> |
| 1285 |
<h2 id="_persistent_rollback_journals"><span>7.6. </span> Persistent Rollback Journals</h2> |
| 1286 |
|
| 1287 |
<p>Deleting a file is an expensive operation on many systems. |
| 1288 |
So as an optimization, SQLite can be configured to avoid the |
| 1289 |
delete operation of <a href="#section_3_11">section 3.11</a>. |
| 1290 |
Instead of deleting the journal file in order to commit a transaction, |
| 1291 |
the file is either truncated to zero bytes in length or its |
| 1292 |
header is overwritten with zeros. Truncating the file to zero |
| 1293 |
length saves having to make modifications to the directory containing |
| 1294 |
the file since the file is not removed from the directory. |
| 1295 |
Overwriting the header has the additional savings of not having |
| 1296 |
to update the length of the file (in the "inode" on many systems) |
| 1297 |
and not having to deal with newly freed disk sectors. Furthermore, |
| 1298 |
at the next transaction the journal will be created by overwriting |
| 1299 |
existing content rather than appending new content onto the end |
| 1300 |
of a file, and overwriting is often much faster than appending.</p> |
| 1301 |
|
| 1302 |
<p>SQLite can be configured to commit transactions by overwriting |
| 1303 |
the journal header with zeros instead of deleting the journal file |
| 1304 |
by setting the "PERSIST" journaling mode using the |
| 1305 |
<a href="pragma.html#pragma_journal_mode">journal_mode</a> PRAGMA. |
| 1306 |
For example:</p> |
| 1307 |
|
| 1308 |
<blockquote><pre> |
| 1309 |
PRAGMA journal_mode=PERSIST; |
| 1310 |
</pre></blockquote> |
| 1311 |
|
| 1312 |
<p>The use of persistent journal mode provides a noticeable performance |
| 1313 |
improvement on many systems. Of course, the drawback is that the |
| 1314 |
journal files remain on the disk, using disk space and cluttering |
| 1315 |
directories, long after the transaction commits. The only safe way |
| 1316 |
to delete a persistent journal file is to commit a transaction |
| 1317 |
with journaling mode set to DELETE:</p> |
| 1318 |
|
| 1319 |
<blockquote><pre> |
| 1320 |
PRAGMA journal_mode=DELETE; |
| 1321 |
BEGIN EXCLUSIVE; |
| 1322 |
COMMIT; |
| 1323 |
</pre></blockquote> |
| 1324 |
|
| 1325 |
<p>Beware of deleting persistent journal files by any other means |
| 1326 |
since the journal file might be hot, in which case deleting it will |
| 1327 |
corrupt the corresponding database file.</p> |
| 1328 |
|
| 1329 |
<p>Beginning in SQLite <a href="releaselog/3_6_4.html">version 3.6.4</a> (2008-10-15), |
| 1330 |
the TRUNCATE journal mode is |
| 1331 |
also supported:</p> |
| 1332 |
|
| 1333 |
<blockquote><pre> |
| 1334 |
PRAGMA journal_mode=TRUNCATE; |
| 1335 |
</pre></blockquote> |
| 1336 |
|
| 1337 |
<p>In truncate journal mode, the transaction is committed by truncating |
| 1338 |
the journal file to zero length rather than deleting the journal file |
| 1339 |
(as in DELETE mode) or by zeroing the header (as in PERSIST mode). |
| 1340 |
TRUNCATE mode shares the advantage of PERSIST mode that the directory |
| 1341 |
that contains the journal file and database does not need to be updated. |
| 1342 |
Hence truncating a file is often faster than deleting it. TRUNCATE has |
| 1343 |
the additional advantage that it is not followed by a |
| 1344 |
system call (ex: fsync()) to synchronize the change to disk. It might |
| 1345 |
be safer if it did. |
| 1346 |
But on many modern filesystems, a truncate is an atomic and |
| 1347 |
synchronous operation and so we think that TRUNCATE will usually be safe |
| 1348 |
in the face of power failures. If you are uncertain about whether or |
| 1349 |
not TRUNCATE will be synchronous and atomic on your filesystem and it is |
| 1350 |
important to you that your database survive a power loss or operating |
| 1351 |
system crash that occurs during the truncation operation, then you might |
| 1352 |
consider using a different journaling mode.</p> |
| 1353 |
|
| 1354 |
<p>On embedded systems with synchronous filesystems, TRUNCATE results |
| 1355 |
in slower behavior than PERSIST. The commit operation is the same speed. |
| 1356 |
But subsequent transactions are slower following a TRUNCATE because it is |
| 1357 |
faster to overwrite existing content than to append to the end of a file. |
| 1358 |
New journal file entries will always be appended following a TRUNCATE but |
| 1359 |
will usually overwrite with PERSIST.</p> |
| 1360 |
|
| 1361 |
<a name="testing"></a> |
| 1362 |
|
| 1363 |
<h1 id="_testing_atomic_commit_behavior"><span>8. </span> Testing Atomic Commit Behavior</h1> |
| 1364 |
|
| 1365 |
<p>The developers of SQLite are confident that it is robust |
| 1366 |
in the face of power failures and system crashes because the |
| 1367 |
automatic test procedures do extensive checks on |
| 1368 |
the ability of SQLite to recover from simulated power loss. |
| 1369 |
We call these the "crash tests".</p> |
| 1370 |
|
| 1371 |
<p>Crash tests in SQLite use a modified VFS that can simulate |
| 1372 |
the kinds of filesystem damage that occur during a power |
| 1373 |
loss or operating system crash. The crash-test VFS can simulate |
| 1374 |
incomplete sector writes, pages filled with garbage data because |
| 1375 |
a write has not completed, and out of order writes, all occurring |
| 1376 |
at varying points during a test scenario. Crash tests execute |
| 1377 |
transactions over and over, varying the time at which a simulated |
| 1378 |
power loss occurs and the properties of the damage inflicted. |
| 1379 |
Each test then reopens the database after the simulated crash and |
| 1380 |
verifies that the transaction either occurred completely |
| 1381 |
or not at all and that the database is in a completely |
| 1382 |
consistent state.</p> |
| 1383 |
|
| 1384 |
<p>The crash tests in SQLite have discovered a number of very |
| 1385 |
subtle bugs (now fixed) in the recovery mechanism. Some of |
| 1386 |
these bugs were very obscure and unlikely to have been found |
| 1387 |
using only code inspection and analysis techniques. From this |
| 1388 |
experience, the developers of SQLite feel confident that any other |
| 1389 |
database system that does not use a similar crash test system |
| 1390 |
likely contains undetected bugs that will lead to database |
| 1391 |
corruption following a system crash or power failure.</p> |
| 1392 |
|
| 1393 |
<a name="sect_9_0"></a> |
| 1394 |
|
| 1395 |
<h1 id="_things_that_can_go_wrong"><span>9. </span> Things That Can Go Wrong</h1> |
| 1396 |
|
| 1397 |
<p>The atomic commit mechanism in SQLite has proven to be robust, |
| 1398 |
but it can be circumvented by a sufficiently creative |
| 1399 |
adversary or a sufficiently broken operating system implementation. |
| 1400 |
This section describes a few of the ways in which an SQLite database |
| 1401 |
might be corrupted by a power failure or system crash. |
| 1402 |
(See also: <a href="howtocorrupt.html">How To Corrupt Your Database Files</a>.)</p> |
| 1403 |
|
| 1404 |
<a name="brokenlocks"></a> |
| 1405 |
|
| 1406 |
<h2 id="_broken_locking_implementations"><span>9.1. </span> Broken Locking Implementations</h2> |
| 1407 |
|
| 1408 |
<p>SQLite uses filesystem locks to make sure that only one |
| 1409 |
process and database connection is trying to modify the database |
| 1410 |
at a time. The filesystem locking mechanism is implemented |
| 1411 |
in the VFS layer and is different for every operating system. |
| 1412 |
SQLite depends on this implementation being correct. If something |
| 1413 |
goes wrong and two or more processes are able to write the same |
| 1414 |
database file at the same time, severe damage can result.</p> |
| 1415 |
|
| 1416 |
<p>We have received reports of implementations of both |
| 1417 |
Windows network filesystems and NFS in which locking was |
| 1418 |
subtly broken. We can not verify these reports, but as |
| 1419 |
locking is difficult to get right on a network filesystem |
| 1420 |
we have no reason to doubt them. You are advised to |
| 1421 |
avoid using SQLite on a network filesystem in the first place, |
| 1422 |
since performance will be slow. But if you must use a |
| 1423 |
network filesystem to store SQLite database files, consider |
| 1424 |
using a secondary locking mechanism to prevent simultaneous |
| 1425 |
writes to the same database even if the native filesystem |
| 1426 |
locking mechanism malfunctions.</p> |
| 1427 |
|
| 1428 |
<p>The versions of SQLite that come preinstalled on Apple |
| 1429 |
Mac OS X computers contain a version of SQLite that has been |
| 1430 |
extended to use alternative locking strategies that work on |
| 1431 |
all network filesystems that Apple supports. These extensions |
| 1432 |
used by Apple work great as long as all processes are accessing |
| 1433 |
the database file in the same way. Unfortunately, the locking |
| 1434 |
mechanisms do not exclude one another, so if one process is |
| 1435 |
accessing a file using (for example) AFP locking and another |
| 1436 |
process (perhaps on a different machine) is using dot-file locks, |
| 1437 |
the two processes might collide because AFP locks do not exclude |
| 1438 |
dot-file locks or vice versa.</p> |
| 1439 |
|
| 1440 |
<a name="fsync"></a> |
| 1441 |
|
| 1442 |
<h2 id="_incomplete_disk_flushes"><span>9.2. </span> Incomplete Disk Flushes</h2> |
| 1443 |
|
| 1444 |
<p>SQLite uses the fsync() system call on Unix and the FlushFileBuffers() |
| 1445 |
system call on w32 in order to sync the file system buffers onto disk |
| 1446 |
oxide as shown in <a href="#section_3_7">step 3.7</a> and |
| 1447 |
<a href="#section_3_10">step 3.10</a>. Unfortunately, we have received |
| 1448 |
reports that neither of these interfaces works as advertised on many |
| 1449 |
systems. We hear that FlushFileBuffers() can be completely disabled |
| 1450 |
using registry settings on some Windows versions. Some historical |
| 1451 |
versions of Linux contain versions of fsync() which are no-ops on |
| 1452 |
some filesystems, we are told. Even on systems where |
| 1453 |
FlushFileBuffers() and fsync() are said to be working, often |
| 1454 |
the IDE disk control lies and says that data has reached oxide |
| 1455 |
while it is still held only in the volatile control cache.</p> |
| 1456 |
|
| 1457 |
<p>On the Mac, you can set this pragma:</p> |
| 1458 |
|
| 1459 |
<blockquote> |
| 1460 |
PRAGMA fullfsync=ON; |
| 1461 |
</blockquote> |
| 1462 |
|
| 1463 |
<p>Setting fullfsync on a Mac will guarantee that data really does |
| 1464 |
get pushed out to the disk platter on a flush. But the implementation |
| 1465 |
of fullfsync involves resetting the disk controller. And so not only |
| 1466 |
is it profoundly slow, it also slows down other unrelated disk I/O. |
| 1467 |
So its use is not recommended.</p> |
| 1468 |
|
| 1469 |
<a name="filedel"></a> |
| 1470 |
|
| 1471 |
<h2 id="_partial_file_deletions"><span>9.3. </span> Partial File Deletions</h2> |
| 1472 |
|
| 1473 |
<p>SQLite assumes that file deletion is an atomic operation from the |
| 1474 |
point of view of a user process. If power fails in the middle of |
| 1475 |
a file deletion, then after power is restored SQLite expects to see |
| 1476 |
either the entire file with all of its original data intact, or it |
| 1477 |
expects not to find the file at all. Transactions may not be atomic |
| 1478 |
on systems that do not work this way.</p> |
| 1479 |
|
| 1480 |
<a name="filegarbage"></a> |
| 1481 |
|
| 1482 |
<h2 id="_garbage_written_into_files"><span>9.4. </span> Garbage Written Into Files</h2> |
| 1483 |
|
| 1484 |
<p>SQLite database files are ordinary disk files that can be |
| 1485 |
opened and written by ordinary user processes. A rogue process |
| 1486 |
can open an SQLite database and fill it with corrupt data. |
| 1487 |
Corrupt data might also be introduced into an SQLite database |
| 1488 |
by bugs in the operating system or disk controller; especially |
| 1489 |
bugs triggered by a power failure. There is nothing SQLite can |
| 1490 |
do to defend against these kinds of problems.</p> |
| 1491 |
|
| 1492 |
<a name="mvhotjrnl"></a> |
| 1493 |
|
| 1494 |
<h2 id="_deleting_or_renaming_a_hot_journal"><span>9.5. </span> Deleting Or Renaming A Hot Journal</h2> |
| 1495 |
|
| 1496 |
<p>If a crash or power loss does occur and a hot journal is left on |
| 1497 |
the disk, it is essential that the original database file and the hot |
| 1498 |
journal remain on disk with their original names until the database |
| 1499 |
file is opened by another SQLite process and rolled back. |
| 1500 |
During recovery at <a href="#section_4_2">step 4.2</a> SQLite locates |
| 1501 |
the hot journal by looking for a file in the same directory as the |
| 1502 |
database being opened and whose name is derived from the name of the |
| 1503 |
file being opened. If either the original database file or the |
| 1504 |
hot journal have been moved or renamed, then the hot journal will |
| 1505 |
not be seen and the database will not be rolled back.</p> |
| 1506 |
|
| 1507 |
<p>We suspect that a common failure mode for SQLite recovery happens |
| 1508 |
like this: A power failure occurs. After power is restored, a well-meaning |
| 1509 |
user or system administrator begins looking around on the disk for |
| 1510 |
damage. They see their database file named "important.data". This file |
| 1511 |
is perhaps familiar to them. But after the crash, there is also a |
| 1512 |
hot journal named "important.data-journal". The user then deletes |
| 1513 |
the hot journal, thinking that they are helping to cleanup the system. |
| 1514 |
We know of no way to prevent this other than user education.</p> |
| 1515 |
|
| 1516 |
<p>If there are multiple (hard or symbolic) links to a database file, |
| 1517 |
the journal will be created using the name of the link through which |
| 1518 |
the file was opened. If a crash occurs and the database is opened again |
| 1519 |
using a different link, the hot journal will not be located and no |
| 1520 |
rollback will occur.</p> |
| 1521 |
|
| 1522 |
<p>Sometimes a power failure will cause a filesystem to be corrupted |
| 1523 |
such that recently changed filenames are forgotten and the file is |
| 1524 |
moved into a "/lost+found" directory. When that happens, the hot |
| 1525 |
journal will not be found and recovery will not occur. |
| 1526 |
SQLite tries to prevent this |
| 1527 |
by opening and syncing the directory containing the rollback journal |
| 1528 |
at the same time it syncs the journal file itself. However, the |
| 1529 |
movement of files into /lost+found can be caused by unrelated processes |
| 1530 |
creating unrelated files in the same directory as the main database file. |
| 1531 |
And since this is out from under the control of SQLite, there is nothing |
| 1532 |
that SQLite can do to prevent it. If you are running on a system that |
| 1533 |
is vulnerable to this kind of filesystem namespace corruption (most |
| 1534 |
modern journalling filesystems are immune, we believe) then you might |
| 1535 |
want to consider putting each SQLite database file in its own private |
| 1536 |
subdirectory.</p> |
| 1537 |
|
| 1538 |
<a name="future"></a> |
| 1539 |
|
| 1540 |
<h1 id="_future_directions_and_conclusion"><span>10. </span> Future Directions And Conclusion</h1> |
| 1541 |
|
| 1542 |
<p>Every now and then someone discovers a new failure mode for |
| 1543 |
the atomic commit mechanism in SQLite and the developers have to |
| 1544 |
put in a patch. This is happening less and less and the |
| 1545 |
failure modes are becoming more and more obscure. But it would |
| 1546 |
still be foolish to suppose that the atomic commit logic of |
| 1547 |
SQLite is entirely bug-free. The developers are committed to fixing |
| 1548 |
these bugs as quickly as they might be found.</p> |
| 1549 |
|
| 1550 |
<p> |
| 1551 |
The developers are also on the lookout for new ways to |
| 1552 |
optimize the commit mechanism. The current VFS implementations |
| 1553 |
for Unix (Linux and Mac OS X) and Windows make pessimistic assumptions about |
| 1554 |
the behavior of those systems. After consultation with experts |
| 1555 |
on how these systems work, we might be able to relax some of the |
| 1556 |
assumptions on these systems and allow them to run faster. In |
| 1557 |
particular, we suspect that most modern filesystems exhibit the |
| 1558 |
safe append property and that many of them might support atomic |
| 1559 |
sector writes. But until this is known for certain, SQLite will |
| 1560 |
take the conservative approach and assume the worst.</p> |
| 1561 |
<p align="center"><small><i>This page was last updated on 2026-04-21 10:28:59Z </small></i></p> |
| 1562 |
|