~ubuntu-branches/ubuntu/wily/sqlite3/wily

onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">

112

113

</form>

114

</div>

115

</table>

116

</div></div></div></div>

117

</td></tr></table>

118

119

120

121

122

123

<h1 align="center">Write-Ahead Logging</h1>

124

125

<p>The default method by which SQLite implements

126

<a href="atomiccommit.html">atomic commit and rollback</a> is a <a href="lockingv3.html#rollback">rollback journal</a>.

127

Beginning with <a href="releaselog/3_7_0.html">version 3.7.0</a>, a new "Write-Ahead Log" option

128

(hereafter referred to as "WAL") is available.</p>

129

130

<p>There are advantages and disadvantages to using WAL instead of

131

a rollback journal. Advantages include:</p>

132

133

<ol>

134

<li>WAL is significantly faster in most scenarios.

135

<li>WAL provides more concurrency as readers do not block writers and

136

a writer does not block readers. Reading and writing can proceed

137

concurrently.

138

<li>Disk I/O operations tends to be more sequential using WAL.

139

<li>WAL uses many fewer fsync() operations and is thus less vulnerable to

140

problems on systems where the fsync() system call is broken.

141

</ol>

142

143

<p>But there are also disadvantages:</p>

144

145

<ol>

146

<li>WAL normally requires that the <a href="vfs.html">VFS</a>

147

support shared-memory primitives.

148

(Exception: <a href="wal.html#noshm">WAL without shared memory</a>)

149

The built-in unix and windows VFSes

150

support this but third-party extension VFSes for custom operating

151

systems might not.

152

<li>All processes using a database must be on the same host computer;

153

WAL does not work over a network filesystem.

154

<li>Transactions that involve changes against multiple <a href="lang_attach.html">ATTACHed</a>

155

databases are atomic for each individual database, but are not

156

atomic across all databases as a set.

157

<li>It is not possible to change the database page size after entering WAL

158

mode, either on an empty database or by using <a href="lang_vacuum.html">VACUUM</a> or by restoring

159

from a backup using the <a href="backup.html">backup API</a>. You must be in a rollback journal

160

mode to change the page size.

161

<li>It is not possible to open <a href="wal.html#readonly">read-only WAL databases</a>.

162

The opening process must have write privileges for "<tt>-shm</tt>"

163

<a href="fileformat2.html#walindexformat">wal-index</a> shared memory file associated with the database, if that

164

file exists, or else write access on the directory containing

165

the database file if the "<tt>-shm</tt>" file does not exist.

166

<li>WAL might be very slightly slower (perhaps 1% or 2% slower)

167

than the traditional rollback-journal approach

168

in applications that do mostly reads and seldom write.

169

<li>There is an additional quasi-persistent "<tt>-wal</tt>" file and

170

"<tt>-shm</tt> shared memory file associated with each

171

database, which can make SQLite less appealing for use as an

172

<a href="whentouse.html#appfileformat">application file-format</a>.

173

<li>There is the extra operation of <a href="wal.html#ckpt">checkpointing</a> which, though automatic

174

by default, is still something that application developers need to

175

be mindful of.

176

<li>WAL works best with smaller transactions. WAL does

177

not work well for very large transactions. For transactions larger than

178

about 100 megabytes, traditional rollback journal modes will likely

179

be faster. For transactions in excess of a gigabyte, WAL mode may

180

fail with an I/O or disk-full error.

181

It is recommended that one of the rollback journal modes be used for

182

transactions larger than a few dozen megabytes.

183

184

</ol>

185

186

<h2>How WAL Works</h2>

187

188

<p>The traditional rollback journal works by writing a copy of the

189

original unchanged database content into a separate rollback journal file

190

and then writing changes directly into the database file. In the

191

event of a crash or <a href="lang_transaction.html">ROLLBACK</a>, the original content contained in the

192

rollback journal is played back into the database file to

193

revert the database file to its original state. The <a href="lang_transaction.html">COMMIT</a> occurs

194

when the rollback journal is deleted.</p>

195

196

<p>The WAL approach inverts this. The original content is preserved

197

in the database file and the changes are appended into a separate

198

WAL file. A <a href="lang_transaction.html">COMMIT</a> occurs when a special record indicating a commit

199

is appended to the WAL. Thus a COMMIT can happen without ever writing

200

to the original database, which allows readers to continue operating

201

from the original unaltered database while changes are simultaneously being

202

committed into the WAL. Multiple transactions can be appended to the

203

end of a single WAL file.</p>

204

205

206

207

<h3>Checkpointing</h3>

208

209

<p>Of course, one wants to eventually transfer all the transactions that

210

are appended in the WAL file back into the original database. Moving

211

the WAL file transactions back into the database is called a

212

"<i>checkpoint</i>".<p>

213

214

<p>Another way to think about the difference between rollback and

215

write-ahead log is that in the rollback-journal

216

approach, there are two primitive operations, reading and writing,

217

whereas with a write-ahead log

218

there are now three primitive operations: reading, writing, and

219

checkpointing.</p>

220

221

<p>By default, SQLite does a checkpoint automatically when the WAL file

222

reaches a threshold size of 1000 pages. (The

223

<a href="compile.html#default_wal_autocheckpoint">SQLITE_DEFAULT_WAL_AUTOCHECKPOINT</a> compile-time option can be used to

224

specify a different default.) Applications using WAL do

225

not have to do anything in order to for these checkpoints to occur.

226

But if they want to, applications can adjust the automatic checkpoint

227

threshold. Or they can turn off the automatic checkpoints and run

228

checkpoints during idle moments or in a separate thread or process.</p>

229

230

231

232

<h3>Concurrency</h3>

233

234

<p>When a read operation begins on a WAL-mode database, it first

235

remembers the location of the last valid commit record in the WAL.

236

Call this point the "end mark". Because the WAL can be growing and

237

adding new commit records while various readers connect to the database,

238

each reader can potentially have its own end mark. But for any

239

particular reader, the end mark is unchanged for the duration of the

240

transaction, thus ensuring that a single read transaction only sees

241

the database content as it existed at a single point in time.</p>

242

243

<p>When a reader needs a page of content, it first checks the WAL to

244

see if that page appears there, and if so it pulls in the last copy

245

of the page that occurs in the WAL prior to the reader's end mark.

246

If no copy of the page exists in the WAL prior to the reader's end mark,

247

then the page is read from the original database file. Readers can

248

exist in separate processes, so to avoid forcing every reader to scan

249

the entire WAL looking for pages (the WAL file can grow to

250

multiple megabytes, depending on how often checkpoints are run), a

251

data structure called the "wal-index" is maintained in shared memory

252

which helps readers locate pages in the WAL quickly and with a minimum

253

of I/O. The wal-index greatly improves the performance of readers,

254

but the use of shared memory means that all readers must exist on the

255

same machine. This is why the write-ahead log implementation will not

256

work on a network filesystem.</p>

257

258

<p>Writers merely append new content to the end of the WAL file.

259

Because writers do nothing that would interfere with the actions of

260

readers, writers and readers can run at the same time. However,

261

since there is only one WAL file, there can only be one writer at

262

a time.</p>

263

264

<p>A checkpoint operation takes content from the WAL file

265

and transfers it back into the original database file.

266

A checkpoint can run concurrently with readers, however the checkpoint

267

must stop when it reaches a page in the WAL that is past the read mark

268

of any current reader. The checkpoint has to stop at that point because

269

otherwise it might overwrite part of the database file that the reader

270

is actively using. The checkpoint remembers (in the wal-index) how far

271

it got and will resume transferring content from the WAL to the database

272

from where it left off on the next invocation.</p>

273

274

<p>Thus a long-running read transaction can prevent a checkpointer from

275

making progress. But presumably every read transactions will eventually

276

end and the checkpointer will be able to continue.</p>

277

278

<p>Whenever a write operation occurs, the writer checks how much progress

279

the checkpointer has made, and if the entire WAL has been transferred into

280

the database and synced and if no readers are making use of the WAL, then

281

the writer will rewind the WAL back to the beginning and start putting new

282

transactions at the beginning of the WAL. This mechanism prevents a WAL

283

file from growing without bound.</p>

284

285

286

287

<h3>Performance Considerations</h3>

288

289

<p>Write transactions are very fast since they only involve writing

290

the content once (versus twice for rollback-journal transactions)

291

and because the writes are all sequential. Further, syncing the

292

content to the disk is not required, as long as the application is

293

willing to sacrifice durability following a power loss or hard reboot.

294

(Writers sync the WAL on every transaction commit if

295

<a href="pragma.html#pragma_synchronous">PRAGMA synchronous</a> is set to FULL but omit this sync if

296

<a href="pragma.html#pragma_synchronous">PRAGMA synchronous</a> is set to NORMAL.)</p>

297

298

<p>On the other hand, read performance deteriorates as the WAL file

299

grows in size since each reader must check the WAL file for the content

300

and the time needed to check the WAL file is proportional

301

to the size of the WAL file. The wal-index helps find content

302

in the WAL file much faster, but performance still falls off with

303

increasing WAL file size. Hence, to maintain good read performance

304

it is important to keep the WAL file size down by

305

running checkpoints at regular intervals.</p>

306

307

<p>Checkpointing does require sync operations in order to avoid

308

the possibility of database corruption following a power loss

309

or hard reboot. The WAL must be synced to persistent storage

310

prior to moving content from the WAL into the database and the

311

database file must by synced prior to resetting the WAL.

312

Checkpoint also requires more seeking.

313

The checkpointer makes an effort to

314

do as many sequential page writes to the database as it can (the pages

315

are transferred from WAL to database in ascending order) but even

316

then there will typically be many seek operations interspersed among

317

the page writes. These factors combine to make checkpoints slower than

318

write transactions.</p>

319

320

<p>The default strategy is to allow successive write transactions to

321

grow the WAL until the WAL becomes about 1000 pages in size, then to

322

run a checkpoint operation for each subsequent COMMIT until the WAL

323

is reset to be smaller than 1000 pages. By default, the checkpoint will be

324

run automatically by the same thread that does the COMMIT that pushes

325

the WAL over its size limit. This has the effect of causing most

326

COMMIT operations to be very fast but an occasional COMMIT (those that trigger

327

a checkpoint) to be much slower. If that effect is undesirable, then

328

the application can disable automatic checkpointing and run the

329

periodic checkpoints in a separate thread, or separate process.

330

(Links to commands and interfaces to accomplish this are

331

<a href="#how_to_checkpoint">shown below</a>.)</p>

332

333

334

<p>Note that with <a href="pragma.html#pragma_synchronous">PRAGMA synchronous</a> set to NORMAL, the checkpoint

335

is the only operation to issue an I/O barrier or sync operation

336

(fsync() on unix or FlushFileBuffers() on windows). If an application

337

therefore runs checkpoint in a separate thread or process, the main

338

thread or process that is doing database queries and updates will never

339

block on a sync operation. This helps to prevent "latch-up" in applications

340

running on a busy disk drive. The downside to

341

this configuration is that transactions are no longer durable and

342

might rollback following a power failure or hard reset.</p>

343

344

345

<p>Notice too that there is a tradeoff between average read performance

346

and average write performance. To maximize the read performance,

347

one wants to keep the WAL as small as possible and hence run checkpoints

348

frequently, perhaps as often as every COMMIT. To maximize

349

write performance, one wants to amortize the cost of each checkpoint

350

over as many writes as possible, meaning that one wants to run checkpoints

351

infrequently and let the WAL grow as large as possible before each

352

checkpoint. The decision of how often to run checkpoints may therefore

353

vary from one application to another depending on the relative read

354

and write performance requirements of the application.

355

The default strategy is to run a checkpoint once the WAL

356

reaches 1000 pages and this strategy seems to work well in test applications on

357

workstations, but other strategies might work better on different

358

platforms or for different workloads.</p>

359

360

<h2>Activating And Configuring WAL Mode</h2>

361

362

<p>An SQLite database connection defaults to

363

<a href="pragma.html#pragma_journal_mode">journal_mode=DELETE</a>. To convert to WAL mode, use the

364

following pragma:</p>

365

366

367

PRAGMA journal_mode=WAL;

368

</pre></blockquote>

369

370

<p>The journal_mode pragma returns a string which is the new journal mode.

371

On success, the pragma will return the string "<tt>wal</tt>". If

372

the conversion to WAL could not be completed (for example, if the <a href="vfs.html">VFS</a>

373

does not support the necessary shared-memory primitives) then the

374

journaling mode will be unchanged and the string returned from the

375

primitive will be the prior journaling mode (for example "<tt>delete</tt>").

376

377

378

<h3>Automatic Checkpoint</h3>

379

380

<p>By default, SQLite will automatically checkpoint whenever a <a href="lang_transaction.html">COMMIT</a>

381

occurs that causes the WAL file to be 1000 pages or more in size, or when the

382

last database connection on a database file closes. The default

383

configuration is intended to work well for most applications.

384

But programs that want more control can force a checkpoint

385

using the <a href="pragma.html#pragma_wal_checkpoint">wal_checkpoint pragma</a> or by calling the

386

<a href="c3ref/wal_checkpoint.html">sqlite3_wal_checkpoint()</a> C interface. The automatic checkpoint

387

threshold can be changed or automatic checkpointing can be completely

388

disabled using the <a href="pragma.html#pragma_wal_autocheckpoint">wal_autocheckpoint pragma</a> or by calling the

389

<a href="c3ref/wal_autocheckpoint.html">sqlite3_wal_autocheckpoint()</a> C interface. A program can also

390

use <a href="c3ref/wal_hook.html">sqlite3_wal_hook()</a> to register a callback to be invoked whenever

391

any transaction commits to the WAL. This callback can then invoke

392

<a href="c3ref/wal_checkpoint.html">sqlite3_wal_checkpoint()</a> to for a checkpoint based on whatever

393

criteria it thinks is appropriate. (The automatic checkpoint mechanism

394

is implemented as a simple wrapper around <a href="c3ref/wal_hook.html">sqlite3_wal_hook()</a>.)</p>

395

396

<h3>Persistence of WAL mode</h3>

397

398

<p>Unlike the other journaling modes,

399

<a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode=WAL</a> is

400

persistent. If a process sets WAL mode, then closes and reopens the

401

database, the database will come back in WAL mode. In contrast, if

402

a process sets (for example) PRAGMA journal_mode=TRUNCATE and then closes and

403

reopens the database will come back up in the default rollback mode of

404

DELETE rather than the previous TRUNCATE setting.</p>

405

406

<p>The persistence of WAL mode means that applications can be converted

407

to using SQLite in WAL mode without making any changes to the application

408

itself. One has merely to run "<tt>PRAGMA journal_mode=WAL;</tt>" on the

409

database file(s) using the <a href="sqlite.html">command-line shell</a> or other utility, then

410

restart the application.</p>

411

412

<p>The WAL journal mode will be set on all

413

connections to the same database file if it is set on any one connection.

414

</p>

415

416

417

418

<h2>Read-Only Databases</h2>

419

420

<p>No SQLite database (regardless of whether or not it is WAL mode) is

421

readable if it is located on read-only media and it requires recovery.

422

So, for example, if an application crashes and leaves an SQLite database

423

with a <a href="lockingv3.html#hotjrnl">hot journal</a>, that database cannot be opened unless the opening

424

process has write privilege on the database file, the directory

425

containing the database file, and the hot journal. This is because the

426

incomplete transaction left over from the crash must be rolled back prior

427

to reading the database and that rollback cannot occur without write

428

permission on all files and the directory containing them.</p>

429

430

<p>A database in WAL mode cannot generally be opened from read-only

431

media because even ordinary reads in WAL mode require recovery-like

432

operations.</p>

433

434

<p>An efficient implementation of the <a href="fileformat2.html#walread">WAL read algorithm</a> requires that

435

there exist a hash table in shared memory over the content of the WAL file.

436

This hash table is called the <a href="fileformat2.html#walindexformat">wal-index</a>.

437

The wal-index is in shared memory, and so technically it does not have

438

to have a name in the host computer filesystem. Custom

439

<a href="vfs.html">VFS</a> implementations are free to implement shared

440

memory in any way they see fit, but the default unix and windows

441

drivers that come built-in with SQLite implement shared memory

442

using <a href="http://en.wikipedia.org/wiki/Mmap">mmapped files</a>

443

named using the suffix "<tt>-shm</tt>" and

444

located in the same directory as the database file. The wal-index must

445

be rebuilt upon first access, even by readers, and so in order to open

446

the WAL database, write access is required on the "<tt>-shm</tt>" shared

447

memory file if the file exists, or else write access is required on the

448

directory containing the database so that the wal-index can be created if

449

it does not already exist.

450

This does not preclude custom VFS implementations that implement shared

451

memory differently from being able to access read-only WAL databases, but

452

it does prevent the default unix and windows backends from accessing

453

WAL databases on read-only media.</p>

454

455

<p>Hence, SQLite databases should always be converted to

456

<a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode=DELETE</a> prior to being transferred

457

to read-only media.</p>

458

459

<p>Also, if multiple processes are to access a WAL mode database, then

460

all processes should run under user or group IDs that give them write

461

access to the database files, the WAL file, the shared memory

462

<tt>-shm</tt> file, and the containing directory.</p>

463

464

<h2>Implementation Of Shared-Memory For The WAL-Index</h2>

465

466

<p>The <a href="fileformat2.html#walindexformat">wal-index</a> is implemented using an ordinary file that is

467

mmapped for robustness. Early (pre-release) implementations of WAL mode

468

stored the wal-index in volatile shared-memory, such as files created in

469

/dev/shm on Linux or /tmp on other unix systems. The problem

470

with that approach is that processes with a different root directory

471

(changed via <a href="http://en.wikipedia.org/wiki/Chroot">chroot</a>)

472

will see different files and hence use different shared memory areas,

473

leading to database corruption. Other methods for creating nameless

474

shared memory blocks are not portable across the various flavors of

475

unix. And we could not find any method to create nameless shared

476

memory blocks on windows. The only way we have found to guarantee

477

that all processes accessing the same database file use the same shared

478

memory is to create the shared memory by mmapping a file in the same

479

directory as the database itself.</p>

480

481

<p>Using an ordinary disk file to provide shared memory has the

482

disadvantage that it might actually do unnecessary disk I/O by

483

writing the shared memory to disk. However, the developers do not

484

think this is a major concern since the wal-index rarely exceeds

485

32 KiB in size and is never synced. Furthermore, the wal-index

486

backing file is deleted when the last database connection disconnects,

487

which often prevents any real disk I/O from ever happening.</p>

488

489

<p>Specialized applications for which the default implementation of

490

shared memory is unacceptable can devise alternative methods via a

491

custom <a href="vfs.html">VFS</a>.

492

For example, if it is known that a particular database

493

will only be accessed by threads within a single process, the wal-index

494

can be implemented using heap memory instead of true shared memory.</p>

495

496

497

498

<h2>Use of WAL Without Shared-Memory</h2>

499

500

<p>Beginning in SQLite version 3.7.4, WAL databases can be created, read, and

501

written even if shared memory is unavailable as long as the

502

<a href="pragma.html#pragma_locking_mode">locking_mode</a> is set to EXCLUSIVE before the first attempted access.

503

In other words, a process can interact with

504

a WAL database without using shared memory if that

505

process is guaranteed to be the only process accessing the database.

506

This feature allows WAL databases to be created, read, and written

507

by legacy <a href="vfs.html">VFSes</a> that lack the "version 2" shared-memory

508

methods xShmMap, xShmLock, xShmBarrier, and xShmUnmap on the

509

<a href="c3ref/io_methods.html">sqlite3_io_methods</a> object.</p>

510

511

<p>If EXCLUSIVE locking mode is set prior to the first WAL-mode

512

database access, then SQLite never attempts to call any of the

513

shared-memory methods and hence no shared-memory

514

wal-index is ever created.

515

In that case, the database connection remains in EXCLUSIVE mode

516

as long as the journal mode is WAL; attempts to change the locking

517

mode using "<tt>PRAGMA locking_mode=NORMAL;</tt>" are no-ops.

518

The only way to change out of EXCLUSIVE locking mode is to first

519

change out of WAL journal mode.</p>

520

521

<p>If NORMAL locking mode is in effect for the first WAL-mode database

522

access, then the shared-memory wal-index is created. This means that the

523

underlying VFS must support the "version 2" shared-memory.

524

If the VFS does not support shared-memory methods, then the attempt to

525

open a database that is already in WAL mode, or the attempt convert a

526

database into WAL mode, will fail.

527

As long a connection is using a shared-memory wal-index, the locking

528

mode can be changed freely between NORMAL and EXCLUSIVE. It is only

529

when the shared-memory wal-index is omitted, when the locking mode is

530

EXCLUSIVE prior to the first WAL-mode database access, that the locking

531

mode is stuck in EXCLUSIVE.</p>

532

533

534

535

<h2>Backwards Compatibility</h2>

536

537

<p>The database file format is unchanged for WAL mode. However, the

538

WAL file and the <a href="fileformat2.html#walindexformat">wal-index</a> are new concepts and so older versions of

539

SQLite will not know

540

how to recover a crashed SQLite database that was operating in WAL mode

541

when the crash occurred.

542

To prevent older versions of SQLite from trying to recover

543

a WAL-mode database (and making matters worse) the database file format

544

version numbers (bytes 18 and 19 in the <a href="fileformat2.html#database_header">database header</a>)

545

are increased from 1 to 2 in WAL mode.

546

Thus, if an older version of SQLite attempts to connect to an SQLite

547

database that is operating in WAL mode, it will report an error along

548

the lines of "file is encrypted or is not a database".</p>

549

550

<p>One can explicitly change out of WAL mode using a pragma such as

551

this:</p>

552

553

554

PRAGMA journal_mode=DELETE;

555

</pre></blockquote>

556

557

<p>Deliberately changing out of WAL mode changes the database file format

558

version numbers back to 1 so that older versions of SQLite can once again

559

access the database file.</p>

560